Tony Tony - 4 months ago 22
SQL Question

Force Oracle database not to materialize CTE

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

Mat Mat
Answer

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

Comments