CODEWITHSUNDEEP
sqloracle-databaseleft-join
Bob
Bob

Reputation: 327

How can I improve performance of a 'LIKE'-linked LEFT JOIN (Oracle SQL)

I'm having some problems with performance of a join that uses LIKE to make the join. An example setup is described below. Table1 grows over the period of the day up to a few thousand rows. Table2 is a lookup table and is constant in size with just a few dozen entries. As more data is added to Table1, the query performance worsens massively to a point where it becomes effectively unusable.

I have indexes on Table1.Entity and Table2.EntityJoin

I've build the tables this way, with wildcarded EntityJoin values so the front end can define filters that map similar entity names to a particular group. It appeared to me it would be more efficient from a user's point of view to be able to use wildcards rather than have to define every single entity name from Table1 in Table2 (note, this structure is a massively simplified view for sake of example).

I've read through a few similar posts regarding join performance, but didn't see any that use LIKE in the way I'm trying to use it.

Table1:

------------------------
Entity
------------------------
AnEntity1
AnEntity2
OtherEntity1
Other3Entity
Other12Entity
MoreEntities2
EvenMore3Entities5
------------------------

Table2:

---------------------------------
EntityJoin     | JoinName
---------------------------------
An%            | SomeVal
Other%         | SomeVal
More%          | SomeOtherVal
---------------------------------

Query

select 
    *
from
    Table1 T1
    left join Table2 T2
        on T1.Entity like T2.EntityJoin;

Upvotes: 1

Views: 517

Answers (1)

Thomas Strub
Thomas Strub

Reputation: 1285

Best what you can expect with your query is Full Table Scan on table 1 and a nested loop to table 2.

This is because you have to check every entry in t1 if it matches to any entry in t2.

T2 like 

---------------------------------
EntityJoin     | JoinName
---------------------------------
An%            | SomeVal
A%             | SomeVal
AnE%           | SomeOtherVal
---------------------------------

would be possible and entries: 

AnEntity1
AnEntity2

would triple.

So you have to think about what can be done to be more selective.

Upvotes: 0

Related Questions