Execution Plan Operations and Options

The row source tree is the core of the execution plan. It contains the sequence of operations that the database performs to run the statement; each operation may have options associated with it. The operation in execution plan is also known as row source operator.  If you look at the below example, TABLE ACCESS operation is performed on both plans, and the former has FULL and latter has SAMPLE. Each option will affect the way operation works – FULL retrieves all the rows from a table and SAMPLE retrieves sampled rows from a table.


SELECT * FROM t;

Plan hash value: 2153619298

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T    |
----------------------------------

SELECT * FROM t SAMPLE (5);

Plan hash value: 3749081399

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|   1 |  TABLE ACCESS SAMPLE| T    |
------------------------------------

Starting from 11g R1, it is possible to list all documented operations by querying X$XPLTON (Explain Plan Table Operation Names).


SQL> SELECT xplton_id operation_id,
  2    xplton_name operation_name
  3  FROM x$xplton;

OPERATION_ID OPERATION_NAME                                                    
------------ ----------------------------------------                          
           0                                                                   
           1 BITMAP KEY ITERATION                                              
           2 NESTED LOOPS                                                      
           3 MERGE JOIN                                                        
           4 HASH JOIN                                                         
           5 AND-EQUAL                                                         
           6 BITMAP AND                                                        
           7 INTERSECTION                                                      
           8 MINUS                                                             
           9 BITMAP MINUS                                                      
          10 GENERATE                                                          
          11 BITMAP CONVERSION                                                 
          12 BITMAP INDEX                                                      
          13 CONNECT BY                                                        
          14 CONNECT BY PUMP                                                   
          15 COUNT                                                             
          16 DELETE                                                            
          17 UPDATE                                                            
          18 FIRST ROW                                                         
          19 FILTER                                                            
          20 FOR UPDATE                                                        
          21 FIXED TABLE                                                       
          22 INLIST ITERATOR                                                   
          23 INDEX                                                             
          24 INDEX BUILD                                                       
          25 LOAD AS SELECT                                                    
          26 MULTI-TABLE INSERT                                                
          27 INTO                                                              
          28 DIRECT LOAD INTO                                                  
          29 COLLECTION ITERATOR                                               
          30 DOMAIN INDEX                                                      
          31 PARTITION RANGE                                                   
          32 PARTITION HASH                                                    
          33 PARTITION LIST                                                    
          34 PARTITION SYSTEM                                                  
          35 PARTITION COMBINED                                                
          36 GRANULE ITERATOR                                                  
          37 SEQUENCE                                                          
          38 TABLE ACCESS                                                      
          39 TABLE QUEUE                                                       
          40 INDEX MAINTENANCE                                                 
          41 FIFO BUFFER                                                       
          42 WINDOW                                                            
          43 TEMP TABLE TRANSFORMATION                                         
          44 SORT                                                              
          45 BUFFER                                                            
          46 BITMAP COMPACTION                                                 
          47 BITMAP CONSTRUCTION                                               
          48 BITMAP OR                                                         
          49 BITMAP MERGE                                                      
          50 CONCATENATION                                                     
          51 UNION-ALL                                                         
          52 VIEW PUSHED PREDICATE                                             
          53 VIEW                                                              
          54 REMOTE                                                            
          55 SELECT STATEMENT                                                  
          56 INSERT STATEMENT                                                  
          57 UPDATE STATEMENT                                                  
          58 MERGE STATEMENT                                                   
          59 DELETE STATEMENT                                                  
          60 CREATE TABLE STATEMENT                                            
          61 ALTER INDEX STATEMENT                                             
          62 CREATE INDEX STATEMENT                                            
          63 DDL STATEMENT                                                     
          64 Can't Explain!!                                                   
          65 TEMP TABLE GENERATION                                             
          66 TRUNCATE TEMP TABLE                                               
          67 RECURSIVE EXECUTION                                               
          68 MERGE                                                             
          69 EXTERNAL TABLE ACCESS                                             
          70 BITMAP JOIN INDEX UPDATE                                          
          71 BITMAP JOIN INDEX UPDATE STATEMENT                                
          72 VIEW HIERARCHY                                                    
          73 SQL MODEL                                                         
          74 MAT_VIEW ACCESS                                                   
          75 MAT_VIEW REWRITE ACCESS                                           
          76 FREQUENT ITEMSET COUNTING                                         
          77 FIC ENUMERATE FEED                                                
          78 FIC LOAD BITMAPS                                                  
          79 FIC LOAD ITEMSETS                                                 
          80 FIC DETECT END                                                    
          81 FIC RECURSIVE ITERATION                                           
          82 DOMAIN INDEX BUILD                                                
          83 DOMAIN INDEX PARTITION BUILD                                      
          84 FAST DUAL                                                         
          85 REFERENCE MODEL                                                   
          86 PX RECEIVE                                                        
          87 QC SEND                                                           
          88 PX SEND                                                           
          89 PX COORDINATOR                                                    
          90 PX BLOCK                                                          
          91 PX PARTITION RANGE                                                
          92 PX PARTITION HASH                                                 
          93 PX PARTITION LIST                                                 
          94 WINDOW (IN SQL MODEL)                                             
          95 UNION ALL PUSHED PREDICATE                                        
          96 DST ENUMERATE FEED                                                
          97 DST DETECT END                                                    
          98 DST RECURSIVE ITERATION                                           
          99 DST SPLIT                                                         
         100 DECISION TREE CLASSIFICATION                                      
         101 DST PRUNE                                                         
         102 JOIN FILTER                                                       
         103 HASH                                                              
         104 ERROR LOGGING                                                     
         105 LOAD TABLE CONVENTIONAL                                           
         106 PART JOIN FILTER                                                  
         107 AW SCAN                                                           
         108 PARTITION REFERENCE                                               
         109 PX PARTITION REFERENCE                                            
         110 XPATH EVALUATION                                                  
         111 RESULT CACHE                                                      
         112 DM FEED PREDICTOR-TARGET PAIRS                                    
         113 DM SUPERVISED BINNING                                             
         114 DM HASH-BROADCAST COUNTS                                          
         115 BULK BINDS GET                                                    
         116 APPROXIMATE NDV                                                   
         117 UNPIVOT                                                           
         118 TRANSPOSE                                                         
         119 MONITORING                                                        
         120 CUBE SCAN                                                         
         121 JOINED CUBE SCAN                                                  
         122 CUBE ACCESS                                                       
         123 MAT_VIEW CUBE ACCESS                                              
         124 MAT_VIEW REWRITE CUBE ACCESS                                      

125 rows selected.

options can be listed by querying X$XPLTOO (Explain Plan Table Operation Options).


SQL> SELECT xpltoo_id operation_options_id,
  2    xpltoo_name operation_options_name
  3  FROM x$xpltoo;

OPERATION_OPTIONS_ID OPERATION_OPTIONS_NAME                                    
-------------------- ----------------------------------------                  
                   0                                                           
                   1 OUTER                                                     
                   2 ANTI                                                      
                   3 SEMI                                                      
                   4 CARTESIAN                                                 
                   5 TO ROWIDS                                                 
                   6 FROM ROWIDS                                               
                   7 COUNT                                                     
                   8 SINGLE VALUE                                              
                   9 SAMPLE FAST FULL SCAN                                     
                  10 FAST FULL SCAN                                            
                  11 FULL SCAN                                                 
                  12 RANGE SCAN                                                
                  13 UNIQUE SCAN                                               
                  14 SKIP SCAN                                                 
                  15 FULL SCAN (MIN/MAX)                                       
                  16 RANGE SCAN (MIN/MAX)                                      
                  17 FULL SCAN DESCENDING                                      
                  18 RANGE SCAN DESCENDING                                     
                  19 SKIP SCAN DESCENDING                                      
                  20 WITH FILTERING                                            
                  21 WITHOUT FILTERING                                         
                  22 STOPKEY                                                   
                  23 FIXED INDEX                                               
                  24 FULL                                                      
                  25 HASH                                                      
                  26 INDEX BUILD                                               
                  27 UNIQUE (LOCAL)                                            
                  28 NON UNIQUE (LOCAL)                                        
                  29 UNIQUE                                                    
                  30 NON UNIQUE                                                
                  31 PICKLER FETCH                                             
                  32 CONSTRUCTOR FETCH                                         
                  33 SUBQUERY FETCH                                            
                  34 REMOTE                                                    
                  35 SAMPLE                                                    
                  36 SAMPLE BY ROWID RANGE                                     
                  37 BY ROWID RANGE                                            
                  38 BY LOCAL INDEX ROWID                                      
                  39 BY GLOBAL INDEX ROWID                                     
                  40 BY INDEX ROWID                                            
                  41 BY USER ROWID                                             
                  42 CLUSTER                                                   
                  43 NOSORT                                                    
                  44 BUFFER PUSHED RANK                                        
                  45 BUFFER                                                    
                  46 CHILD PUSHED RANK                                         
                  47 CHILD                                                     
                  48 SORT PUSHED RANK                                          
                  49 SINGLE                                                    
                  50 EMPTY                                                     
                  51 INLIST                                                    
                  52 ALL                                                       
                  53 ITERATOR                                                  
                  54 PARTITION                                                 
                  55 AGGREGATE                                                 
                  56 GROUP BY STOPKEY                                          
                  57 GROUP BY                                                  
                  58 UNIQUE NOSORT                                             
                  59 GROUP BY NOSORT                                           
                  60 GROUP BY ROLLUP                                           
                  61 CUBE                                                      
                  62 ROLLUP                                                    
                  63 SORT                                                      
                  64 CREATE INDEX                                              
                  65 UNIQUE STOPKEY                                            
                  66 ORDER BY STOPKEY                                          
                  67 ORDER BY                                                  
                  68 JOIN                                                      
                  69 GROUP BY NOSORT ROLLUP                                    
                  70 CUSTOM OPTION                                             
                  71 ROWID RANGE                                               
                  72 EXTERNAL CHUNK                                            
                  73 ACYCLIC                                                   
                  74 CYCLIC                                                    
                  75 ACYCLIC FAST                                              
                  76 ORDERED                                                   
                  77 ORDERED FAST                                              
                  78 RIGHT OUTER                                               
                  79 RIGHT ANTI                                                
                  80 RIGHT SEMI                                                
                  81 PARTITION OUTER                                           
                  82 (LOCAL)                                                   
                  83 PARTITION (ROWID)                                         
                  84 PARTITION (KEY)                                           
                  85 RANGE                                                     
                  86 ROUND-ROBIN                                               
                  87 BROADCAST                                                 
                  88 QC (ORDER)                                                
                  89 QC (RANDOM)                                               
                  90 BROADCAST LOCAL                                           
                  91 HASH LOCAL                                                
                  92 HYBRID (ROWID PKEY)                                       
                  93 RANDOM LOCAL                                              
                  94 HASH (BLOCK ADDRESS)                                      
                  95 MIXED HASH BCAST LOCAL                                    
                  96 MIXED HASH BCAST                                          
                  97 OUTER BUFFERED                                            
                  98 RIGHT OUTER BUFFERED                                      
                  99 ANTI BUFFERED                                             
                 100 RIGHT ANTI BUFFERED                                       
                 101 SEMI BUFFERED                                             
                 102 RIGHT SEMI BUFFERED                                       
                 103 CARTESIAN BUFFERED                                        
                 104 PARTITION OUTER BUFFERED                                  
                 105 HASH ALL                                                  
                 106 RANGE ALL                                                 
                 107 LIST                                                      
                 108 LIST ALL                                                  
                 109 SYSTEM                                                    
                 110 FORCED SERIAL                                             
                 111 PARTITION JOIN                                            
                 112 AW HASH                                                   
                 113 CREATE                                                    
                 114 USE                                                       
                 115 SUBQUERY                                                  
                 116 OR                                                        
                 117 MULTI-COLUMN                                              
                 118 NOSORT STOPKEY                                            
                 119 BUFFERED                                                  
                 120 FULL OUTER                                                
                 121 FULL OUTER BUFFERED                                       
                 122 NO FILTERING WITH START-WITH                              
                 123 JOIN-FILTER                                               
                 124 ANTI NA                                                   
                 125 ANTI SNA                                                  
                 126 ANTI NA BUFFERED                                          
                 127 ANTI SNA BUFFERED                                         
                 128 RIGHT ANTI NA                                             
                 129 RIGHT ANTI SNA                                            
                 130 RIGHT ANTI NA BUFFERED                                    
                 131 RIGHT ANTI SNA BUFFERED                                   
                 132 GROUP BY PIVOT                                            
                 133 GROUP BY NOSORT PIVOT                                     
                 134 DUMP                                                      
                 135 VERIFY                                                    
                 136 STRIP                                                     
                 137 STORAGE FULL                                              
                 138 STORAGE SAMPLE                                            
                 139 STORAGE SAMPLE BY ROWID RANGE                             
                 140 STORAGE BY ROWID RANGE                                    
                 141 PARTIAL OUTER                                             
                 142 OPTIMIZED                                                 
                 143 SPECIFIED                                                 
                 144 STORAGE SAMPLE FAST FULL SCAN                             
                 145 STORAGE FAST FULL SCAN                                    
                 146 STORAGE FULL SCAN                                         
                 147 STORAGE RANGE SCAN                                        
                 148 STORAGE FULL SCAN (MIN/MAX)                               

149 rows selected.

Also, in 11g R2, the same information is available via Automatic Workload Repository (AWR) views, DBA_HIST_PLAN_OPERATION_NAME and DBA_HIST_PLAN_OPTION_NAME.


SQL> SELECT banner FROM v$version WHERE ROWNUM = 1;

BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production         

SQL> SELECT
  2    operation_id,
  3    operation_name
  4  FROM
  5    DBA_HIST_PLAN_OPERATION_NAME;

OPERATION_ID OPERATION_NAME                                                    
------------ ----------------------------------------------------------------  
           0                                                                   
           1 BITMAP KEY ITERATION                                              
           2 NESTED LOOPS                                                      
           3 MERGE JOIN                                                        
           4 HASH JOIN                                                         
           5 AND-EQUAL                                                         
           6 BITMAP AND                                                        
           7 INTERSECTION                                                      
           8 MINUS                                                             
           9 BITMAP MINUS                                                      
          10 GENERATE                                                          
          11 BITMAP CONVERSION                                                 
          12 BITMAP INDEX                                                      
          13 CONNECT BY                                                        
          14 CONNECT BY PUMP                                                   
          15 COUNT                                                             
          16 DELETE                                                            
          17 UPDATE                                                            
          18 FIRST ROW                                                         
          19 FILTER                                                            
          20 FOR UPDATE                                                        
          21 FIXED TABLE                                                       
          22 INLIST ITERATOR                                                   
          23 INDEX                                                             
          24 INDEX BUILD                                                       
          25 LOAD AS SELECT                                                    
          26 MULTI-TABLE INSERT                                                
          27 INTO                                                              
          28 DIRECT LOAD INTO                                                  
          29 COLLECTION ITERATOR                                               
          30 DOMAIN INDEX                                                      
          31 PARTITION RANGE                                                   
          32 PARTITION HASH                                                    
          33 PARTITION LIST                                                    
          34 PARTITION SYSTEM                                                  
          35 PARTITION COMBINED                                                
          36 GRANULE ITERATOR                                                  
          37 SEQUENCE                                                          
          38 TABLE ACCESS                                                      
          39 TABLE QUEUE                                                       
          40 INDEX MAINTENANCE                                                 
          41 FIFO BUFFER                                                       
          42 WINDOW                                                            
          43 TEMP TABLE TRANSFORMATION                                         
          44 SORT                                                              
          45 BUFFER                                                            
          46 BITMAP COMPACTION                                                 
          47 BITMAP CONSTRUCTION                                               
          48 BITMAP OR                                                         
          49 BITMAP MERGE                                                      
          50 CONCATENATION                                                     
          51 UNION-ALL                                                         
          52 VIEW PUSHED PREDICATE                                             
          53 VIEW                                                              
          54 REMOTE                                                            
          55 SELECT STATEMENT                                                  
          56 INSERT STATEMENT                                                  
          57 UPDATE STATEMENT                                                  
          58 MERGE STATEMENT                                                   
          59 DELETE STATEMENT                                                  
          60 CREATE TABLE STATEMENT                                            
          61 ALTER INDEX STATEMENT                                             
          62 CREATE INDEX STATEMENT                                            
          63 DDL STATEMENT                                                     
          64 Can't Explain!!                                                   
          65 TEMP TABLE GENERATION                                             
          66 TRUNCATE TEMP TABLE                                               
          67 RECURSIVE EXECUTION                                               
          68 MERGE                                                             
          69 EXTERNAL TABLE ACCESS                                             
          70 BITMAP JOIN INDEX UPDATE                                          
          71 BITMAP JOIN INDEX UPDATE STATEMENT                                
          72 VIEW HIERARCHY                                                    
          73 SQL MODEL                                                         
          74 MAT_VIEW ACCESS                                                   
          75 MAT_VIEW REWRITE ACCESS                                           
          76 FREQUENT ITEMSET COUNTING                                         
          77 FIC ENUMERATE FEED                                                
          78 FIC LOAD BITMAPS                                                  
          79 FIC LOAD ITEMSETS                                                 
          80 FIC DETECT END                                                    
          81 FIC RECURSIVE ITERATION                                           
          82 DOMAIN INDEX BUILD                                                
          83 DOMAIN INDEX PARTITION BUILD                                      
          84 FAST DUAL                                                         
          85 REFERENCE MODEL                                                   
          86 PX RECEIVE                                                        
          87 QC SEND                                                           
          88 PX SEND                                                           
          89 PX COORDINATOR                                                    
          90 PX BLOCK                                                          
          91 PX PARTITION RANGE                                                
          92 PX PARTITION HASH                                                 
          93 PX PARTITION LIST                                                 
          94 WINDOW (IN SQL MODEL)                                             
          95 UNION ALL PUSHED PREDICATE                                        
          96 DST ENUMERATE FEED                                                
          97 DST DETECT END                                                    
          98 DST RECURSIVE ITERATION                                           
          99 DST SPLIT                                                         
         100 DECISION TREE CLASSIFICATION                                      
         101 DST PRUNE                                                         
         102 JOIN FILTER                                                       
         103 HASH                                                              
         104 ERROR LOGGING                                                     
         105 LOAD TABLE CONVENTIONAL                                           
         106 PART JOIN FILTER                                                  
         107 AW SCAN                                                           
         108 PARTITION REFERENCE                                               
         109 PX PARTITION REFERENCE                                            
         110 XPATH EVALUATION                                                  
         111 RESULT CACHE                                                      
         112 DM FEED PREDICTOR-TARGET PAIRS                                    
         113 DM SUPERVISED BINNING                                             
         114 DM HASH-BROADCAST COUNTS                                          
         115 BULK BINDS GET                                                    
         116 APPROXIMATE NDV                                                   
         117 UNPIVOT                                                           
         118 TRANSPOSE                                                         
         119 MONITORING                                                        
         120 CUBE SCAN                                                         
         121 JOINED CUBE SCAN                                                  
         122 CUBE ACCESS                                                       
         123 MAT_VIEW CUBE ACCESS                                              
         124 MAT_VIEW REWRITE CUBE ACCESS                                      
         125 CUBE TABLE SCAN                                                   
         126 JOINED CUBE TABLE SCAN                                            
         127 CUBE TABLE ACCESS                                                 
         128 UNION ALL (RECURSIVE WITH)                                        
         129 RECURSIVE WITH PUMP                                               

130 rows selected.

SQL> SELECT
  2    option_id,
  3    option_name
  4  FROM
  5    DBA_HIST_PLAN_OPTION_NAME;

 OPTION_ID OPTION_NAME                                                         
---------- ----------------------------------------------------------------    
         0                                                                     
         1 OUTER                                                               
         2 ANTI                                                                
         3 SEMI                                                                
         4 CARTESIAN                                                           
         5 TO ROWIDS                                                           
         6 FROM ROWIDS                                                         
         7 COUNT                                                               
         8 SINGLE VALUE                                                        
         9 SAMPLE FAST FULL SCAN                                               
        10 FAST FULL SCAN                                                      
        11 FULL SCAN                                                           
        12 RANGE SCAN                                                          
        13 UNIQUE SCAN                                                         
        14 SKIP SCAN                                                           
        15 FULL SCAN (MIN/MAX)                                                 
        16 RANGE SCAN (MIN/MAX)                                                
        17 FULL SCAN DESCENDING                                                
        18 RANGE SCAN DESCENDING                                               
        19 SKIP SCAN DESCENDING                                                
        20 WITH FILTERING                                                      
        21 WITHOUT FILTERING                                                   
        22 STOPKEY                                                             
        23 FIXED INDEX                                                         
        24 FULL                                                                
        25 HASH                                                                
        26 INDEX BUILD                                                         
        27 UNIQUE (LOCAL)                                                      
        28 NON UNIQUE (LOCAL)                                                  
        29 UNIQUE                                                              
        30 NON UNIQUE                                                          
        31 PICKLER FETCH                                                       
        32 CONSTRUCTOR FETCH                                                   
        33 SUBQUERY FETCH                                                      
        34 REMOTE                                                              
        35 SAMPLE                                                              
        36 SAMPLE BY ROWID RANGE                                               
        37 BY ROWID RANGE                                                      
        38 BY LOCAL INDEX ROWID                                                
        39 BY GLOBAL INDEX ROWID                                               
        40 BY INDEX ROWID                                                      
        41 BY USER ROWID                                                       
        42 CLUSTER                                                             
        43 NOSORT                                                              
        44 BUFFER PUSHED RANK                                                  
        45 BUFFER                                                              
        46 CHILD PUSHED RANK                                                   
        47 CHILD                                                               
        48 SORT PUSHED RANK                                                    
        49 SINGLE                                                              
        50 EMPTY                                                               
        51 INLIST                                                              
        52 ALL                                                                 
        53 ITERATOR                                                            
        54 PARTITION                                                           
        55 AGGREGATE                                                           
        56 GROUP BY STOPKEY                                                    
        57 GROUP BY                                                            
        58 UNIQUE NOSORT                                                       
        59 GROUP BY NOSORT                                                     
        60 GROUP BY ROLLUP                                                     
        61 CUBE                                                                
        62 ROLLUP                                                              
        63 SORT                                                                
        64 CREATE INDEX                                                        
        65 UNIQUE STOPKEY                                                      
        66 ORDER BY STOPKEY                                                    
        67 ORDER BY                                                            
        68 JOIN                                                                
        69 GROUP BY NOSORT ROLLUP                                              
        70 CUSTOM OPTION                                                       
        71 ROWID RANGE                                                         
        72 EXTERNAL CHUNK                                                      
        73 ACYCLIC                                                             
        74 CYCLIC                                                              
        75 ACYCLIC FAST                                                        
        76 ORDERED                                                             
        77 ORDERED FAST                                                        
        78 RIGHT OUTER                                                         
        79 RIGHT ANTI                                                          
        80 RIGHT SEMI                                                          
        81 PARTITION OUTER                                                     
        82 (LOCAL)                                                             
        83 PARTITION (ROWID)                                                   
        84 PARTITION (KEY)                                                     
        85 RANGE                                                               
        86 ROUND-ROBIN                                                         
        87 BROADCAST                                                           
        88 QC (ORDER)                                                          
        89 QC (RANDOM)                                                         
        90 BROADCAST LOCAL                                                     
        91 HASH LOCAL                                                          
        92 HYBRID (ROWID PKEY)                                                 
        93 RANDOM LOCAL                                                        
        94 HASH (BLOCK ADDRESS)                                                
        95 MIXED HASH BCAST LOCAL                                              
        96 MIXED HASH BCAST                                                    
        97 OUTER BUFFERED                                                      
        98 RIGHT OUTER BUFFERED                                                
        99 ANTI BUFFERED                                                       
       100 RIGHT ANTI BUFFERED                                                 
       101 SEMI BUFFERED                                                       
       102 RIGHT SEMI BUFFERED                                                 
       103 CARTESIAN BUFFERED                                                  
       104 PARTITION OUTER BUFFERED                                            
       105 HASH ALL                                                            
       106 RANGE ALL                                                           
       107 LIST                                                                
       108 LIST ALL                                                            
       109 SYSTEM                                                              
       110 FORCED SERIAL                                                       
       111 PARTITION JOIN                                                      
       112 AW HASH                                                             
       113 CREATE                                                              
       114 USE                                                                 
       115 SUBQUERY                                                            
       116 OR                                                                  
       117 MULTI-COLUMN                                                        
       118 NOSORT STOPKEY                                                      
       119 BUFFERED                                                            
       120 FULL OUTER                                                          
       121 FULL OUTER BUFFERED                                                 
       122 NO FILTERING WITH START-WITH                                        
       123 JOIN-FILTER                                                         
       124 ANTI NA                                                             
       125 ANTI SNA                                                            
       126 ANTI NA BUFFERED                                                    
       127 ANTI SNA BUFFERED                                                   
       128 RIGHT ANTI NA                                                       
       129 RIGHT ANTI SNA                                                      
       130 RIGHT ANTI NA BUFFERED                                              
       131 RIGHT ANTI SNA BUFFERED                                             
       132 GROUP BY PIVOT                                                      
       133 GROUP BY NOSORT PIVOT                                               
       134 DUMP                                                                
       135 VERIFY                                                              
       136 STRIP                                                               
       137 STORAGE FULL                                                        
       138 STORAGE SAMPLE                                                      
       139 STORAGE SAMPLE BY ROWID RANGE                                       
       140 STORAGE BY ROWID RANGE                                              
       141 PARTIAL OUTER                                                       
       142 OPTIMIZED                                                           
       143 SPECIFIED                                                           
       144 STORAGE SAMPLE FAST FULL SCAN                                       
       145 STORAGE FAST FULL SCAN                                              
       146 STORAGE FULL SCAN                                                   
       147 STORAGE RANGE SCAN                                                  
       148 STORAGE FULL SCAN (MIN/MAX)                                         
       149 WITH FILTERING (UNIQUE)                                             
       150 WITHOUT FILTERING (UNIQUE)                                          
       151 NO FILTERING WITH SW (UNIQUE)                                       
       152 BREADTH FIRST                                                       
       153 DEPTH FIRST                                                         
       154 AND                                                                 

155 rows selected.

If you look the output closely, you will notice that Oracle 11g R2 has below additional operations and options listed:

  • 11g R2 Operations (line 138-144 highlighted above):

CUBE TABLE SCAN
JOINED CUBE TABLE SCAN
CUBE TABLE ACCESS
UNION ALL (RECURSIVE WITH)
RECURSIVE WITH PUMP

  • 11g R2 Options (line 305-310 highlighted above):

WITH FILTERING (UNIQUE)
WITHOUT FILTERING (UNIQUE)
NO FILTERING WITH SW (UNIQUE)
BREADTH FIRST
DEPTH FIRST
AND

Leave a Comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.