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>