Hello,

I have a weird performance issue with a relatively
simple query with INTERSECT and need advice.
The query in question looks like this

(subquery A)
intersect
(subquery B)

independently subquery A runs to completion in 2 seconds and returns 487 rows
independently subquery B runs to completion in 6 seconds and returns 2440 rows

the combined query (with INTERSECT) returns 15 matches
(correct), but runs for a whooping 1 minute 6 seconds

Considering the relatively small size of INTERSECT,
I cannot understand why the performance degrades so much.

Either subquery returns a result set of strings
20-30 characters long. Both subqueries contain nothing
exceptional - very conventional SQL. The size of
intersected results is relatively small, so no memory
issues should be responsible (I could intersect
this results with Perl in less then a second).
The only possible catch is that the strings
returned generally differ only in the last few
characters - could this affect sorting/hashing?

For all
means and purposes Oracle cannot run INTERSECT for
almost 1 minute (we are talking 2 MHz/512MB machine
running Oracle 9i)

So I presume it must be a query optimizer fault.
Nevertheless, playing with QUERY_REWRITE_ENABLED does not solve
anything, likewise COST hint.

Could anyone advice to me how to tackle this problem
or at least explain what is happening?