Discovering When an Index Is Useful : Index STATISTICS « Index « Oracle PL / SQL

1. Oracle PL / SQL
2. Index
3. Index STATISTICS

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>

 

Related examples in the same category