点评：先是对LI_IDX_02索引做PARTITIONRANGE ITERATOR 的INDEX RANGE SCAN，需要返回的数据，access（访问）和filter（过滤）全部在索引中完成，不需要回表，consistent gets为3141，比使用LI_IDX_01索引的consistent gets小83倍。
4.3 场景三：localnonprefixed类型，索引列中不包含表分区键列SQL> select /*+ index(t li_idx_03)*/ * 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: 3955115924
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 200 | 98 (0)| 00:00:02 | | |
| 1 | PARTITION RANGE ITERATOR | | 10 | 200 | 98 (0)| 00:00:02 | 11 | 12 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| LI_DB_SESSION_T | 10 | 200 | 98 (0)| 00:00:02 | 11 | 12 |
|* 3 | INDEX RANGE SCAN | LI_IDX_03 | 136 | | 5 (0)| 00:00:01 | 11 | 12 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("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'))
3 - access("T"."DBNAME"='COSTDB' AND "T"."ALLSESS"=28 AND "T"."ACTIVESS"=4)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
658 consistent gets
174 physical reads
0 redo size
13309 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_03索引做PARTITIONRANGE ITERATOR 的INDEX RANGE SCAN，然后通过索引中的ROWID回表（TABLE ACCESS BY LOCAL INDEX ROWID），consistent gets为658。
4.4 场景四：localnonprefixed类型，多列索引，表分区键列不为前置位置SQL> select /*+ index(t li_idx_04)*/ * 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: 3237585467
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 200 | 5 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 10 | 200 | 5 (0)| 00:00:01 | 11 | 12 |
|* 2 | INDEX RANGE SCAN | LI_IDX_04 | 10 | 200 | 5 (0)| 00:00:01 | 11 | 12 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."DBNAME"='COSTDB' AND "T"."ALLSESS"=28 AND "T"."TIMSTAP">TO_DATE('
2011-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."ACTIVESS"=4 AND
"T"."TIMSTAP"<TO_DATE(' 2011-01-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("T"."ACTIVESS"=4)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
43 consistent gets
9 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