Short-Circuiting the COST

The Oracle Optimizer can be smart when evaluating predicates in a query.

If it finds a predicate that causes a "short-circuit" --- one that prevents rows from being returned by the query --- it can evaluate the COST of the query to 0 (zero).  Normally, we would never expect a COST to zero ---  even where we know zero rows will be returned by the query, Oracle may have to undertake some I/O  (a Full Table Scan or an Index Range Scan in the simplest cases) and some CPU cycles to verify the resulting blocks for the expected result.

Here I show a simple example of a short-circuit.

First, I build a Table and an Index, with statistics :

SQL> create table my_table
  2  as select * from dba_objects;

Table created.

SQL> select count(*) from my_table;

  COUNT(*)
----------
     76609

SQL> create index my_table_ndx on my_table(owner);

Index created.

SQL> exec dbms_stats.gather_table_stats('','MY_TABLE',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> 
SQL> select num_rows, sample_size
  2  from user_tab_statistics
  3  where table_name = 'MY_TABLE';

  NUM_ROWS SAMPLE_SIZE
---------- -----------
     76609       76609

SQL> select num_distinct
  2  from user_tab_col_statistics
  3  where table_name = 'MY_TABLE'
  4  and column_name = 'OWNER';

NUM_DISTINCT
------------
          44

SQL> select leaf_blocks, distinct_keys, num_rows, sample_size
  2  from user_ind_statistics
  3  where table_name = 'MY_TABLE'
  4  and index_name = 'MY_TABLE_NDX';

LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS SAMPLE_SIZE
----------- ------------- ---------- -----------
        183            44      76609       76609

SQL> 

So we know that all the statistics (Row Count in the table and number of Distinct values in the Index) are consistent. I then run a simple query :

SQL> col object_name format a30
SQL> col object_type format a18
SQL> set autotrace on
SQL> select object_name, object_type
  2  from my_table
  3  where owner = 'HEMANT'
  4  and object_name like 'S%'
  5  order by 1,2;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
SOURCE_PK                      INDEX
SOURCE_PK1                     INDEX
SOURCE_TABLE                   TABLE


Execution Plan
----------------------------------------------------------
Plan hash value: 1587485563

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    24 |   984 |    53   (2)| 00:00:01 |
|   1 |  SORT ORDER BY               |              |    24 |   984 |    53   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| MY_TABLE     |    24 |   984 |    52   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | MY_TABLE_NDX |  1741 |       |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_NAME" LIKE 'S%')
   3 - access("OWNER"='HEMANT')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        579  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> 

Oracle has estimated that it will fetch 1741 rowids from the index in the plan step id=3 and then fetch 1,741 rows and also apply a filter for object_name in plan step id=2 to reduce the row count to 24.
The actual resulting row count is 3.  (The 24 row count is an estimate)
The estimated cost of the Index Range Scan is 5, the estimated cost of the Table Access (1741 rowids) and Filter is 47 (52-5) and the estimated cost of the Sort (for 24 rows) is 1 (53-52), resulting in a total cost of 53.
Oracle read a total of 5 blocks (consistent gets).

What happens if I add an "AND 1=2" predicate (which is always FALSE) ?

SQL> select object_name, object_type
  2  from my_table
  3  where owner = 'HEMANT'
  4  and object_name like 'S%'
  5  and 1=2
  6  order by 1,2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 422461895

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |    41 |     0   (0)|          |
|   1 |  SORT ORDER BY                |              |     1 |    41 |            |          |
|*  2 |   FILTER                      |              |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| MY_TABLE     |    24 |   984 |    52   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | MY_TABLE_NDX |  1741 |       |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - filter(NULL IS NOT NULL)
   3 - filter("OBJECT_NAME" LIKE 'S%')
   4 - access("OWNER"='HEMANT')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        356  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 

The query returns no rows (i.e. zero rows).
But notice the "filter (NULL IS NOT NULL)".  This is the short-circuit that Oracle adds.  This will always evaluate to FALSE.  This is Oracle's way of translating the "1=2" condition that I added to the query.
Although there is a supposed COST of 5 for the 1,741 rowids from the Index Range Scan and a supposed COST of 47 (52-5) for the Table Access of the 1,741 rowids and filtering to 24 rows for "OBJECT_NAME LIKE 'S%'), Oracle then proceeds to add the NULL IS NOT NULL FILTER before the SORT ORDER BY.  This converts the COST to 0.
We can see that Oracle*did not do any I/O* because the Statistics show 0 block gets (consistent gets).
Let me say that again : Oracle did NOT do any I/O inspite of the supposed Index Range Scan and Table Access by Index Rowid.

.
.
.