Search notes:

Oracle Plan Operation FILTER for Predicate 1 = 2

Create the demonstration table …

create table tq84_tab (
   nm number,
   tx varchar2(10)
);

… and fill it with some dummy data:

insert into tq84_tab
select
   level,
   to_char(level, 'fmXXXX')
from
   dual connect by level < 1000;

It cannot hurt to gather some statistics:

begin
   dbms_stats.gather_table_stats(user, 'tq84_tab');
end;
/

Explain the plan for a query that does not return a record because of the where 1 = 2 predicate`:

explain plan for
select
   max(tx)
from
   tq84_tab
where
   1 = 2;

select * from table(dbms_xplan.display(format=>'basic rows predicate'));
-- 
-- ------------------------------------------------
-- | Id  | Operation           | Name     | Rows  |
-- ------------------------------------------------
-- |   0 | SELECT STATEMENT    |          |     1 |
-- |   1 |  SORT AGGREGATE     |          |     1 |
-- |*  2 |   FILTER            |          |       |
-- |   3 |    TABLE ACCESS FULL FULL| TQ84_TAB |   999 |
-- ------------------------------------------------
--  
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    2 - filter(NULL IS NOT NULL)

Change the predicate so that all records are returned. Note how this removes the FILTER operation from the execution plan:

explain plan for
select
   max(tx)
from
   tq84_tab
where
   1 = 1;

select * from table(dbms_xplan.display(format=>'basic rows predicate'));
--
-- -----------------------------------------------
-- | Id  | Operation          | Name     | Rows  |
-- -----------------------------------------------
-- |   0 | SELECT STATEMENT   |          |     1 |
-- |   1 |  SORT AGGREGATE    |          |     1 |
-- |   2 |   TABLE ACCESS FULL FULL| TQ84_TAB |   999 |
-- -----------------------------------------------

When the statement is executed (rather than explained), it shows that the FILTER operation does not run its child row source (Starts is 0):

set serveroutput off
select /*+ gather_plan_statistics */
   max(tx)
from
   tq84_tab
where
   1 = 2;

select * from table(dbms_xplan.display_cursor(format=>'adaptive rowstats -predicate'));
--
-- -------------------------------------------------------------------
-- | Id  | Operation           | Name     | Starts | E-Rows | A-Rows |
-- -------------------------------------------------------------------
-- |   0 | SELECT STATEMENT    |          |      1 |        |      1 |
-- |   1 |  SORT AGGREGATE     |          |      1 |      1 |      1 |
-- |   2 |   FILTER            |          |      1 |        |      0 |
-- |   3 |    TABLE ACCESS FULL FULL| TQ84_TAB |      0 |    999 |      0 |
-- -------------------------------------------------------------------

Cleaning up:

drop table tq84_tab;

Index