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")

Do you have a real example, and the execution plans that show what you think it's doing wrong? Materialising means it's all in memory which ought to be faster than accessing the underlying tables even with indexes. — Alex Poole, Aug 05 '14 at 07:51
@AlexPoole I added the execution plan for slow query. Hope it can help. — Tony, Aug 05 '14 at 08:21
`cte1` is identical in both queries? Do the queries have any `where` conditions (apart from the join between `cte2` and `cte3`)? — Alex Poole, Aug 05 '14 at 08:41
yes `cte1` is identical and there are no where clause in outer select. — Tony, Aug 05 '14 at 08:44
I think the `/*+ inline */` hint is supposed to do what you want, but that's not documented AFAICT, and I never used that myself. Possibly worth a try. — Mat, Aug 05 '14 at 09:09
@Mat hey, thank you. It works. I've placed the hint on `select` in cte1 and it worked. If you write your comment as an answer I will accept it. — Tony, Aug 05 '14 at 09:15

score 8 · Accepted Answer · answered Aug 05 '14 at 09:27

8

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).

answered Aug 05 '14 at 09:27

Mat

202,337
40
393
406

In order to make it work like described in this answer, ... is there an option to activate on Oracle? ... is there a package to be installed?... is there something special to do?... because I do not see it work on my side. Thanks for a short feedback or hint. – skyfrog Nov 02 '15 at 13:44
1

No, nothing needs to be done. But as I said, this is unsupported and undocumented, so who knows in what circumstances it will or won't work. – Mat Nov 02 '15 at 14:02
Thanks for your reply. It rather seems to have no effect on our 12c. – skyfrog Nov 02 '15 at 14:05
The materialize hint works on 12c as far as I can tell, but I don't have a test-case for the inline one. – Mat Nov 02 '15 at 14:29

score 2 · Answer 2 · answered Jul 25 '16 at 22:55

+1'd question and Mat's answer.

Although there is another reason for Oracle to do TEMP TABLE TRANSFORMATION. If you have set init parameter star_transformation_enabled to TRUE.

If it's not desired in your case, you can switch it off by running

alter session set star_transformation_enabled=TEMP_DISABLE;

This will keep star tranformation enabled, but will switch off TEMP TABLE TRANSFORMATION.

https://blogs.oracle.com/optimizer/entry/star_transformation

Force Oracle database not to materialize CTE

2 Answers2