Force Oracle database not to materialize CTE

Question

I have some issue with cte, if I use this query oracle materialized the cte1 view and query will be slow

with cte1 as (..),
    cte2 as ( ... use cte1 ...),
    cte3 as ( ... use cte1 ...)
select * from  cte2  join cte3
  on ...

in the following query Oracle does not materialize cte1 and the query is 20 times quicker as before:

with cte1 as (..),
    cte2 as ( ... use cte1 ...)
select * from cte2 on ....

as well

with cte1 as (..),
    cte3 as ( ... use cte1 ...)
select * from cte3 on ....

Is it possible to force Oracle not to materialize CTE so it will be using idexes ?

Execution plan for query 1:

Plan hash value: 1038428573

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |   126K|   104M|  1753   (1)| 00:00:22 |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |          |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9DC639_11293183 |       |       |            |          |
|*  3 |    HASH JOIN                   |                             | 39285 |  1726K|  1618   (1)| 00:00:20 |
|*  4 |     HASH JOIN                  |                             | 31724 |   650K|   863   (1)| 00:00:11 |
|*  5 |      INDEX RANGE SCAN          | UQ1_xxxxxxx                 | 31724 |   402K|    23   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL         | xxxx                        |   384K|  3005K|   837   (1)| 00:00:11 |
|   7 |     TABLE ACCESS FULL          | xxxxxxxxx                   |   481K|    11M|   753   (1)| 00:00:10 |
|*  8 |   HASH JOIN                    |                             |   126K|   104M|   136   (3)| 00:00:02 |
|*  9 |    HASH JOIN                   |                             |     3 |  1314 |    68   (2)| 00:00:01 |
|  10 |     TABLE ACCESS BY INDEX ROWID| xxxxxxxxxxxxxxxx            |     2 |    20 |     1   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN          | FK2_xxxxxxxxxxxxxxxx        |     2 |       |     1   (0)| 00:00:01 |
|* 12 |     VIEW                       |                             | 39285 |    16M|    66   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL         | SYS_TEMP_0FD9DC639_11293183 | 39285 |  1035K|    66   (0)| 00:00:01 |
|* 14 |    VIEW                        |                             | 39285 |    16M|    66   (0)| 00:00:01 |
|  15 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9DC639_11293183 | 39285 |  1035K|    66   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ES"."xxxxxxxxxxxxx"="E"."xxxxxxxxxxxxxx")
   4 - access("CR"."xxxxxxxxxxxxxx"="E"."xxxxxxxxxxxxID")
   5 - access("CR"."xxxxxxxID"=TO_NUMBER(:xxxxxxxID))
   8 - access("EV"."xxxxxxxxxxxx_ID"="LA"."xxxxxx_ID")
   9 - access("LA"."xxxxxxxxxxxxx_ID"="EV2"."xxxxxxxxxxxx_ID")
  11 - access("LA"."xxxxxxxID"=359134)
  12 - filter("EV2"."xxxxxxxxxxxxxxxxID"=4)
  14 - filter("EV"."xxxxxxxxxxxxxxx_ID"=3 AND "EV"."xxxxxxxxxxxx_ID"=359134)

Execution plan for query 2:

Plan hash value: 1937334873

----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                        |     1 |    55 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                   |                        |       |       |            |          |
|   2 |   NESTED LOOPS                  |                        |     1 |    55 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                        |     1 |    31 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                        |     2 |    46 |     2   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| xxxxxxxxxxxxxxxx       |     2 |    20 |     1   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | FK2_xxxxxxxxxxxxxxxx   |     2 |       |     1   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| xxxxxxxxxxxx           |     1 |    13 |     1   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | FK2_xxxxxxxxxxxx       |     4 |       |     1   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS BY INDEX ROWID | xxxxxxxxxxxxx          |     1 |     8 |     1   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN          | PK_xxxxxxxxxxxxx       |     1 |       |     1   (0)| 00:00:01 |
|* 11 |    INDEX RANGE SCAN             | UQ1_xxxxxxxxxxxxxxxxxx |     1 |       |     1   (0)| 00:00:01 |
|  12 |   TABLE ACCESS BY INDEX ROWID   | xxxxxxxxxxxxxxxxxx     |     1 |    24 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("LA"."xxxxxx_ID"=359134)
   7 - filter("CR"."xxxxxxxID"=TO_NUMBER(:xxxxxxxID))
   8 - access("LA"."xxxxxxxxxxxxx_ID"="CR"."xxxxxxxxxxxx_ID")
   9 - filter("E"."xxxxxxxxxxxxxxx_ID"=4)
  10 - access("CR"."xxxxxxxxxxxxID"="E"."xxxxxxxxxxxxID")
  11 - access("ES"."xxxxxxxxxxxID"="E"."xxxxxxxxxxxxID")

Answer 1

There's an undocumented /*+ inline */ hint that should prevent the optimizer from materializing the CTE. Should be placed right after the select in the CTE itself.

The /*+ materialize */ hint would be the opposite of that, i.e. request that the view be materialized.

Neither of these are officially documented as far as I can tell, so use with caution. Opening an SR with Oracle support to get some advice is a good idea in this sort of case, they could "approve" the use of the hint or provide alternatives (including potential patches/bugfixes that would address the issue).