Oracle 11g: "With Clause" very slow 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
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;