Occasionally a join using primary key columns in a query can be eliminated because of the data involved. If Oracle determines that all of the ‘required’ data is available in a single table, then the optimizer will rewrite the query to eliminate the ‘unnecessary’ join. Oracle has been doing this for years with the limitation that the primary keys involved were single-column keys. In versions 12.2 and later, multi-column primary keys can be used, however there are cases where the join elimination isn’t complete depending upon table order in the FROM clause or on the join syntax in use. Let’s look at an example that illustrates these points.
The following example uses three tables — my_table, my_other_table, and my_other_other_table — in various parent->child relationships. [Building the tables won’t be covered here, as we are interested only in the join elimination results.] by using the traditional Oracle syntax for the joins, the table order can affect the extent of the join elimination. Remember that the goal is to reduce the query to its simplest form having a minimum of join conditions (ideally, none). Using the traditional Oracle syntax, joining the tables in the following order:
my_other_other_table,
my_other_table,
my_tablec
we eliminate only the join involving my_other_table:
BING @ orclpdb1 >
BING @ orclpdb1 > select
2 count(c.my_varch)
3 from
4 my_other_other_table g,
5 my_other_table p,
6 my_table c
7 where
8 c.my_num between 200 and 215
9 and p.my_other_id = c.my_other_id
10 and p.my_other_other_id = c.my_other_other_id
11 and g.my_other_other_id = p.my_other_other_id
12 ;
COUNT(C.MY_VARCH)
-----------------
80
BING @ orclpdb1 >
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID fmu4cu5mqprtt, child number 0
-------------------------------------
select count(c.my_varch) from my_other_other_table g,
my_other_table p, my_table c where c.my_num between
200 and 215 and p.my_other_id = c.my_other_id and p.my_other_other_id
= c.my_other_other_id and g.my_other_other_id = p.my_other_other_id
Plan hash value: 2096404014
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 55 (100)| |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
| 2 | NESTED LOOPS | | 85 | 1955 | 55 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| MY_TABLE | 85 | 1615 | 55 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| MY_OTHER_OTHER_PK | 1 | 4 | 0 (0)| |
-----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$A43D1678")
ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
OUTLINE(@"SEL$1")
FULL(@"SEL$A43D1678" "C"@"SEL$1")
INDEX(@"SEL$A43D1678" "G"@"SEL$1" ("MY_OTHER_OTHER_TABLE"."MY_OTHER_OTHER_ID"))
LEADING(@"SEL$A43D1678" "C"@"SEL$1" "G"@"SEL$1")
USE_NL(@"SEL$A43D1678" "G"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("C"."MY_NUM"<=215 AND "C"."MY_NUM">=200))
4 - access("G"."MY_OTHER_OTHER_ID"="C"."MY_OTHER_OTHER_ID")
44 rows selected.
The highlighted text in the outline proves only one join was eliminated, even though the data would allow the optimizer to eliminate both joins. The table order makes the difference; with this order the join between my_table and my_other_table can be eliminated but the join between that row source and my_other_other_table cannot be. Changing the order of the FROM clause to:
my_table,
my_other_table,
my_other_other_table
provides the optimizer with the ‘correct’ order to eliminate both joins:
BING @ orclpdb1 >
BING @ orclpdb1 > select
2 count(c.my_varch)
3 from
4 my_table c,
5 my_other_table p,
6 my_other_other_table g
7 where
8 c.my_num between 200 and 215
9 and p.my_other_id = c.my_other_id
10 and p.my_other_other_id = c.my_other_other_id
11 and g.my_other_other_id = p.my_other_other_id
12 ;
COUNT(C.MY_VARCH)
-----------------
80
BING @ orclpdb1 >
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID dur2a6u4k0dw7, child number 0
-------------------------------------
select count(c.my_varch) from my_table c,
my_other_table p, my_other_other_table g where c.my_num
between 200 and 215 and p.my_other_id = c.my_other_id and
p.my_other_other_id = c.my_other_other_id and g.my_other_other_id =
p.my_other_other_id
Plan hash value: 3996063390
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 55 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | TABLE ACCESS FULL| MY_TABLE | 85 | 1275 | 55 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$EE3A0715")
ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
ELIMINATE_JOIN(@"SEL$1" "G"@"SEL$1")
OUTLINE(@"SEL$1")
FULL(@"SEL$EE3A0715" "C"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("C"."MY_NUM"<=215 AND "C"."MY_NUM">=200))
40 rows selected.
With this order the optimizer can eliminate both joins it joine my_other_other_table to my_other_table, reducing that join to a single-table query against my_other_table, and that allows for the second join to be eliminated (as in the first query) leaving only my_table as the row source.
Since Oracle allows for using either the ‘native’ Oracle syntax or the ANSI syntax the example can be rewritten to conform to the ANSI syntax. Using the table order from the first Oracle-syntax query the optimizer has no problem eliminating both joins:
BING @ orclpdb1 >
BING @ orclpdb1 > select
2 count(c.my_varch)
3 from
4 my_other_other_table g
5 join
6 my_other_table p
7 on p.my_other_other_id = g.my_other_other_id
8 join
9 my_table c
10 on c.my_other_id = p.my_other_id
11 and c.my_other_other_id = p.my_other_other_id
12 where
13 c.my_num between 200 and 215
14 ;
COUNT(C.MY_VARCH)
-----------------
80
BING @ orclpdb1 >
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1avs3dy4zyfyf, child number 0
-------------------------------------
select count(c.my_varch) from my_other_other_table g join
my_other_table p on p.my_other_other_id = g.my_other_other_id
join my_table c on c.my_other_id = p.my_other_id and
c.my_other_other_id = p.my_other_other_id where c.my_num between
200 and 215
Plan hash value: 3996063390
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 55 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | TABLE ACCESS FULL| MY_TABLE | 85 | 1275 | 55 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$8E5E10D6")
ELIMINATE_JOIN(@"SEL$9DC5130D" "P"@"SEL$1")
OUTLINE(@"SEL$9DC5130D")
MERGE(@"SEL$E7C364C4" >"SEL$3")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$E7C364C4")
MERGE(@"SEL$1C2B93D4" >"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1C2B93D4")
ELIMINATE_JOIN(@"SEL$1" "G"@"SEL$1")
OUTLINE(@"SEL$1")
FULL(@"SEL$8E5E10D6" "C"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("C"."MY_NUM"<=215 AND "C"."MY_NUM">=200))
47 rows selected.
In yet another interesting turn of events if the join order is changed to that of the “successful” query writtem using Oracle syntax only the join involving my_other_table is eliminated:
BING @ orclpdb1 >
BING @ orclpdb1 > select
2 count(c.my_varch)
3 from
4 my_table c
5 join
6 my_other_table p
7 on p.my_other_id = c.my_other_id
8 and p.my_other_other_id = c.my_other_other_id
9 join
10 my_other_other_table g
11 on g.my_other_other_id = p.my_other_other_id
12 where
13 c.my_num between 200 and 215
14 ;
COUNT(C.MY_VARCH)
-----------------
80
BING @ orclpdb1 >
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3jqqzwwsy9t6r, child number 0
-------------------------------------
select count(c.my_varch) from my_table c join
my_other_table p on p.my_other_id = c.my_other_id and
p.my_other_other_id = c.my_other_other_id join my_other_other_table
g on g.my_other_other_id = p.my_other_other_id where c.my_num
between 200 and 215
Plan hash value: 2096404014
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 55 (100)| |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
| 2 | NESTED LOOPS | | 85 | 1955 | 55 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| MY_TABLE | 85 | 1615 | 55 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| MY_OTHER_OTHER_PK | 1 | 4 | 0 (0)| |
-----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$935DEA34")
MERGE(@"SEL$AAEC2A2A" >"SEL$3")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$AAEC2A2A")
MERGE(@"SEL$A43D1678" >"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$A43D1678")
ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
OUTLINE(@"SEL$1")
FULL(@"SEL$935DEA34" "C"@"SEL$1")
INDEX(@"SEL$935DEA34" "G"@"SEL$2" ("MY_OTHER_OTHER_TABLE"."MY_OTHER_OTHER_ID"))
LEADING(@"SEL$935DEA34" "C"@"SEL$1" "G"@"SEL$2")
USE_NL(@"SEL$935DEA34" "G"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("C"."MY_NUM"<=215 AND "C"."MY_NUM">=200))
4 - access("G"."MY_OTHER_OTHER_ID"="C"."MY_OTHER_OTHER_ID")
51 rows selected.
It is worth noting that when Oracle processes an ANSI syntax join it first converts it to an equivalent Oracle-syntax join then proceeeds with the optimization/join elimination. With the first ANSI example the re-write essentially produces a query of the form:
select [] from (select [] from my_other_other_table join my_other_table) join my_table
This allows for elimination of the my_other_other_table join, leaving the my_other_table, my_table join which, given the data set, can also be eliminated leaving a simple query againt my_table. The re-write of tne first ANSI join produces basically the same query as the second Oracle-syntax query in the example. With the other ANSI query the resulting re-write is of the form:
select [] from (select [] from my_table join my_other_table) join my_other_other_table
That essentially matches the first Oracle-syntax query in the example which provides the optimizer with only enough information to eliminate the first join.
It is a good idea to check execution plans for queries utilizing multiple joins on primary-key columns; it’s possible that simply changing the order of the tables could change the execution plan, as illustrated here. It’s also worth noting that ANSI-syntax queries can behave (as far as the optimizer is concerned) in opposite ways because of the way Oracle re-writes them.
