B*索引，谓词不适用索引前导列的测试

SQL> create table t as select object_id,object_name,object_type from dba_objects;
Table created.

SQL> create index t_idx_1 on t(object_id,object_name);
Index created.
SQL> analyze table t compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL> set autotrace traceonly

SQL> select object_id,object_name,object_type from t where object_name='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)|    Time  |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    2 |    64 |      13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |    2 |    64 |      13 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T')
Statistics
----------------------------------------------------------
 24 recursive calls
 0 db block gets
 53 consistent gets
 0 physical reads
 0 redo size
 544 bytes sent via SQL*Net to client
 385 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed

SQL> select object_id,object_name from t where object_name='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 3278571932
--------------------------------------------------------------------------------
| Id | Operation            | Name     | Rows | Bytes | Cost (%CPU)|     Time |
--------------------------------------------------------------------------------
| 0  | SELECT STATEMENT     |          |    2 |    42 |      12 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN | T_IDX_1  |    2 |    42 |      12 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T')
Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 52 consistent gets
 0 physical reads
 0 redo size
 477 bytes sent via SQL*Net to client
 385 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed

SQL> set autotrace off
SQL> update t set object_type='INDEX' where object_type not in ('INDEX','TABLE','PACKAGE');
7585 rows updated.
SQL> commit;
Commit complete.
SQL> set autotrace traceonly
SQL> analyze table t compute statistics;
Table analyzed.

SQL> select object_id,object_name,object_type from t where t.object_name='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 602818099
---------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |     1|    27 |      5 (0) | 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID | T        |     1|    27 |      5 (0) | 00:00:01 |
|* 2 | INDEX SKIP SCAN             | T_IDX_2  |     1|       |      4 (0) | 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_NAME"='T')
 filter("T"."OBJECT_NAME"='T')
Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 22 consistent gets
 0 physical reads
 0 redo size
 544 bytes sent via SQL*Net to client
 385 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed

SQL> set autotrace off
SQL> update t set object_type=chr(mod(rownum,12));
9930 rows updated.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> set autotrace traceonly
SQL> select object_id,object_name,object_type from t where t.object_name='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    1 |    22 |      13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |    1 |    22 |      13 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_NAME"='T')
Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 51 consistent gets
 0 physical reads
 0 redo size
 540 bytes sent via SQL*Net to client
 385 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24496749/viewspace-723280/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24496749/viewspace-723280/