Database Reference
In-Depth Information
Inequality and IS NOT NULL Conditions
As pointed out in Table
13-3
, conditions based on either inequalities (
!=
,
<>
) or
IS NOT NULL
can't lead to index range
scans. To illustrate this limitation and show you how to optimize SQL statements hitting it, let's have a look at an
example based on the
inequalities.sql
script. The test table has a column named
status
that is characterized by a
very non-uniform distribution. In fact, most of the rows have their status set to processed (P). Here's the example:
SQL> SELECT status, count(*)
2 FROM t
3 GROUP BY status;
S COUNT(*)
- ----------
A 7
P 159981
R 4
X 8
An application has to select all rows with a status different than processed. For that purpose, it executes the
following query:
SELECT * FROM t WHERE status != 'P'
Even though the query has a very strong selectivity and the
status
column is indexed, the query optimizer
chooses a full table scan that leads to way too many logical reads (23,063) for reading 19 rows:
--------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 23063 |
|* 1 | TABLE ACCESS FULL| T | 1 |
19
|
23063
|
--------------------------------------------------------------
1 - filter("STATUS"<>'P')
In a case like this, where the inequality condition has a strong selectivity, it's nevertheless possible to take
advantage of an index. There are three techniques you can apply.
First, if the inequality condition can be rewritten into an
IN
condition, it's possible to use one index range scan to
apply the
IN
condition. This is an option only when the number of values to be selected is known and the number is
limited. The following query is an example:
SELECT * FROM t WHERE status IN ('A','R','X')
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 13 |
| 1 | INLIST ITERATOR | | 1 | 19 | 13 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 3 |
19
|
13
|
|* 3 | INDEX RANGE SCAN | I_STATUS | 3 | 19 | 7 |
-----------------------------------------------------------------------------
