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

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.