Database Reference
In-Depth Information
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 10 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 |
19
|
10
|
|* 2 | INDEX FULL SCAN | I_STATUS | 1 | 19 | 3 |
----------------------------------------------------------------------------
2 - filter("STATUS" IS NOT NULL)
In summary, as the previous examples show, it's possible to efficiently execute a SQL statement with inequalities
or
IS NOT NULL
conditions. However, special care is required.
Min/Max Functions
To execute queries containing the
min
or
max
functions efficiently, two specific operations are available with B-tree
indexes. 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:
SELECT /*+ index(t t_pk) */
min
(id) FROM t
-------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 |
| 2 |
INDEX FULL SCAN (MIN/MAX)
| T_PK | 1 | 1 |
-------------------------------------------------------------
The second,
INDEX RANGE SCAN (MIN/MAX)
, is used when the query specifies a condition on the same column
used in the function:
SELECT /*+ index(t t_pk) */
min
(id) FROM t WHERE
id > 42
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 |
| 2 | FIRST ROW | | 1 | 1 |
|* 3 |
INDEX RANGE SCAN (MIN/MAX)
| T_PK | 1 | 1 |
---------------------------------------------------------------
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:
SELECT /*+ index(t t_pk) */
min
(id),
max
(id) FROM t
