Iniling a Common Table Expression

Having a complex common table expression (subquery factoring) SQL clause. It is used several times in the main query. The optimizer desides to do TEMP TABLE TRANSFORMATION. Effectively do what the undocumented MATERIALIZE hint would do. Sometimes that is not the most efficient way to execute. The problem described through an exaple:

drop table cteinline;

create table cteinline as
select level id, mod(level,20) md
  from dual connect by level < 100000;
  
alter table cteinline add constraint cteinline_pk primary key(id);

create index cteinline_md_idx on cteinline(md) compress 1;


with cte as (
select id, (select count(*) from cteinline co where ct.id=co.md) co from cteinline ct
)
select /*+gather_plan_statistics*/ * 
  from cte 
 where id < 10
 union all
select * 
  from cte ct2 
 where id > 99990
;

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |      1 |        |     18 |00:00:00.34 |     867 |    165 |    165 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION |                             |      1 |        |     18 |00:00:00.34 |     867 |    165 |    165 |       |       |          |
|   2 |   LOAD AS SELECT           |                             |      1 |        |      1 |00:00:00.32 |     525 |      0 |    165 |   525K|   525K|  525K (0)|
|   3 |    SORT AGGREGATE          |                             |  99999 |      1 |  99999 |00:00:00.17 |     173 |      0 |      0 |       |       |          |
|*  4 |     INDEX RANGE SCAN       | CTEINLINE_MD_IDX            |  99999 |    961 |  95000 |00:00:00.08 |     173 |      0 |      0 |       |       |          |
|   5 |    TABLE ACCESS FULL       | CTEINLINE                   |      1 |  96116 |  99999 |00:00:00.01 |     182 |      0 |      0 |       |       |          |
|   6 |   UNION-ALL                |                             |      1 |        |     18 |00:00:00.02 |     339 |    165 |      0 |       |       |          |
|*  7 |    VIEW                    |                             |      1 |  96116 |      9 |00:00:00.02 |     171 |    165 |      0 |       |       |          |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D9DFC_47AEB497 |      1 |  96116 |  99999 |00:00:00.01 |     171 |    165 |      0 |       |       |          |
|*  9 |    VIEW                    |                             |      1 |  96116 |      9 |00:00:00.01 |     168 |      0 |      0 |       |       |          |
|  10 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D9DFC_47AEB497 |      1 |  96116 |  99999 |00:00:00.01 |     168 |      0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CO"."MD"=:B1)
   7 - filter("ID"<10)
   9 - filter("ID">99990)

We want to avoid the TEMP TABLE TRANSFORMATION. Do not do the MATERIALIZE for the common table expression. 11.1 introduced a new performance view v$sql_hint. From there one can find yet another not documented hint that does the inverse.

 
select inverse from V$SQL_HINT where name = 'MATERIALIZE'; 

INLINE

The inverse is not NO_MATERIALIZE or NOMATERIALIZE. So many other inverse hints start with word NO. Like MERGE and NO_MERGE. The INLINE hint is not icluded in the 11.2.0.3 documentation but seems to do the job for us. TEMP TABLE TRANSFORMATION is wanished from the plan and less rows are browsed during the execution in this case.

with cte as (
select /*+inline*/ id, (select count(*) from cteinline co where ct.id=co.md) co from cteinline ct
)
select /*+gather_plan_statistics*/ * 
  from cte 
 where id < 10
 union all
select * 
  from cte ct2 
 where id > 99990
;

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |      1 |        |     18 |00:00:00.01 |      92 |
|   1 |  UNION-ALL         |                  |      1 |        |     18 |00:00:00.01 |      92 |
|   2 |   SORT AGGREGATE   |                  |      9 |      1 |      9 |00:00:00.01 |      80 |
|*  3 |    INDEX RANGE SCAN| CTEINLINE_MD_IDX |      9 |    961 |  45000 |00:00:00.01 |      80 |
|*  4 |   INDEX RANGE SCAN | CTEINLINE_PK     |      1 |      9 |      9 |00:00:00.01 |       2 |
|   5 |   SORT AGGREGATE   |                  |      9 |      1 |      9 |00:00:00.01 |       8 |
|*  6 |    INDEX RANGE SCAN| CTEINLINE_MD_IDX |      9 |    961 |      0 |00:00:00.01 |       8 |
|*  7 |   INDEX RANGE SCAN | CTEINLINE_PK     |      1 |      9 |      9 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CO"."MD"=:B1)
   4 - access("ID"<10)
   6 - access("CO"."MD"=:B1)
   7 - access("ID">99990)