SQL> select /*+ index(t li_idx_01)*/ * from tivoli.li_db_session_t t where t.allsess=28 and t.dbname='COSTDB' and t.timstap >to_date('2011-01-01','yyyy-mm-dd') and t.timstap < to_date('2011-01-20','yyyy-mm-dd') and t.activess=4;
498 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3409921846
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 200 | 208K (1)| 00:41:38 | | |
| 1 | PARTITION RANGE ITERATOR | | 10 | 200 | 208K (1)| 00:41:38 | 11 | 12 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| LI_DB_SESSION_T | 10 | 200 | 208K (1)| 00:41:38 | 11 | 12 |
|* 3 | INDEX RANGE SCAN | LI_IDX_01 | 630K| | 1681 (1)| 00:00:21 | 11 | 12 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."ALLSESS"=28 AND "T"."DBNAME"='COSTDB' AND "T"."ACTIVESS"=4)
3 - access("T"."TIMSTAP">TO_DATE(' 2011-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."TIMSTAP"<TO_DATE(' 2011-01-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
262334 consistent gets
0 physical reads
0 redo size
9997 bytes sent via SQL*Net to client
886 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
498 rows processed
点评：先是对LI_IDX_01索引做PARTITIONRANGE ITERATOR 的INDEX RANGE SCAN，然后通过索引中的ROWID回表（TABLE ACCESS BY LOCAL INDEX ROWID），consistent gets达到262334，代价很高。
4.2 场景二：local prefixed类型，多列索引，表分区键列为前置位置SQL> select /*+ index(t li_idx_02)*/ * from tivoli.li_db_session_t t where t.allsess=28 and t.dbname='COSTDB' and t.timstap >to_date('2011-01-01','yyyy-mm-dd') and t.timstap < to_date('2011-01-20','yyyy-mm-dd') and t.activess=4;
498 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3413193479
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 200 | 2783 (1)| 00:00:34 | | |
| 1 | PARTITION RANGE ITERATOR| | 10 | 200 | 2783 (1)| 00:00:34 | 11 | 12 |
|* 2 | INDEX RANGE SCAN | LI_IDX_02 | 10 | 200 | 2783 (1)| 00:00:34 | 11 | 12 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."TIMSTAP">TO_DATE(' 2011-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."DBNAME"='COSTDB' AND "T"."ALLSESS"=28 AND "T"."ACTIVESS"=4 AND "T"."TIMSTAP"<TO_DATE('
2011-01-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("T"."ALLSESS"=28 AND "T"."DBNAME"='COSTDB' AND "T"."ACTIVESS"=4)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3141 consistent gets
3099 physical reads
0 redo size
9997 bytes sent via SQL*Net to client
886 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
498 rows processed