Database Reference
In-Depth Information
Second, if the previous technique can't be applied because the values are unknown or the number of values to
be specified is too high, it's always possible to rewrite an inequality with two disjunct range predicates and, as a result,
execute one index range scan for each of them. The idea is to take advantage of the or expansion query transformation
(refer to Chapter 6 for information about it). The query would be rewritten like this:
SELECT * FROM t WHERE status < 'P' OR status > 'P'
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 12 |
| 1 | CONCATENATION | | 1 | 19 | 12 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 7 | 5 |
|* 3 | INDEX RANGE SCAN | I_STATUS | 1 | 7 | 3 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 7 |
|* 5 | INDEX RANGE SCAN | I_STATUS | 1 | 12 | 3 |
-----------------------------------------------------------------------------
3 - access("STATUS"<'P')
5 - access("STATUS">'P')
filter(LNNVL("STATUS"<'P'))
In case or expansion doesn't kick in automatically, you can manually rewrite the query to make sure that both
component queries can take advantage of an index range scan and, as a result, reduce the number of logical reads to a
minimum:
SELECT * FROM t WHERE status < 'P'
UNION ALL
SELECT * FROM t WHERE status > 'P'
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 12 |
| 1 | UNION-ALL | | 1 | 19 | 12 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 7 | 5 |
|* 3 | INDEX RANGE SCAN | I_STATUS | 1 | 7 | 3 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 7 |
|* 5 | INDEX RANGE SCAN | I_STATUS | 1 | 12 | 3 |
-----------------------------------------------------------------------------
3 - access("STATUS"<'P')
5 - access("STATUS">'P')
The third technique is based on an index full scan. To get it, you can simply force an index full scan with, for
example, the index hint. From a performance point of view, as shown in the following example, it's not optimal,
though. For a query with very strong selectivity, the ratio between the number of logical reads (299) and the number
of returned rows (19) is too high:
SELECT /*+ index(t) */ * FROM t WHERE status != 'P'
 
Search WWH ::




Custom Search