If you’ve ever used subquery factoring (“with” subqueries or common table expressions (CTEs) as they are often called) then you’re probably aware of the (undocumented) hints /*+ materialize */ , which forces Oracle to create a local temporary table to hold the result of the subquery for subsequent use, and /*+ inline */, which forces the optimizer to copy the text of the subquery into the body of the query before starting the optimisation phase.
There’s a small, but important, enhancement to these hints that appeared in Oracle 18. Like so many other hints in Oracle they can now have a query block name as a “parameter”, so you can use them at the top level of your query. Here’s some code to demonstrate:
rem
rem Script: inline_hint.sql
rem Author: Jonathan Lewis
rem Dated: Oct 2020
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem 18.3.0.0
rem 12.2.0.1 -- hints don't have any effect
rem
create table t1
as
select *
from all_objects
where rownum <= 10000 -- > comment to avoid wordpress format issue
/
create index t1_i1 on t1(object_id);
create table t2
as
select *
from t1
/
create index t2_i1 on t2(object_id);
spool inline_hint.lst
explain plan for
with v1 as (
select
/*+
qb_name(cte)
*/
object_id, object_type, object_name
from t2 gtt1
where object_id is not null
)
select
/*+
qb_name(main)
inline(@cte)
*/
t1.object_id,
t1.object_name
from
t1
where
exists (
select
null
from
v1 v1a
where
v1a.object_id = t1.object_id
and v1a.object_type = 'TABLE'
)
and exists (
select
null
from
v1 v1b
where
v1b.object_id = t1.object_id
and v1b.object_name like 'WRI%'
)
and
t1.object_id between 100 and 200
/
select * from table(dbms_xplan.display(format=>'alias'));
explain plan for
with v1 as (
select
/*+
qb_name(cte)
*/
object_id, object_type, object_name
from t2 gtt1
where object_id is not null
)
select
/*+
qb_name(main)
materialize(@cte)
*/
t1.object_id,
t1.object_name
from
t1
where
exists (
select
null
from
v1 v1a
where
v1a.object_id = t1.object_id
and v1a.object_type = 'TABLE'
)
and
t1.object_id between 100 and 200
/
select * from table(dbms_xplan.display(format=>'alias'));
The first of these two queries uses the factored subquery twice so, by default, it will create a “cursor duration memory” temporary table to hold the results of the subquery and then use that temporary table twice in the execution plan.
Conversely the second query uses the factored subquery just once, so the optimizer’s default action will be to copy the text into the body of the main query and optimize the whole thing as a single query block.
To reverse the default behaviour in versions of Oracle up to 12.2.0.1 (though later patch sets may include the 18c enhancements) you could add the /*+ inline */ or /*+ materialize */ hints respectively to the factored subqueries; but my demonstration you can see that I’ve given the factored subquery a query block name and added the relevant hint to the main query block passing in the query block name of the factored subquery – hence /*+ inline(@cte) */ and /*+ materialize(@cte) */.
Here – from 19.3 – are the resulting execution plans (with some cosmetic editing) – first the plan with the inline() hint.
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 63 | 9 (12)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 1 | 63 | 9 (12)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 50 | 7 (15)| 00:00:01 |
| 3 | SORT UNIQUE | | 1 | 25 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 25 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 48 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 25 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T1_I1 | 1 | | 1 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 1 | 13 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | T2_I1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$06B48120
4 - SEL$06B48120 / GTT1@CTE
5 - SEL$06B48120 / GTT1@CTE
6 - SEL$06B48120 / T1@MAIN
7 - SEL$06B48120 / T1@MAIN
8 - SEL$06B48120 / GTT1@CTE
9 - SEL$06B48120 / GTT1@CTE
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("OBJECT_NAME" LIKE 'WRI%')
5 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=200)
7 - access("OBJECT_ID"="T1"."OBJECT_ID")
filter("T1"."OBJECT_ID"<=200 AND "T1"."OBJECT_ID">=100)
8 - filter("OBJECT_TYPE"='TABLE')
9 - access("OBJECT_ID"="T1"."OBJECT_ID")
filter("OBJECT_ID"<=200 AND "OBJECT_ID">=100)
As you can see Oracle has copied the subquery text into the main body of the text and then optimized to produce a three-table join. One of the subqueries has been unnested into an aggregate view (operations 3,4,5), the other has been transformed into a semi-join.
In passing you’ll also notice that the optimizer has used transitive closure to add the range predicate on t1 to both occurrences of the t2 table.
And here’s the plan for the query with the single use of the subquery and materialize() hint:
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48 | 2448 | 39 (8)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6611_F53A566 | | | | |
| 3 | TABLE ACCESS FULL | T2 | 10000 | 322K| 27 (8)| 00:00:01 |
|* 4 | HASH JOIN SEMI | | 48 | 2448 | 13 (16)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 48 | 1200 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1_I1 | 48 | | 2 (0)| 00:00:01 |
|* 7 | VIEW | | 10000 | 253K| 8 (13)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_F53A566 | 10000 | 322K| 8 (13)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$A3F38ADC
2 - CTE
3 - CTE / GTT1@CTE
5 - SEL$A3F38ADC / T1@MAIN
6 - SEL$A3F38ADC / T1@MAIN
7 - SEL$AA28F105 / V1A@SEL$1
8 - SEL$AA28F105 / T1@SEL$AA28F105
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("V1A"."OBJECT_ID"="T1"."OBJECT_ID")
6 - access("T1"."OBJECT_ID">=100 AND "T1"."OBJECT_ID"<=200)
7 - filter("V1A"."OBJECT_TYPE"='TABLE' AND "V1A"."OBJECT_ID">=100 AND "V1A"."OBJECT_ID"<=200)
In this plan the optimizer has created an in-memory temporary table and then used it in the existence subquery – which it has then transformed into a semi-join, so we have a query block with the name SEL$A3F38ADC; but we also see that the query block CTE still exists, labelling the operations that Oracle used to populate the temporary table.
It is an interesting (and irritating) detail that when we look at object aliases we see (operation 8) that Oracle has given the temporary table the alias of t1 – which is just a little confusing since I actually have a table called t1!
Next Steps
Being able to nominate a query block for the inline() and materialize() hints may be of great help in some cases (there’s a recent example on the Oracle Developer Forum (may need a MOS login) where it might make a huge difference to the performance of a particular query without requiring a rewrite of the SQL).
But there are a couple of details to investigate. First, I had a query block name built into my factored subquery – what happens if the author of the SQL didn’t include a query block name?
Before I’d added the inline() hint and query block names in the first example above this is what the plan looked like:
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48 | 6240 | 48 (11)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6612_F53A566 | | | | |
| 3 | TABLE ACCESS FULL | T2 | 10000 | 322K| 27 (8)| 00:00:01 |
|* 4 | HASH JOIN SEMI | | 48 | 6240 | 21 (15)| 00:00:01 |
|* 5 | HASH JOIN SEMI | | 48 | 4992 | 13 (16)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 48 | 1200 | 4 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T1_I1 | 48 | | 2 (0)| 00:00:01 |
|* 8 | VIEW | | 10000 | 771K| 8 (13)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_F53A566 | 10000 | 322K| 8 (13)| 00:00:01 |
|* 10 | VIEW | | 10000 | 253K| 8 (13)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_F53A566 | 10000 | 322K| 8 (13)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$A317D234
2 - SEL$1
3 - SEL$1 / GTT1@SEL$1
6 - SEL$A317D234 / T1@SEL$2
7 - SEL$A317D234 / T1@SEL$2
8 - SEL$D67CB2D2 / V1B@SEL$4
9 - SEL$D67CB2D2 / T1@SEL$D67CB2D2
10 - SEL$D67CB2D3 / V1A@SEL$3
11 - SEL$D67CB2D3 / T1@SEL$D67CB2D3
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("V1A"."OBJECT_ID"="T1"."OBJECT_ID")
5 - access("V1B"."OBJECT_ID"="T1"."OBJECT_ID")
7 - access("T1"."OBJECT_ID">=100 AND "T1"."OBJECT_ID"<=200)
8 - filter("V1B"."OBJECT_NAME" LIKE 'WRI%' AND "V1B"."OBJECT_ID">=100 AND "V1B"."OBJECT_ID"<=200)
10 - filter("V1A"."OBJECT_TYPE"='TABLE' AND "V1A"."OBJECT_ID">=100 AND "V1A"."OBJECT_ID"<=200)
As you can see, the factored subquery (operations 2 and 3) has the query block name of sel$1 and the main query (operations 6 an 7 where the real t1 is used) has the query block name sel$2. So without giving the subquery a name I could have used the hint /*+ inline(@sel$1) */ in the main query block.
This takes us on to the second point that needs investigation. If you’ve looked at the example on the Oracle Developer Forum you will have seen that there’s an SQL statement that references a stored view and the factored subquery of interest is defined in the view. This means we might be able to edit the query that calls the view to include a hint referencing the query block inside the view – but then what do we do if we can’t edit the main query itself?
To be investigated (1) – would the inline() hint with nominated query block work if the factored subquery was inside a stored view that we were using in our query?
To be investigated(2) – if (1) works, could we achieve the same result by using an SQL Patch to attach the hint to the main query text without editing the main query?
Update (Oct 2020)
It turns out that I discovered this enhancement a few months ago while doing some experimentation with recursive subquery factoring.
Update Nov 2020
A blog note from Nenad Noveljic warns of a surprising ORA-07445 if you get too trigger-happy with the inline() and materialize() hints.
Oracle Scratchpad 