Improvements in Oracle’s Join Elimination

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.

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles