Written by 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_enabledundocumented initialization parameter), as the statistics show, the whole table is read (cell physical IO bytes saved by storage index= 0) and theminandmaxfunctions 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 scanis 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.