"All" Operator in Oracle Database

Envronment Setup

create table t (id integer, order_date date, 
                constraint t_pk primary key(id));
insert into t 
select rownum, rownum+sysdate from dual connect by level <= 1000000;
commit;
exec dbms_stats.gather_table_stats('ADMIN','T');

Execution Time and Plan for “ALL” operator

SQL> select * from t where order_date > ALL (select order_date from t where id=999995);

        ID    ORDER_DATE 
__________ _____________ 
    999996 07/09/59      
    999997 08/09/59      
    999998 09/09/59      
    999999 10/09/59      
   1000000 11/09/59      

Elapsed: 00:00:00.613

SQL> explain plan for select * from t where order_date > ALL (select order_date from t where id=999995);
Explained.

SQL> select * from table(dbms_xplan.display());

                                                                                PLAN_TABLE_OUTPUT 
_________________________________________________________________________________________________ 
Plan hash value: 86575764                                                                         
                                                                                                  
----------------------------------------------------------------------------------------------    
| Id  | Operation                     | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    
----------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT              |      | 10000 |   253K|       |  5443   (1)| 00:00:01 |    
|   1 |  MERGE JOIN ANTI NA           |      | 10000 |   253K|       |  5443   (1)| 00:00:01 |    
|   2 |   SORT JOIN                   |      |  1000K|    12M|    45M|  5439   (1)| 00:00:01 |    
|   3 |    TABLE ACCESS FULL          | T    |  1000K|    12M|       |   688   (1)| 00:00:01 |    
|*  4 |   SORT UNIQUE                 |      |     1 |    13 |       |     4  (25)| 00:00:01 |    
|   5 |    TABLE ACCESS BY INDEX ROWID| T    |     1 |    13 |       |     3   (0)| 00:00:01 |    
|*  6 |     INDEX UNIQUE SCAN         | T_PK |     1 |       |       |     2   (0)| 00:00:01 |    
----------------------------------------------------------------------------------------------    
 
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
                                                                                                  
   4 - access("ORDER_DATE"<="ORDER_DATE")                                                         
       filter("ORDER_DATE"<="ORDER_DATE")                                                         
   6 - access("ID"=999995)                                                                        

20 rows selected. 

Execution Time and Plan for MAX+Subquery

SQL> select * from t where order_date > (select max(order_date) from t where id=999995);

        ID    ORDER_DATE 
__________ _____________ 
    999996 07/09/59      
    999997 08/09/59      
    999998 09/09/59      
    999999 10/09/59      
   1000000 11/09/59      

Elapsed: 00:00:00.286

SQL> explain plan for select * from t where order_date > (select max(order_date) from t where id=999995);

Explained.

SQL> select * from table(dbms_xplan.display());

                                                                        PLAN_TABLE_OUTPUT 
_________________________________________________________________________________________ 
Plan hash value: 3070503375                                                               
                                                                                          
--------------------------------------------------------------------------------------    
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
--------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT              |      | 50000 |   634K|   692   (2)| 00:00:01 |    
|*  1 |  TABLE ACCESS FULL            | T    | 50000 |   634K|   689   (2)| 00:00:01 |    
|   2 |   SORT AGGREGATE              |      |     1 |    13 |            |          |    
|   3 |    TABLE ACCESS BY INDEX ROWID| T    |     1 |    13 |     3   (0)| 00:00:01 |    
|*  4 |     INDEX UNIQUE SCAN         | T_PK |     1 |       |     2   (0)| 00:00:01 |    
--------------------------------------------------------------------------------------    
                                                                                          
Predicate Information (identified by operation id):                                       
---------------------------------------------------                                       
                                                                                          
   1 - filter("ORDER_DATE"> (SELECT MAX("ORDER_DATE") FROM "T" "T" WHERE                  
              "ID"=999995))                                                               
   4 - access("ID"=999995)                                                                

18 rows selected. 

The behaviour differences between ALL Operator and MAX+Subquery (ALL operator evaluates “NULL” as TRUE)

SQL> select order_date from t where id=1000001;

no rows selected

SQL> select count(*) from t where order_date > ALL (select order_date from t where id=1000001);

   COUNT(*) 
___________ 
    1000000 

SQL> select * from t where order_date > (select max(order_date) from t where id=1000001);

no rows selected