环境准备：
SCOTT@test>select * from v$version;
BANNER
-----------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SCOTT@test>create table ob as select * from dba_objects;
Table created.
Elapsed: 00:00:01.10
SCOTT@test>set autot trace;
SCOTT@test>analyze table ob compute statistics;
Table analyzed.
Elapsed: 00:00:01.89
SCOTT@test>set lines 150
1. INDEX RANGE SCAN
SCOTT@test>create index i_ob_id on ob(object_id);
Index created.
Elapsed: 00:00:00.36
SCOTT@test>select count(*) from ob where object_id=100;
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2010433984
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| I_OB_ID | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2. INDEX UNIQUE SCAN
SCOTT@test>drop index i_ob_id;
Index dropped.
Elapsed: 00:00:00.11
SCOTT@test>create unique index i_ob_id on ob(object_id);
Index created.
Elapsed: 00:00:00.27
SCOTT@test>select count(*) from ob where object_id=100;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3647361231
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX UNIQUE SCAN| I_OB_ID | 1 | 4 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
422 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3. INDEX FAST FULL SCAN
SCOTT@test>select object_id from ob;
86789 rows selected.
Elapsed: 00:00:01.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1611727973
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86789 | 339K| 346 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| OB | 86789 | 339K| 346 (1)| 00:00:05 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6924 consistent gets
0 physical reads
0 redo size
1262506 bytes sent via SQL*Net to client
64054 bytes received via SQL*Net from client
5787 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86789 rows processed
SCOTT@test>alter table ob modify(object_id number constraint nn not null);
Table altered.
Elapsed: 00:00:00.16
SCOTT@test>select object_id from ob;
86789 rows selected.
Elapsed: 00:00:00.81
Execution Plan
----------------------------------------------------------
Plan hash value: 2260539534
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86789 | 339K| 51 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| I_OB_ID | 86789 | 339K| 51 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
5990 consistent gets
179 physical reads
0 redo size
1262506 bytes sent via SQL*Net to client
64054 bytes received via SQL*Net from client
5787 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
86789 rows processed
4. INDEX FULL SCAN
SCOTT@test>select object_id from ob order by 1;
86789 rows selected.
Elapsed: 00:00:00.85
Execution Plan
----------------------------------------------------------
Plan hash value: 2425389225
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86789 | 339K| 182 (1)| 00:00:03 |
| 1 | INDEX FULL SCAN | I_OB_ID | 86789 | 339K| 182 (1)| 00:00:03 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5952 consistent gets
0 physical reads
0 redo size
1262506 bytes sent via SQL*Net to client
64054 bytes received via SQL*Net from client
5787 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86789 rows processed
5. INDEX FULL SCAN DESCENDING
SCOTT@test>select object_id from ob order by 1 desc;
86789 rows selected.
Elapsed: 00:00:00.81
Execution Plan
----------------------------------------------------------
Plan hash value: 606382278
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86789 | 339K| 182 (1)| 00:00:03 |
| 1 | INDEX FULL SCAN DESCENDING| I_OB_ID | 86789 | 339K| 182 (1)| 00:00:03 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5952 consistent gets
0 physical reads
0 redo size
1262506 bytes sent via SQL*Net to client
64054 bytes received via SQL*Net from client
5787 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86789 rows processed
6. INDEX SKIP SCAN
SCOTT@test>create index i_ob2_2 on ob(owner,object_type,object_name);
Index created.
Elapsed: 00:00:00.53
SCOTT@test>analyze table ob compute statistics for all indexed columns;
Table analyzed.
Elapsed: 00:00:01.37
SCOTT@test>select owner,object_type,object_name from ob where object_type='RULE';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 19331172
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 32 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | I_OB2_2 | 1 | 37 | 32 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_TYPE"='RULE')
filter("OBJECT_TYPE"='RULE')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
37 consistent gets
34 physical reads
0 redo size
567 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SCOTT@test>spool off;