Database Reference
In-Depth Information
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 299 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 |
19
|
299
|
|* 2 | INDEX FULL SCAN | I_STATUS | 1 | 19 | 293 |
----------------------------------------------------------------------------
2 - filter("STATUS"<>'P')
To optimally execute an index full scan, the size of the index should be as small as possible. To achieve that,
you can implement one of two tricks. The idea of the first one is to avoid indexing the popular value by defining a
function-based index (later on, the “Function-based Indexes” section provides additional information about these
indexes) that excludes it. To implement this trick, both the index and the SQL statement using the index have to
be altered:
•
Create the function-based index excluding the popular value (for more complex conditions
you can also use the
CASE
expression or the
decode
function):
CREATE INDEX i_status ON t (
nullif(status, 'P')
)
•
Alter the predicate of the query to take advantage of the index:
SELECT * FROM t WHERE
nullif(status, 'P')
IS NOT NULL
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 |
19
|
9
|
|* 2 | INDEX FULL SCAN | I_STATUS | 1 | 19 | 3 |
----------------------------------------------------------------------------
2 - filter("T"."SYS_NC00004$" IS NOT NULL)
The idea of the second trick is to replace the most popular value with
NULL
and, thereby preventing most rows
from being referenced in the index. But be careful—this technique works for B-tree indexes only. To implement it,
carry out the following steps:
•
Replace the most popular value with
NULL
:
UPDATE t SET status = NULL WHERE status = 'P'
•
Rebuild the index to shrink its size to the minimum:
ALTER INDEX i_status REBUILD
•
Replace the inequality with a
IS NOT NULL
:
SELECT * FROM t WHERE status IS NOT NULL
