Database Reference
In-Depth Information
EODA@ORA12CR1> create index t_idx on t(gender,object_id);
Index created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
Now, when we query this, we should see the following:
EODA@ORA12CR1> set autotrace traceonly explain
EODA@ORA12CR1> select * from t t1 where object_id = 42;
Execution Plan
...
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 4 (0)| 00:00:01|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 91 | 4 (0)| 00:00:01|
|* 2 | INDEX SKIP SCAN | T_IDX | 1 | | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------------------
The
INDEX SKIP
SCAN
step tells us that Oracle is going to skip throughout the index, looking for points where
GENDER
changes values and read down the tree from there, looking for
OBJECT_ID=42
in each virtual index being
considered. If we increase the number of distinct values for
GENDER
measurably, as follows, we'll see that Oracle stops
seeing the skip scan as being a sensible plan:
EODA@ORA12CR1> update t set gender = chr(mod(rownum,256));
17944 rows updated.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T', cascade=>TRUE );
PL/SQL procedure successfully completed.
It would have 256 mini indexes to inspect, and it opts for a full table scan to find our row
EODA@ORA12CR1> set autotrace traceonly explain
EODA@ORA12CR1> select * from t t1 where object_id = 42;
Execution Plan
...
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 274 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 92 | 274 (1)| 00:00:01 |
--------------------------------------------------------------------------
