In October, Jonathan Lewis offered a free one hour lecture on one SQL statement. Jonathan Lewis is one of the world’s foremost Oracle experts and Oracle Magazine’s 2006 “Oracle Author of the Year.” He is a member of the prestigious Oak Table Network, a network for Oracle Scientists, and has been an independent design and troubleshooting consultant for more than 12 years.
Here, he examines a single SQL statement several different ways and shows that the optimum execution path depends on a good understanding of the data. In his lecture, we are going to examine variations of a single query, involving a couple of subqueries, to help us understand the what Oracle can do with a query, what it can’t do, and how we can determine what we want it to do.
In this article, we are going to start with the basic framework of a complex query, and gradually take it apart looking at different optimizer options in isolation. Once we’ve done that, we can put the query back together and examine the optimizer’s basic strategy, and what this means in terms of what is and is not possible. Finally, we note that the “best” execution path may be one that we choose because we have a solid understanding of the data. We may even decide on an execution path that the optimizer is literally unable to produce unaided.
So this is the basic structure of our query. We have a simple join between two tables, with some simple filter conditions on both tables, and two subqueries, one operating against each table. In it’s most general form, the predicates could be combined with a mixture of Ands and ORs; the subqueries could be INs, EXISTS, or their negation; or could simple be arithmetic comparisons on correlated, or non-correlated subqueries.
Today’s Generic Query
Select
{list of columns}
from t1, t3
where
{access/filter predicate(s) on t1}
and/or {subquery predicate on t1}
and {join predicate(s) t1 to t3}
and/or {access/filter predicate(s) on t3}
and {subquery predicate on t3}
Specific Example
Here, to make the general structure more concrete, is an example with a couple of simple correlated existence tests. We could imagine all sorts of ways in which the optimizer (or we) might choose to operate this query to get the result set with the minimum amount of work. Before we look at the whole thing, though, let’s take parts of it in isolation.
select /*+ qb_name(main) */ — Query Block name
tA.v1
from tA, tC
where tA.n2 = 15 — filter predicate
and exists (select /*+ qb_name(subq2) */ null
from tB
where tB.n1 = 15
and tB.id = tA.id — correlation
)
and tC.n1 = tA.n1 — join predicate
and tC.n2 = 15 — filter predicate
and exists (select /*+ qb_name(subq4) */ null
from tD
where tD.n1 = 15
and tD.id = tC.id — correlation
)
Use of /*+ qb_name() */
We’ll start with a 10g feature – the qb_name() hint. Every single query block (roughly speaking every subquery) in a SQL statement can be given a name with the qb_name() hint. Once a query block has been named, we can make references to object in that query block from higher levels of the statement in the hints that we write. Every hint (probably) can have a new ‘first parameter’ which is the name of the query block to which it applies. The name has to be preceded with the @ sign. Tables can then be qualified with the query block name (again with the @ sign) .
select
/*+
qb_name(main)
leading(tA@main, tC@main, tB@subq2)
unnest(@subq2)
no_unnest(@subq4)
no_push_subq(@subq4)
index(@subq4 tD@subq4(id))
*/
tA.v1
from tA, tC
where tA.n2 = 15
and exists (select /*+ qb_name(subq2) */ … tB …)
and tC.n1 = tA.n1
and tC.n2 = 15
and exists (select /*+ qb_name(subq4) */ … tD …)
The “outline” details
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.3’)
ALL_ROWS
OUTLINE(@”SUBQ4″)
OUTLINE(@”MAIN”)
OUTLINE(@”SUBQ2″)
OUTLINE_LEAF(@”SUBQ4″)
OUTLINE_LEAF(@”SEL$38F5B5F8″)
UNNEST(@”SUBQ2″)
FULL(@”SEL$38F5B5F8″ “TA”@”MAIN”)
FULL(@”SEL$38F5B5F8″ “TC”@”MAIN”)
INDEX_RS_ASC(@”SEL$38F5B5F8″ “TB”@”SUBQ2” (“TB”.”ID”))
LEADING(@”SEL$38F5B5F8″ “TA”@”MAIN” “TC”@”MAIN” “TB”@”SUBQ2″)
USE_HASH(@”SEL$38F5B5F8” “TC”@”MAIN”)
USE_NL(@”SEL$38F5B5F8″ “TB”@”SUBQ2″)
INDEX_RS_ASC(@”SUBQ4” “TD”@”SUBQ4” (“TD”.”ID”))
END_OUTLINE_DATA
*/
In this example, you’ll also notice the enhanced version of the leading() hint in 10g – you can specify every table in the query in the right order, and if there is a legal way to get to that order, Oracle will use it as the single join order it examines to optimize the query. We haven’t included t4 in the leading() hint because it is set to “not unnest” and. So it is going to run as a late subquery. Effect of @qb_name Here’s the execution plan for our original query with the leading() hint from the previous slide included. leading(t1@main, t3@main, t2@subq2) Note that the order is t1, t3, then t2 with a semi-join, and t4 (no_unnest) as a filter. One of the options for dbms_xplan.display() allows use to see table aliases and query block names – so we can see that we have a transformed block of three tables, plus one block which has not been transformed. (Could we use no_query_transformation(@subq4) ? YES !!
in / exists
IN can be transformed to EXISTS
EXISTS can be transformed to:
An inline view – unnesting
possibly followed by complex view merging (from 10g onwards)
A Semi-join
Or it may be operated as:
An access (index driving) subquery
A filter (for each row) subquery
not in / not exists
NOT IN is not the opposite to IN, so
NOT IN cannot always be changed to NOT EXISTS
NOT IN cannot always be unnested
NOT IN sometimes has to run as a filter.
It’s all due to nulls:
IN means “ColX = ColY” is TRUE at least once
NOT IN means “ColX != ColY” is TRUE every single time.
Repeat after me: NULLS are nasty. Remember that NULL causes problems. In Oracle a predicate may fail to be true either because it is false or because it evaluates to null.
in / not in
The problem with IN/NOT IN can best be seen without going to subqueries. Both examples here test a single value against a list of values.
X in (value1, value2, value3) means
X = value1 — OR means the test is true as
or X = value2 — soon as one line returns TRUE
or X = value3
With IN, it will stop at the first result.
X not in (value1, value2, value3) means
X != value1 — AND means the test is true
and X != value2 — only if every line returns TRUE
and X != value3 — must return TRUE.
With NOT IN, all results have to be true.
Semi-join
Let’s go back to the transformations available for a single subquery. In this case we could have a small volume of data in t1 that can be accessed very efficiently by an index on n2; and an efficient access path to find the corresponding rows in t2. So Oracle converts the query into a nested loop join – stopping at the first success at t2 for every row it hits on t1.
select small_vc
from t1
where n2 between 10 and 200
and exists (select null from t2
where t2.n2 = t1.n1 and t2.n2 = 15
);
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 23 | 3 |
| 1 | NESTED LOOPS SEMI | | 1 | 23 | 3 |
|* 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 19 | 2 |
|* 3 | INDEX RANGE SCAN | T1_PK | 1 | | 1 |
|* 4 | INDEX RANGE SCAN | T2_N2 | 1 | 4 | 1 |
Anti-join
select *
from
t1
where v_id not in (
select v1 from t2
);
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 48 | 6 |
|* 1 | HASH JOIN ANTI | | 1 | 48 | 6 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 43 | 3 |
| 3 | TABLE ACCESS FULL | T2 | 1 | 5 | 3 |
A “not in” that can change to an anti-join can be more efficient than a filter if the alternative is a frequent and labour-intensive visit to the second table.
“Visible” Unnesting:
But if there is no efficient access path into t2, the plan has to change. It could become a hash semi-join into t2. However, we have an independent predicate on t2 that returns a few rows very efficiently, and we have an efficient way of getting into t1 based on the value of n1. So the optimizer can generate a unique set of values from t2, then use those as the outer table in a nested loop join into t1.
select small_vc
from t1
where n2 between 10 and 200
and exists (select null from t2
where t2.mod1 = t1.n1 and t2.n2 = 15
);
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 26 | 24 |
|* 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 19 | 2 |
| 2 | NESTED LOOPS | | 1 | 26 | 24 |
| 3 | SORT UNIQUE | | 1 | 7 | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 2 |
|* 5 | INDEX RANGE SCAN | T2_N2 | 1 | | 1 |
|* 6 | INDEX RANGE SCAN | T1_PK | 1 | | 1 |
Access subquery
In this case (which uses a completely different table structure) we have a unique index on (id_parent, id_child), and we are doing a classic: “most recent of” query. The subquery is executed first, and the single result is used as an access predicate to allow Oracle a unique access into a unique index.
select small_vc
from min_max mm1 — pk(id_parent, id_child)
where mm1.id_parent = 100
and mm1.id_child = (select max(mm2.id_child)
from min_max mm2
where mm2.id_parent = 100
);
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 108 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID | MIN_MAX | 1 | 108 | 2 |
|* 2 | INDEX UNIQUE SCAN | MM_PK | 1 | | 1 |
| 3 | SORT AGGREGATE | | 1 | 8 | |
| 4 | FIRST ROW | | 10 | 80 | 2 |
|* 5 | INDEX RANGE SCAN (MIN/MAX)| MM_PK | 10 | 80 | 2 |
The nature of the subquery and indexes causes Oracle to run the subquery to return a single value, which becomes the index’s access predicate in line 2.
If in doubt about how the subquery is used, check the predicates section of the plan. Do you see the subquery as access_predicates or filter_predicates?
Predicate Information (identified by operation id):
2 – access(“MM1”.”ID_PARENT”=100 AND “MM1”.”ID_CHILD”= (
SELECT /*+ */ MAX(“MM2″.”ID_CHILD”)
FROM “MIN_MAX” “MM2”
WHERE “MM2”.”ID_PARENT”=100))
5 – access(“MM2”.”ID_PARENT”=100)
Filter subquery (with OR – a)
In this case, because of the OR clause, the optimizer cannot unnest the subquery – it has to pick up all the rows where n2 between 100 and 200, but also has to examine every other row in the table to check the subquery. In principle, Oracle will run the subquery once for EVERY single row in the table (except the ones that have already passed the n2 test).
select small_vc
from t1
where n2 between 100 and 200
or exists (select null from t2
where t2.n1 = t1.n1 and t2.mod1 = 15
);
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 597 | 11343 | 28 |
|* 1 | FILTER | | | | |
|* 2 | TABLE ACCESS FULL | T1 | 10000 | 185K| 28 |
|* 3 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 7 | 2 |
|* 4 | INDEX RANGE SCAN | T2_PK | 1 | | 1 |
The presence of the OR clause makes it impossible (at present) for Oracle to check both predicates on t1 efficiently. It uses an “inclusive” path.
Filter subquery (with OR – b)
Sometimes you just write the unnest manually. In this case we use OR expansion (not yet implemented) to get two non-colliding results. Watch out for NULL effects.
select small_vc — manual rewrite IF …
from t1
where n2 between 100 and 200 — this can be efficient
union all
select small_vc
from t1
where exists ( — and this unnested efficiently
select null
from t2
where t2.n1 = t1.n1
and t2.mod1 = 15
)
and not (n2 between 100 and 200)
At the bottom, we still have to accommodate for n2 possibly being a NULL. So we rewrite this part using a NVL function.
— and not (nvl(n2,-1) between 100 and 200)
In 10g, Oracle has a newly documented function called LNNVL which does the same thing. It’s a conditional boolean function that returns TRUE whenever the condition passed on as argument is unkown or FALSE.
— lnnvl(n2 between 100 and 200) — 10g
If the condition is 100% sure thing, absolutely true, the function LNNVL throws FALSE. Otherwise, it will ring the bell and return TRUE.
There is some aid from the problem of running a filter subquery for every row in the table. A feature (from 8.0) known as scalar subquery caching. In general it is quite good to go for unnesting, because filter subqueries are unpredictable due to the hash algorithm used for caching. How many times could this run ? Min 6, max 20,000. Response time 0.01 -> 150 seconds CPU. A small change in the actual data (a rogue value appearing) could make a massive difference in the number of times the query runs. In 8i and 9i you get 256 values in the hash table. In 10g you get seem to get 1024 for numbers, but the table size is limited to 64KB in 10g, which means problems for unconstrained text values which deemed to be 4,000 bytes each.
If you cannot unnest, and if you have wild fluctuations in performance because of hash collisions, you could ensure the values supplied to the subquery appear in order – then oracle run-time simply remembers the return values from “the previous” row. But this is a little dangerous – you should not depend on internal mechanisms.
Filter Postponement
Another feature to be aware of with filter subqueries is there effect on the arithmetic, and how this is incompatible with their impact at runtime. We have a query here where I can include or exclude the subquery. The query joins t1 and t3, but the subquery is against t1. Check the plans and the cardinality.
select
/*+ ordered */
t1.v1
From t1, t3
Where t1.n2 = 15
/*
and exists (select –+ no_unnest qb_name(subq2)
null
from t2
where t2.n1 = 15
and t2.id = t1.id
)
*/
and t3.n1 = t1.n1
and t3.n2 = 15
;
Check the execution plan of this query with, and without, the subquery. This illustrates a feature of how Oracle handles filter subqueries – the filter is against the first table in the join – but Oracle “postpones” filter subqueries.
Execution Plan 9.2.0.8 – no subquery
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 173 | 3979 | 109 |
|* 1 | HASH JOIN | | 173 | 3979 | 109 |
|* 2 | TABLE ACCESS FULL | T1 | 157 | 2355 | 54 |
|* 3 | TABLE ACCESS FULL | T3 | 157 | 1256 | 54 |
Execution Plan 9.2.0.8 – with subquery
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 9 | 252 | 127 |
|* 1 | FILTER | | | | |
|* 2 | HASH JOIN | | 9 | 252 | 109 |
|* 3 | TABLE ACCESS FULL | T1 | 8 | 160 | 54 |
|* 4 | TABLE ACCESS FULL | T3 | 157 | 1256 | 54 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 2 |
|* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 |
Without the subquery, the cardinality of t1 is 157, and the join cardinality it 173. With the subquery, the cardinality of t1 drops to 5% of 157 – which reduces the join cardinality to 9. But the subquery does not run until AFTER the join has been operated. So the number of calls to the subquery is 20 times larger than the number costed for. This type of change could result in an indexed nested loop instead of a hash join – have two subqueries against t1 and it becomes very likely, as you get the selectivity down to 5% of 5%.
10g behaves much better than 9i in this timing/costing split. But you can improve 9i (and earlier) by using the push_subq hint – which changes its use in 10g: current 9i use of this hint WILL NOT WORK on the next upgrade.
select
/*+ ordered push_subq */ — 9i use of hint
/*+ ordered push_subq(@subq2) */ — 10g use of hint
/*+ no_unnest(@subq2) */ — 10g use of hint
t1.v1
From t1, t3
Where t1.n2 = 15
and exists (select –+ no_unnest qb_name(subq2) push_subq
null
from t2
where t2.n1 = 15
and t2.id = t1.id
)
and t3.n1 = t1.n1
and t3.n2 = 15
;
Execution Plan 10.2.0.3 – without pushing
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 28 | 369 (2)| 00:00:05 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 173 | 4844 | 195 (2)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T1 | 157 | 3140 | 97 (2)| 00:00:02 |
|* 4 | TABLE ACCESS FULL | T3 | 157 | 1256 | 97 (2)| 00:00:02 |
|* 5 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 8 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 |
Execution Plan 10.2.0.3 – pushed
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
| 0 | SELECT STATEMENT | | 9 | 252 | 197 (2)| 00:00:03 |
|* 1 | HASH JOIN | | 9 | 252 | 195 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL (filter) | T1 | 8 | 160 | 97 (2)| 00:00:02 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T3 | 157 | 1256 | 97 (2)| 00:00:02 |
Without pushing, the 10g plan shows a late running subquery – but the t1 cardinality and join cardinality have not suffered the 5% factor. When the subquery is pushed, the 5% appears in the right place. Note – however – that the FILTER operation has gone missing. It has been squashed into the tablescan of t1 – which really ought to be the filter line, and the scan of t1 should be one line down and one step to the right. Filter Subquery join effects Another aspect of filter subqueries if we do not push them is that the number of times the subquery runs can be affected by the join method that takes place before the subquery. We can see operation counts by enabling rowsource execution statistics. This can be expensive on CPU usage, and should be done rarely. It populates the view v$sql_plan_statistics – and there is a hint in 10g to make this easy. Filter Subquery join effects Querying dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’) we can see the statistics for the last run of the most recent statement we executed. (You need to have serveroutput off for this to work, or the last statement will be the call to dbms_output.get_lines). With the nested loop join, the data order of the driving column for the subquery s dictated by the first table – and this maximises the benefit of scalar subquery caching. With the hash join, the data order of the driving column for the subquery is dictated by the second table. And this can mean we run the subquery more times.
Join Orders
alter session set events ‘10053 trace name context forever’;
select /*+ qb_name(main) */
outer.*
from emp outer — 20,000 rows
where outer.sal > (
select /*+ qb_name(sub) */
avg(inner.sal)
from
emp inner
where
inner.dept_no = outer.dept_no
)
;
find “Join order[” {filename}
grep “Join order[” {filename}
It’s not just run-time that can be affected. It’s worth checking how hard the optimizer has to work when handling a statement with subqueries. It changes with version of Oracle. A simple check in the 10053 trace file (CBO trace) is to look for the number of lines where Oracle reports a ‘Join Order’ to see how many get examined. Consider this query where we want the employees that earn more than the average salary for their department. 9i and 10g produced the same plan (unnest, inline view and hash join) for this query – although 10g did a hash group by – but how did they get to that decision.
Join order[1]: EMP[INNER]#0 (simple unnest)
Join order[1]: VW_SQ_1[VW_SQ_1]#0 EMP[OUTER]#1
Join order[2]: EMP[OUTER]#1 VW_SQ_1[VW_SQ_1]#0
Join order[1]: EMP[INNER]#0 EMP[OUTER]#1 (unnest with CVM)
Join order[2]: EMP[OUTER]#1 EMP[INNER]#0
Join order[1]: EMP[INNER]#0 (yet more CPU with 10g)
Join order[1]: EMP[OUTER]#0
Join order[1]: EMP[INNER]#0
Join order[1]: VW_SQ_3[VW_SQ_3]#0 EMP[OUTER]#1
Join order[2]: EMP[OUTER]#1 VW_SQ_3[VW_SQ_3]#0
Join order[1]: EMP[INNER]#0 EMP[OUTER]#1
Join order[2]: EMP[OUTER]#1 EMP[INNER]#0
Join order[1]: EMP[INNER]#0
Join order[1]: VW_SQ_1[VW_SQ_1]#0 EMP[OUTER]#1
Join order[2]: EMP[OUTER]#1 VW_SQ_1[VW_SQ_1]#0
The plans in Oracle 9, there are two stages only: Work out the strategy of the inline view. Work out the cost of joining the view to the base table. In 10g we then work out the cost of doing complex view merging between the view and the base table. Then we work out half a dozen other options. The optimization CPU, memory, and latch activity goes up significantly.
This might look like a threat in the case of our sample query – how many different things will the optimizer try to work out ? In fact, because we have simple existence subqueries with non-null columns and primary keys all over the place, we find that Oracle has invoked ‘non-costed’ transformation to turn the query into a straight four-table join. This means it only looks at one general plan – with 24 (4 * 3 * 2 * 1) join orders. This “non-costed” approach is not necessarily the right thing to do with multiple subqueries (in my opinion), and there are execution plans which simply cannot be created from a 4-table join.
These are the join orders examined – you will note that a few are skipped. This is because the initial stages of a previous join are the same, and the previous join order was aborted early as being more expensive than the current best option.
select — notional transformation (9i)
outer.*
from (
select dept_no, avg(sal) av_sal
from emp
group by dept_no
) inner,
emp outer
where
outer.dept_no = inner.dept_no
and outer.sal > inner.av_sal;
Despite some orders being skipped, we can force Oracle to evaluate and use the paths (or at least display them). This only works for very simple cases. What happens if you have multiple complex query blocks – you only get on choice of permutation number.
select — investigated transformation (10g)
outer.dept_no dept_no, outer.sal sal,
outer.emp_no emp_no, outer.padding padding
from
test_user.emp inner, test_user.emp outer
where
inner.dept_no = outer.dept_no
group by
inner.dept_no, outer.rowid,
outer.padding, outer.emp_no,
outer.sal, outer.dept_no
having
outer.sal > avg(inner.sal)
10g has ‘cost based query transformation’ (CBQT). It will cost the unnested subquery of the previous slide, and then cost for ‘complex view merging’. It is worth noting that the execution plans can appear to contradict the join orders. This is a common reason why people sometimes think that Oracle has ignored a hint. With HASH joins specifically, when you ‘join the next table’ – Oracle can choose whether that new table should be the build (hash) table or the probe (second) table. There is a hint swap_join_inputs() in 9i which forces it to be the build table; and a 10g hint no_swap_join_inputs() that forces it to be the probe table.
For our specific example of a four-table query we might expect to see a number of transformations in the trace:
SU: Considering subquery unnesting in query block MAIN (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on MAIN (#1).
SU: Checking validity of unnesting subquery SUBQ2 (#3)
SU: Passed validity checks.
SU: Transforming EXISTS subquery to a join.
SU: Checking validity of unnesting subquery SUBQ4 (#2)
SU: Passed validity checks.
SU: Transforming EXISTS subquery to a join.
It is also interesting to note that, although there are three (four) different strategies that Oracle could choose to transform a subquery, sometimes the options are constrained by the previous actions in the join order. In this case we see t4 used as an in-line view (because that’s the only way you can join t1 and t4 through the necessary cartesian merge join), and t3 being access through a semi-join. Being 10g, the semi-join has been set as a hash semi-join, which can then be reversed (to become an outer join the wrong way around). Every version brings new little details.
The meaning of the query
select /*+ qb_name(main) */ t1.v1
from t1, t3
where t1.n2 = 15
and exists (select /*+ qb_name(subq2) */ null
from t2
where t2.n1 = 15
and t2.id = t1.id
)
and t3.n1 = t1.n1
and t3.n2 = 15
and exists (select /*+ qb_name(subq4) */ null
from t4
where t4.n1 = 15
and t4.id = t3.id
)
Putting aside the technicalities of what transformations and join mechanisms are available, let’s review the query in terms of the data. What if…
What If (1a)
We start with t1
… and the join to t3 eliminates data.
… but the filter to t2 eliminates more data
So we start with t1 and run the t2 subquery first.
.
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 49 | 158 |
| 1 | NESTED LOOPS SEMI | | 1 | 49 | 157 |
| 2 | NESTED LOOPS | | 3 | 123 | 157 |
| 3 | NESTED LOOPS SEMI | | 3 | 84 | 70 |
|* 4 | TABLE ACCESS FULL | T1 | 157 | 3140 | 54 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 1 |
|* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | | |
|* 7 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 13 | 29 |
|* 8 | INDEX RANGE SCAN | T3_N1 | 1 | | 1 |
|* 9 | TABLE ACCESS BY INDEX ROWID | T4 | 1 | 8 | 1 |
|* 10 | INDEX UNIQUE SCAN | T4_PK | 1 | | |
What If (1b)
BUT
… if the subquery cost is expensive,
… or the subsequent join method to t3 is a hash join either way,
… perhaps it is still better to go to t3 first.
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 49 | 115 |
| 1 | NESTED LOOPS SEMI | | 1 | 49 | 115 |
| 2 | NESTED LOOPS SEMI | | 1 | 41 | 112 |
|* 3 | HASH JOIN | | 10 | 330 | 109 |
|* 4 | TABLE ACCESS FULL | T1 | 157 | 3140 | 54 |
|* 5 | TABLE ACCESS FULL | T3 | 157 | 2041 | 54 |
|* 6 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 1 |
|* 7 | INDEX UNIQUE SCAN | T2_PK | 1 | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | T4 | 1 | 8 | 1 |
|* 9 | INDEX UNIQUE SCAN | T4_PK | 1 | | |
But, the subquery may be fairly expensive on disk or CPU, to so we might want to minise the number of times it runs. Conversely, the nature of the join to t3 (hence the cost of the join) may be unchanged by the fact that we have filtered data out already – so there may be no benefit in running the subquery early. So we could choose to join then filter. In this case, by filtering early, we changed the join to t3 from a hash join (expensive) to a nested loop (possibly cheaper – if it is a precise nested loop).
The meaning of the query (2)
select /*+ qb_name(main) */ t1.v1
from t1, t3
where t1.n2 = 15
and exists (select /*+ qb_name(subq2) */ null
from t2
where t2.n1 = 15
and t2.id = t1.id
)
and t3.n1 = t1.n1
and t3.n2 = 15
and exists (select /*+ qb_name(subq4) */ null
from t4
where t4.n1 = 15
and t4.id = t3.id
)
But notice that both subqueries have their own filter-only predicates. How much data would we get from each subquery, and how efficiently, if we started with one of these subquery tables?
What if …(2a)
On the other hand –
… Getting from t1 into t2 might be expensive
… but getting into t1 from t2 might be efficient
… so we could start by unnesting t2 and making it the driver
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 49 | 85 |
| 1 | NESTED LOOPS SEMI | | 1 | 49 | 85 |
|* 2 | HASH JOIN | | 3 | 123 | 84 |
| 3 | NESTED LOOPS | | 3 | 84 | 29 |
| 4 | SORT UNIQUE | | 3 | 24 | 7 |
|* 5 | TABLE ACCESS FULL | T2 | 3 | 24 | 7 |
|* 6 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 20 | 1 |
|* 7 | INDEX UNIQUE SCAN | T1_PK | 1 | | |
|* 8 | TABLE ACCESS FULL | T3 | 157 | 2041 | 54 |
|* 9 | TABLE ACCESS BY INDEX ROWID | T4 | 1 | 8 | 1 |
|* 10 | INDEX UNIQUE SCAN | T4_PK | 1 | | |
Sometimes we realise that the subqueries should be used to “drive” the main query – in this case the number of rows we get from t2 (on its own) is small.
What if …(2b)
But if we keep seeing the join from t1 to t3 is expensive
… but getting into t3 from t4 happens to be efficient
… and both t2 and t4 return very few rows from their driving predicates
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 49 | 45 |
| 1 | NESTED LOOPS | | 1 | 49 | 45 |
| 2 | NESTED LOOPS | | 3 | 87 | 42 |
| 3 | MERGE JOIN CARTESIAN | | 3 | 48 | 39 |
| 4 | SORT UNIQUE | | 3 | 24 | 7 |
|* 5 | TABLE ACCESS FULL | T4 | 3 | 24 | 7 |
| 6 | BUFFER SORT | | 3 | 24 | |
| 7 | SORT UNIQUE | | 3 | 24 | 6 |
|* 8 | TABLE ACCESS FULL | T2 | 3 | 24 | 6 |
|* 9 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 13 | 1 |
|* 10 | INDEX UNIQUE SCAN | T3_PK | 1 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 20 | 1 |
|* 12 | INDEX UNIQUE SCAN | T1_PK | 1 | | |
This execution plan seems unreasonable; everyone “knows” Cartesian joins are bad; but they aren’t if the result is small and moves on to precise indexes.
Impossible path
What if ..
… there is no good join from t1 to t3
… t2 and t4 both produce a lot of data when taken in isolation
… t1 when filtered by t2 produces a small data set
… t3 when filtered by t4 produces a small data set
Your query is really two separate queries, and you want to join the two result sets
The optimum execution plan is a “bushy tree”.
Oracle always tries to create “left-deep” trees.
There are cases where the optimum execution path is to run two (or more) queries, and then join the result sets. Oracle will not attempt to do this.
Bushy Tree SQL (method a)
select /*+ no_merge(m1) no_merge(m2) */ m1.v1
from
(select t1.n1, t1.v1 from t1
where n1 = 15
and exists (
select t2.id from t2
where t2.n1 = 15 and t2.id = t1.id
)
) m1,
(select t3.n1 from t3
where n1 = 15
and exists (
select t4.id from t4
where t4.n1 = 15 and t4.id = t3.id
)
) m2
where m2.n1 = m1.n1;
We can make Oracle do something it doesn’t want to do. Create a couple of intermediate results and join them. For example – compare this summary of this week’s sales with the same week last year. Oracle will tend to join two big sets and aggregate when we can see that we want to aggregate two sets and join. The no_merge hint (or possibly subquery factoring with the /*+ materialize */ hint) can work for use here. Note that the materialize will cause global temporary tables to be dumped to the temp tablespace, whereas no_merge uses PGA memory.
Bushy Tree plan (no_merge)
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 9 | 279 | 59 |
|* 1 | HASH JOIN | | 9 | 279 | 59 |
| 2 | VIEW | | 3 | 54 | 29 |
.
| 3 | NESTED LOOPS | | 3 | 72 | 29 |
| 4 | SORT UNIQUE | | 3 | 24 | 7 |
|* 5 | TABLE ACCESS FULL | T2 | 3 | 24 | 7 |
|* 6 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 16 | 1 |
|* 7 | INDEX UNIQUE SCAN | T1_PK | 1 | | |
| 8 | VIEW | | 3 | 39 | 29 |
| 9 | NESTED LOOPS | | 3 | 51 | 29 |
| 10 | SORT UNIQUE | | 3 | 24 | 7 |
|* 11 | TABLE ACCESS FULL | T4 | 3 | 24 | 7 |
|* 12 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 9 | 1 |
|* 13 | INDEX UNIQUE SCAN | T3_PK | 1 | | |
This is the execution plan. VIEW means “in memory result set generated”. Then we hash join the two sets.
Bushy Tree SQL (method b)
with part1 as (
select /*+ materialize */ t1.n1, t1.v1 from t1
where n1 = 15
and exists (
select t2.id from t2
where t2.n1 = 15 and t2.id = t1.id
)
),
part2 as (
select /*+ materialize */ t3.n1 from t3
where n1 = 15
and exists (
select t4.id from t4
where t4.n1 = 15 and t4.id = t3.id
)
)
select part1.v1 from part1, part2 where part2.n1 = part1.n1;
The materialize hint is not (yet) documented. When factored subqueries are materialized, they cause direct written and scattered reads on the temp tablespace.
Bushy Tree plan (subq factoring)
|Id |Operation |Name |Rows|Bytes|Cost|
| 0|SELECT STATEMENT | | 1| 31| 63|
| 1| TEMP TABLE TRANSFORMATION | | | | |
| 2| LOAD AS SELECT | | | | |
| 3| NESTED LOOPS | | 3| 72| 29|
| 4| SORT UNIQUE | | 3| 24| 7|
|* 5| TABLE ACCESS FULL |T2 | 3| 24| 7|
|* 6| TABLE ACCESS BY INDEX ROWID|T1 | 1| 16| 1|
|* 7| INDEX UNIQUE SCAN |T1_PK | 1| | |
| 8| LOAD AS SELECT | | | | |
| 9| NESTED LOOPS | | 3| 51| 29|
| 10| SORT UNIQUE | | 3| 24| 7|
|*11| TABLE ACCESS FULL |T4 | 3| 24| 7|
|*12| TABLE ACCESS BY INDEX ROWID|T3 | 1| 9| 1|
|*13| INDEX UNIQUE SCAN |T3_PK | 1| | |
|*14| HASH JOIN | | 1| 31| 5|
| 15| VIEW | | 3| 54| 2|
| 16| TABLE ACCESS FULL |SYS_TEMP_0FD9D6605_CB74C8| 3| 33| 2|
| 17| VIEW | | 3| 39| 2|
| 18| TABLE ACCESS FULL |SYS_TEMP_0FD9D6606_CB74C8| 3| 12| 2|
If you use subquery factoring the ultimate plan is a very simple hash join. Some optimizer features are disabled (e.g. CVM) if you use this feature.
Summary – Know your Data
Does the query decompose ?
Consider each component separately. Start small and look for the table returning the least amount of data.
For the next step – in “loose” order of preference:
Aggregate to reduce the current data size
Pick a table that eliminates rows from the set
Pick a table that only adds columns to the set
Pick a table that multiplies the rows
Keep looking ahead a couple of steps
How much smaller / bigger will the data set be ?
What’s the impact of the join and access methods ?
If you know what your data looks like, and what the query is supposed to do, you can produce a plan that minimises the work at (nearly) every step.