This is possibly my longest title to date – I try to keep them short enough to fit the right hand column of the blog without wrapping – but I couldn’t think of a good way to shorten it (Personally I prefer to use the expression CTE – common table expression – over “factored subquery” or “subquery factoring” or “with subquery”, and that would have achieved my goal, but might not have meant anything to most people.)
If you haven’t come across them before, recursive CTEs appeared in 11.2, are in the ANSI standard, and are (probably) viewed by Oracle as the strategic replacement for “connect by” queries. Here’s a simple (and silly) example:
with data(p) as (
select 1 p from dual
union all
select p + 1 from data where p < 100
)
select p
from data
where rownum <= 10
;
P
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 37253879
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 4 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 2 | 26 | 4 (0)| 00:00:01 |
| 3 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 5 | RECURSIVE WITH PUMP | | | | | |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
5 - filter("P"<100)
A recursive CTE has three features that identify it. First, the query alias (“data” in this case) is followed by a list of column aliases; secondly the query includes a UNION ALL; and thirdly the second subquery in the UNION ALL references the query alias – i.e. it’s the recursive bit. There are other optional bits but I’m not planning to go into those – all I want to talk about is how to control the materialization (or not) of a recursive CTE through hinting.
The reason I wrote this note was because Jeff Jacobs, in his presentation on “Performance Anti-patterns” at RMOUG last week, raised the question of whether or not the /*+ materialize */ and /*+ inline */ hints worked with recursive CTEs and gave an example of a UNION ALL query where the CTE always materialized, no matter how you applied the /*+ inline */ hint. The CTE seemed to be following the basic guideline for CTEs – if you use it once in the main query it goes inline, if you use it more than once it will (almost invariably) materialize.
I’m always interested in examples where “the hint is ignored”, so I exchanged a couple of email messages with Jeff and he sent me an example (which I’ve simplified for this blog) of a query that demonstrated the issue; and I spent a little while thinking about it and decided that it simply wasn’t possible to hint the code the way we wanted to and it was just one of those cases where it takes a bit of time for new features to catch up and fit in to the standard framework. Here’s a simplified version of the query, with its execution plan:
with data(p) as (
select 1 p from dual
union all
select p + 1 from data where p < 100
)
select p
from data
where rownum <= 10
union all
select p
from data
where rownum <= 10
;
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 4 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6608_7391CD7 | | | | |
| 3 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 5 | RECURSIVE WITH PUMP | | | | | |
| 6 | UNION-ALL | | | | | |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | VIEW | | 2 | 26 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_7391CD7 | 2 | 12 | 2 (0)| 00:00:01 |
|* 10 | COUNT STOPKEY | | | | | |
| 11 | VIEW | | 2 | 26 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_7391CD7 | 2 | 12 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("P"<100)
7 - filter(ROWNUM<=10)
10 - filter(ROWNUM<=10)
The following morning I woke up with one of those “overnight insights” where you seem to have worked out the answer in your sleep. To make a hint work you have to put it in the right query block, or you have to name the right query block in the main query block: in this case the right query block doesn’t exist in the text, and it’s not possible to figure out what the name of the right query block would be if it came into existence.
If you try putting the /*+ inline */ hint into the query after the select at line 2 above, you’ve put the hint into the first query block of a union all, NOT into the query block of the recursvie CTE.
Having identified the problem, the solution (or at least, a possible solution) was obvious – create the query block you need. This (with its execution plan from 11.2.0.4) is what worked:
with data(p) as (
select 1 p from dual
union all
select p + 1 from data where p < 100
),
data1 as (
select /*+ inline */ * from data
)
select p
from (
select * from data1 where rownum <= 10
union all
select * from data1 where rownum <= 10
)
;
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 8 (0)| 00:00:01 |
| 1 | VIEW | | 4 | 52 | 8 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 2 | 26 | 4 (0)| 00:00:01 |
| 5 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 7 | RECURSIVE WITH PUMP | | | | | |
|* 8 | COUNT STOPKEY | | | | | |
| 9 | VIEW | | 2 | 26 | 4 (0)| 00:00:01 |
| 10 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 12 | RECURSIVE WITH PUMP | | | | | |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=10)
7 - filter("P"<100)
8 - filter(ROWNUM<=10)
12 - filter("P"<100)
All I’ve done is create a second CTE that selects from the first CTE. This is now a simple select, so I can add a perfectly placed hint to it – in the hope that this would effectively require the dependent recursive CTE to be inlined inside it. It seems to be sufficient.
I haven’t tested the strategy exhaustively – so I can give you no guarantee that this has to work – unfortunately I did have another example that I applied the method to, and after several seconds of no response it crashed with an ORA-00600 error :( but that might have been a side effect of the nature of query (it included a couple of the optional extras) rather than a specific feature of inlining.)
