Mega Code Archive

Discovering When an Index Is Useful

SQL> SQL> SQL> set autotrace off SQL> SQL> create table indextest as select * from dba_objects 2 where owner in ('OUTLN','PUBLIC','SCOTT','SYS','SYSTEM'); Table created. SQL> SQL> SQL> select owner, count(*) from indextest group by owner; OWNER COUNT(*) ------------------------------ ---------- PUBLIC 2767 OUTLN 8 SYSTEM 449 SYS 6683 SQL> SQL> create index indxtest_owner_idx on indextest (owner); Index created. SQL> SQL> set autotrace trace explain SQL> SQL> select owner, object_name from indextest where owner='SYS'; Execution Plan ---------------------------------------------------------- Plan hash value: 2792531790 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7763 | 629K| 36 (3)| 00:00:01 | |* 1 | TABLE ACCESS FULL| INDEXTEST | 7763 | 629K| 36 (3)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SYS') Note ----- - dynamic sampling used for this statement SQL> SQL> select owner, object_name from indextest where owner='SCOTT'; Execution Plan ---------------------------------------------------------- Plan hash value: 3406603611 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 83 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| INDEXTEST | 1 | 83 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDXTEST_OWNER_IDX | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='SCOTT') Note ----- - dynamic sampling used for this statement SQL> SQL> SQL> analyze table indextest compute statistics for columns owner; Table analyzed. SQL> SQL> select owner, object_name from indextest where owner='SYS'; Execution Plan ---------------------------------------------------------- Plan hash value: 2792531790 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7763 | 530K| 36 (3)| 00:00:01 | |* 1 | TABLE ACCESS FULL| INDEXTEST | 7763 | 530K| 36 (3)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SYS') Note ----- - dynamic sampling used for this statement SQL> SQL> select owner, object_name from indextest where owner='SCOTT'; Execution Plan ---------------------------------------------------------- Plan hash value: 3406603611 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 70 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| INDEXTEST | 1 | 70 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDXTEST_OWNER_IDX | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='SCOTT') Note ----- - dynamic sampling used for this statement SQL> SQL> set autotrace off SQL> drop table indextest; Table dropped. SQL> SQL>