CODEWITHSUNDEEP
phpsqloracle-databaseperformancesql-like
Rene Pot
Rene Pot

Reputation: 24815

Speed problems with 3 like statements in 1 query (oracle)

This is a snippet of my query:

and (
    ADDR_FULLTEXT
    like upper(:streets1)
)

Whenever i execute the query this is a part of, it executes in about 0.05 seconds. This is perfect! But, when I do the following:

and (
    ADDR_FULLTEXT
    like upper(:streets1)
    AND
    ADDR_FULLTEXT
    like upper(:streets2)
)

it results in a 20 second query. Why is the difference this big, and how can solve this?

What the rest of the query basically does, is selecting multiple fields from a view (ADDR_FULLTEXT is one of them). There also is another like, just like this one.

Maybe i'm doing something really wrong here, and maybe therefor there is a better way of doing this.

FYI: A PHP loop generates this query, which results from an explode of a search query. Every word is parsed in this query resulting in 1 or more of the same "like" in 1 query. Another like is generated the same way, but this is always 1 like, never more, never less.

Upvotes: 2

Views: 247

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

The change to this predicate is probably changing the CBO's estimate of the number of rows that will be returned by the table. This in turn may cause a significant restructuring of the plan, resulting in a change in performance.

Further analysis will have to wait until you provide the query (including views) and query plans.

Upvotes: 0

KM.
KM.

Reputation: 103589

try breaking it down into two different queries, and taking the intersection, like:

SELECT * FROM YourTable WHERE ADDR_FULLTEXT like upper(:streets1)
INTERSECT
SELECT * FROM YourTable WHERE ADDR_FULLTEXT like upper(:streets2)

see Intersect

Upvotes: 1

Benoit
Benoit

Reputation: 79165

Probably you will have a full table scan for each LIKE statement, and the rest of your query uses indexes.

Upvotes: 0

Related Questions