Oracle 11g: “With Clause” very slow

oracleperformance

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

with abc as 
(
  select /*+ INLINE */ 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;