Subquery Elimination in 12cR2

We can see many crazy queries generated by ORM or BI query generators. They are built to be easily generated rather than being optimal. Then the optimizer has to implement more and more transformations to get an efficient execution plan.

Here is one new that appeared in Oracle 12cR2 database: Subquery elimination when the subquery don’t filter any rows.

This is the behavior observed in 12.1 database (12.1.0.2) – when the subquery do not filter any rows because it reads the same table as the outer one, without any predicates.

training@ORA12C> set autotrace traceonly explain

training@ORA12C> select * from emp where ename in (select ename from emp);

 

Execution Plan

----------------------------------------------------------

Plan hash value: 977554918

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    14 |   616 |     6   (0)| 00:00:01 |

|*  1 |  HASH JOIN SEMI    |      |    14 |   616 |     6   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| EMP  |    14 |    84 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("ENAME"="ENAME")

 

 

Hash join semi is a join where we don’t need to match with all rows, but only one. We write it with an EXISTS/IN subquery.

 

We read the same table twice, join all rows and finally return results as exactly the same rows as those coming from the first full scan. Which is not efficient.

 

Where as in 12.2 (12.2.0.1) database, we observe this.

 

demo@ORA12C> set autotrace traceonly explain

demo@ORA12C> select * from emp where ename in (select ename from emp);

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ENAME" IS NOT NULL)

 

 

The table is scanned only once, because the optimizer knows that the subquery don’t filter anything.

 

Finally here is what you can see in the 10053 Trace.

 

CSE: Considering common sub-expression elimination in query block SEL$D0DB0F9A (#1)

*************************

Common Subexpression elimination (CSE)

*************************

CSE:     CSE not performed on query block SEL$D0DB0F9A (#1).

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "EMP"."EMPNO" "EMPNO",

  "EMP"."ENAME" "ENAME",

  "EMP"."JOB" "JOB",

  "EMP"."MGR" "MGR",

  "EMP"."HIREDATE" "HIREDATE",

  "EMP"."SAL" "SAL",

  "EMP"."COMM" "COMM",

  "EMP"."DEPTNO" "DEPTNO"

FROM "RAJESH"."EMP" "EMP"

WHERE 0            =0

AND "EMP"."ENAME" IS NOT NULL

kkoqbc: optimizing query block SEL$D0DB0F9A (#1)

 

 

This example is simple and it is obvious that the SQL should be rewritten, but with the large generated queries, on complex views, this is the kind of the thing that can be seen in the resulting query and this transformation will help to avoid unnecessary work.