Database Reference
In-Depth Information
filter by operation 1. Because of the many rowids returned by operation 2, the execution generates 327 logical reads in
total. This is unacceptable when retrieving a single row:
------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
327
|
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 327 |
|* 2 | INDEX RANGE SCAN |
I_N1
| 1 |
527
| 4 |
------------------------------------------------------------------------
1 - filter(("N2"=42 AND "N3"=11))
2 - access(
"N1"=6
)
With an index built on the
n2
column, the situation is basically the same as in the previous example. The only
improvement is that fewer rowids (89) are returned by the index scan. Therefore, far fewer logical reads (85) are
performed in total:
------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
85
|
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 85 |
|* 2 | INDEX RANGE SCAN |
I_N2
| 1 |
89
| 4 |
------------------------------------------------------------------------
1 - filter(("N3"=11 AND "N1"=6))
2 - access(
"N2"=42
)
With an index built on the
n3
column, the situation is still similar to the previous ones. In fact, the index scan
returns plenty of rowids (164). The total number of logical reads (141) is still too high:
------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
141
|
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 141 |
|* 2 | INDEX RANGE SCAN |
I_N3
| 1 |
164
| 4 |
------------------------------------------------------------------------
1 - filter(("N2"=42 AND "N1"=6))
2 - access(
"N3"=11
)
In summary, none of the three indexes can apply the predicates efficiently. The selectivities of the three
restrictions taken one by one are too high. This observation is in line with the object statistics stored in the data
dictionary. In fact, the number of distinct values for each column is low, as demonstrated by the following query:
SQL> SELECT column_name, num_distinct
2 FROM user_tab_columns
3 WHERE table_name = 'T' AND column_name IN ('ID', 'N1', 'N2', 'N3');
