Databases Reference
In-Depth Information
Gather statistics on both the table and the index.
SQL>
execute dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'TEST',
cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
Query the
DBA_INDEXES
view.
SQL> select index_name, num_rows, leaf_blocks from dba_indexes where
index_name = 'TEST_IDX1'
SQL> /
OWNER INDEX_NAME NUM_ROWS LEAF_BLOCKS
------- ----------------------- ---------- -----------
HR TEST_IDX1 1 1
SH TEST_IDX1 0 0
SQL>
The
DBA_INDEXES
views shows that only one leaf block is being utilized by the index to host the single
column value that remains in the table. The optimizer correctly chooses the index, as expected.
SQL> set autotrace traceonly explain
SQL> select * from test where id > 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 2624864549
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 7 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX1 | 1 | | 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">10)
SQL>
Once your rebuild the index and analyze the index (validate structure), this is what you get:
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS
---------- - --------- ---------------------
1 1 0
SQL>
