Where does the Exadata storage() predicate come from?

On Exadata (or when setting cell_offload_plan_display = always on non-Exadata) you may see the storage() predicate in addition to the usual access() and filter() predicates in an execution plan:

SQL> SELECT * FROM dual WHERE dummy = 'X';

D
-
X

Check the plan:

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  dtjs9v7q7zj1g, child number 0
-------------------------------------
SELECT * FROM dual WHERE dummy = 'X'

Plan hash value: 272002086

------------------------------------------------------------------------
| Id  | Operation                 | Name | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |        |       |     2 (100)|
|*  1 |  TABLE ACCESS STORAGE FULL| DUAL |      1 |     2 |     2   (0)|
------------------------------------------------------------------------

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

   1 - storage("DUMMY"='X')
       filter("DUMMY"='X')

The access() and filter() predicates come from the corresponding ACCESS_PREDICATES and FILTER_PREDICATES columns in V$SQL_PLAN. But there’s no STORAGE_PREDICATES column there!

SQL> @desc v$sql_plan
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ADDRESS                                  RAW(4)
    2      HASH_VALUE                               NUMBER
    3      SQL_ID                                   VARCHAR2(13)
  ...
   33      TEMP_SPACE                               NUMBER
   34      ACCESS_PREDICATES                        VARCHAR2(4000)
   35      FILTER_PREDICATES                        VARCHAR2(4000)
   36      PROJECTION                               VARCHAR2(4000)
  ...
   40      OTHER_XML                                CLOB

So where does the storage predicate come from then?

The answer is that there is no storage() predicate column in any V$ views. The storage() predicate actually comes from the ACCESS_PREDICATE column, but the DBMS_XPLAN.DISPLAY functions just have extra logic in them that if the execution plan line (OPTION column in V$SQL_PLAN) contains STORAGE string, then any access() predicates for that line must be storage() predicates instead!

SQL> SELECT id, access_predicates,filter_predicates FROM v$sql_plan WHERE sql_id = 'dtjs9v7q7zj1g' AND child_number = 0;

        ID ACCESS_PREDICATES    FILTER_PREDICATES
---------- -------------------- --------------------
         0
         1 "DUMMY"='X'          "DUMMY"='X'

This actually makes sense, as the filter() predicates are the “dumb brute-force” predicates that are not able to pass any information (about what values are they looking for) inside the access path row source they are filtering. In other words, a filter() function fetches all the rows from its rowsource and it throws away everything that doesn’t match the filter condition.

The access() predicate, on the other hand, is able to pass in the value (or range) it’s looking for inside its row source. For example, when doing an index unique lookup, the access() predicate can send the value your query is looking for right into the index traversing code, so you only retrieve the rows you want as opposed to retrieving everything and throwing the non-wanted rows away.

So the access() predicate traditionally showed up for index access paths and also hash join row sources, but never for full table scans. Now, with Exadata, even full table scans can work in a smart way (allowing you pass in the values you’re looking for into the storage layer), so some of the full scanning row sources support the access() predicate now too – with the catch that if the OPTION column in V$SQL_PLAN contains “STORAGE”, the access() predicates are shown as storage().

Note that the SQL Monitor reports (to my knowledge) still don’t support this display logic, so you would see row sources like TABLE ACCESS STORAGE FULL with filter() and access() predicates on them – the access() on these STORAGE row sources really means storage()