Oracle SQL Optimization Tricks: Table Elimination and OR-Expansion Explained

Oracle optimizer's job is to find the most efficient way to execute SQL statements and provide the requested data to the client.

We all know it is a complex code and gets improved as part of every product release.

Today Oracle Optimizer is very evolved and has very rich useful features in-built in it (adaptive plans, dynamic statistics etc)

Below is a basic optimization technique ,which may be there from 8i/9i, may be not relevant today  , but still posting as sometimes below things may come up in some execution plan which you are checking.

Table discard/elimination
=========================

The query involves 2 tables - students and teachers , with student table having a FK relationship with the teachers table.

CREATE TABLE STUDENTS
(
  STUDENT_ID  NUMBER(10)                        NOT NULL,
  NAME        VARCHAR2(50 BYTE)                 NOT NULL,
  TEACHER_ID  NUMBER(10)
)

ALTER TABLE STUDENTS ADD (
  FOREIGN KEY (TEACHER_ID) 
  REFERENCES TEACHERS (TEACHER_ID)
  ENABLE VALIDATE);  
  
  CREATE TABLE TEACHERS
(
  TEACHER_ID    NUMBER(10),
  TEACHER_NAME  VARCHAR2(50 BYTE)               NOT NULL,
  COURSES       VARCHAR2(50 BYTE)
)

ALTER TABLE TEACHERS ADD (
  CONSTRAINT TEACHER_PK
  PRIMARY KEY
  (TEACHER_ID)
  USING INDEX TEACHER_PK
  ENABLE VALIDATE);


The query below references both these table in the SQL , however oracle optimizer ignores the teachers table all-together

select * from students where TEACHER_ID in (select TEACHER_ID from teachers);

--------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     7 |    77 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| STUDENTS |     7 |    77 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage("TEACHER_ID" IS NOT NULL)
       filter("TEACHER_ID" IS NOT NULL)

This is feature where the oracle optimizer understands (due to the FK constraint defined)  that the TEACHERS table can be ignored, as the value in teacher_id column in STUDENTS table will always exist at least once in the TEACHERS table.

Evaluating the condition by actually accessing the TEACHERS table and fetching values will not have any impact on the result set of the query

We can also see the same behaviour for query using join(For optimizer this query is semantically same as the earlier query)
    

SQL> select s.student_id,s.name,t.teacher_id from students s, teachers t
    where s.teacher_id=t.teacher_id;

--------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     7 |    77 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| STUDENTS |     7 |    77 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage("S"."TEACHER_ID" IS NOT NULL)
       filter("S"."TEACHER_ID" IS NOT NULL)
	   
	   
OR-expansion
=============

Optimizer rewrites the query , if any two un-related conditions are there and they can be written as union all to utilize better access paths for each component(e.g indexes)

The user executes the below query in the database

select * from teachers where teacher_id=1001 or courses='subject 4'

Upon checking the the execution plan we can see that the optimizer transformed the query as below

where teacher_id=1001 or courses='subject 4'

to 

where teacher_id=1001 
union all 
where courseS='subject 4'
and teacher_id!=1001

----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |     2 |   134 |     3   (0)| 00:00:01 |
|   1 |  VIEW                                 | VW_ORE_C5CDDEC5  |     2 |   134 |     3   (0)| 00:00:01 |
|   2 |   UNION-ALL                           |                  |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID        | TEACHERS         |     1 |    25 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN                 | TEACHER_PK       |     1 |       |     0   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEACHERS         |     1 |    25 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | TEACHERS_COURSES |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("TEACHER_ID"=1001)
   5 - filter(LNNVL("TEACHER_ID"=1001))
   6 - access("COURSES"='subject 4')