Oracle Tips and Tricks — David Fitzjarrell

March 10, 2019

Final Elimination

Filed under: General — dfitzjarrell @ 08:07

"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

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 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 and that can depend on table order in the FROM clause or on the join syntax in use. Let’s look at an example that illustrates these points.

This 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.] Using the traditional Oracle syntax for the joins we find that 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"=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"=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"=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"=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

which 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’s 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.

Which should be expected.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

%d bloggers like this: