I have written a query utilizing Oracle's with clause. I have identified one particular with clause in a larger query to be really, really slow. Here are three versions of the same query, they all have the same execution plan!
Version 1 (takes 30 seconds):
with abc as
(
select count(distinct id) as my_count, some_column
from large_view v where some_column in (...constant values here...)
group by some_column
) select my_count from abc;
Version 2 (takes 50ms):
select count(distinct id) as my_count, some_column
from large_view v where some_column in (...constant values here...)
group by some_column;
Version 3 (takes 50ms);
select my_count from (
select count(distinct id) as my_count, some_column
from large_view v where some_column in (...constant values here...)
group by some_column
);
I'd really like to use the with clause to keep the large query tidy. However, the difference in execution time is dramatic. Version 2 and 3 only take a few ms and Version 1 runs for about 30 seconds.
What is it that oracle does so that the statement with the with clause is that much slower and how can I get rid of it? Any explanation appreciated. I suspect that Version 1 is materializing every time but don't know for sure and how I can test it. Also, if that's the case, can I get rid of it (with a hint or a setting)? However, I could be wrong on this because with the materialize hint explicitly set the query runs even slower.
Oracle version:
- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit
- Production PL/SQL Release 11.2.0.1.0 – Production
- "CORE 11.2.0.1.0 Production"
- TNS for Linux: Version 11.2.0.1.0 –
- Production NLSRTL Version 11.2.0.1.0 – Production
Best Answer
Try the hint INLINE