Very slow INTERSECT under 9i - optimizer mistake?
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?
Re: Very slow INTERSECT under 9i - optimizer mistake?
Quote:
Originally posted by anatoli
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
Both subquery independently don't use any addition sorting.
Bot if u try to get intersection of the queries, Oracle has to execute addition step for each of them:
SORT UNIQUE
This is reason, why intersection more slowly, then two subqueries
separately.
Re: Re: Very slow INTERSECT under 9i - optimizer mistake?
Quote:
Originally posted by Shestakov
Both subquery independently don't use any addition sorting.
Bot if u try to get intersection of the queries, Oracle has to execute addition step for each of them:
SORT UNIQUE
This is reason, why intersection more slowly, then two subqueries
separately.
Given the number of rows involved, i don't believe that this explains the differencein execution time.