Exadata Storage Index Min/Max Optimization

Before discussing the Exadata-specific feature, let’s review what the database engine can do independently of whether Exadata is used. To execute queries containing the min or max functions efficiently, two specific operations are available with B-tree indexes defined on the column referenced in the min or max function. The first, INDEX FULL SCAN (MIN/MAX), is used when a query doesn’t specify a range condition. In spite of its name, however, it performs no full index scan. It simply gets either the rightmost or the leftmost index key:

SQL> SELECT /*+ index(t t_pk) */ min(id) FROM t;
 
   MIN(ID)
----------
         1
 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last'));
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  gy59myptq5gac, child number 0
-------------------------------------
SELECT /*+ index(t t_pk) */ min(id) FROM t
 
Plan hash value: 2094033419
 
---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE            |      |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_PK |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------

The second, INDEX RANGE SCAN (MIN/MAX), is used when the query specifies a condition on the same column used in the function:

SQL> SELECT /*+ index(t t_pk) */ min(id) FROM t WHERE id > 42;
 
   MIN(ID)
----------
        43
 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  3nxqnh5y5z29b, child number 0
-------------------------------------
SELECT /*+ index(t t_pk) */ min(id) FROM t WHERE id > 42
 
Plan hash value: 4039034112
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   FIRST ROW                  |      |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| T_PK |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("ID">42)

Unfortunately, this optimization technique can’t be applied when both functions (min and max) are used in the same query. In this type of situation, an index full scan is performed. The following query is an example:

SQL> SELECT /*+ index(t t_pk) */ min(id), max(id) FROM t;
 
   MIN(ID)    MAX(ID)
---------- ----------
         1      10000
 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  dn4290uqx6s34, child number 0
-------------------------------------
SELECT /*+ index(t t_pk) */ min(id), max(id) FROM t
 
Plan hash value: 56794325
 
-----------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |      1 |00:00:00.06 |      32 |
|   1 |  SORT AGGREGATE  |      |      1 |      1 |      1 |00:00:00.06 |      32 |
|   2 |   INDEX FULL SCAN| T_PK |      1 |  10000 |  10000 |00:00:00.03 |      32 |
-----------------------------------------------------------------------------------

In Exadata, as of Oracle Database version 12.1.0.2, there is an optimization technique that uses storage indexes instead of B-tree indexes. To take advantage of it, not only a smart scan is required, but a storage index defined on the column referenced in the min or max function must also exist (and that, unfortunately, is something we can’t ensure). Let’s have a look to an example:

  • The table used for the test contains 16GB of data:
SQL> SELECT blocks*block_size AS value
  2  FROM dba_tables JOIN dba_tablespaces USING (tablespace_name)
  3  WHERE table_name = 'ORDERS'
  4  AND owner = sys_context('userenv','current_schema');
 
           VALUE
----------------
  16,725,196,800
  • If the optimization technique is disabled (by default is enabled, but you can control it with the _cell_storidx_minmax_enabled undocumented initialization parameter), as the statistics show, the whole table is read (cell physical IO bytes saved by storage index = 0) and the min and max functions are evaluated by the database instance:
SQL> SELECT /*+ no_parallel */ min(order_total), max(order_total) FROM orders;
 
MIN(ORDER_TOTAL) MAX(ORDER_TOTAL)
---------------- ----------------
               0            21347
 
Elapsed: 00:00:06.24
 
SQL> SELECT name, value
  2  FROM v$mystat NATURAL JOIN v$statname
  3  WHERE name IN ('physical read total bytes',
  4                 'cell physical IO bytes eligible for predicate offload',
  5                 'cell physical IO interconnect bytes returned by smart scan',
  6                 'cell physical IO bytes saved by storage index');
 
NAME                                                                       VALUE
--------------------------------------------------------------- ----------------
physical read total bytes                                         16,725,196,800
cell physical IO bytes eligible for predicate offload             16,725,196,800
cell physical IO bytes saved by storage index                                  0
cell physical IO interconnect bytes returned by smart scan         1,779,074,848
  • If the optimization technique is enabled (which is the default), thanks to a storage index, the cells avoid reading the whole table and much less data is sent back to the database instance (cell physical IO interconnect bytes returned by smart scan is much lower):
SQL> SELECT /*+ no_parallel */ min(order_total), max(order_total) FROM orders;
 
MIN(ORDER_TOTAL) MAX(ORDER_TOTAL)
---------------- ----------------
               0            21347
 
Elapsed: 00:00:00.58
 
SQL> SELECT name, value
  2  FROM v$mystat NATURAL JOIN v$statname
  3  WHERE name IN ('physical read total bytes',
  4                 'cell physical IO bytes eligible for predicate offload',
  5                 'cell physical IO interconnect bytes returned by smart scan',
  6                 'cell physical IO bytes saved by storage index');
 
NAME                                                                       VALUE
--------------------------------------------------------------- ----------------
physical read total bytes                                         16,725,196,800
cell physical IO bytes eligible for predicate offload             16,725,196,800
cell physical IO bytes saved by storage index                     15,565,668,352
cell physical IO interconnect bytes returned by smart scan           123,454,448

Note that even though the query I used for the test contains both the min and the max function, the optimization technique takes place. In other words, there is no limitation similar to the one just discussed with B-tree indexes.

Leave a Reply

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


Time limit is exhausted. Please reload CAPTCHA.