删除Tivoli.li_idx_04后，才能创建Tivoli.li_idx_05索引）
create index Tivoli.li_idx_05on tivoli.li_db_session_t(dbname,allsess,timstap,activess);
3.3 对表与索引进行统计分析
begin
dbms_stats.gather_table_stats(ownname=>'TIVOLI',tabname=>'LI_DB_SESSION_T',estimate_percent=>50,cascade=>TRUE);
end;
3.4 验证所创建索引的类型
select index_name,partitioning_type,subpartitioning_type,locality,alignment from DBA_PART_INDEXES where index_namein('LI_IDX_01','LI_IDX_02','LI_IDX_03','LI_IDX_04','LI_IDX_05')
输出结果如下：
| INDEX_NAME |
PARTITIONING_TYPE |
SUBPARTITIONING_TYPE |
LOCALITY |
ALIGNMENT |
| LI_IDX_01 |
RANGE |
NONE |
LOCAL |
PREFIXED |
| LI_IDX_02 |
RANGE |
NONE |
LOCAL |
PREFIXED |
| LI_IDX_03 |
RANGE |
NONE |
LOCAL |
NON_PREFIXED |
| LI_IDX_04 |
RANGE |
NONE |
LOCAL |
NON_PREFIXED |
LI_IDX_05因为还没有创建所以查询没有结果，实际上，如果LI_IDX_05不是分区索引，所以，即便该索引建立起来了，在DBA_PART_INDEXES视图中也不会出现。
?
4、五种索引类型下的性能对比
以一条select语句为测试语句。
4.1 场景一：local prefixed类型，索引列为表分区键列
SQL> set autotrace traceonly
SQL> set linesize 999
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"
点评：先是对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"
点评：先是对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"
?
点评：先是对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"
?
点评：先是对LI_IDX_04索引做PARTITIONRANGE ITERATOR 的INDEX RANGE SCAN，access（访问）和filter（过滤）全部在索引中完成，不需要回表，consistent gets只有43，代价极小。
?
4.5 场景五：global index（全局索引）
SQL> select /*+ index(t li_idx_05)*/ * 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: 1711410678
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 200 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| LI_IDX_05 | 10 | 200 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - 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"
点评：先是对LI_IDX_05索引做PARTITIONRANGE ITERATOR 的INDEX RANGE SCAN，access（访问）和filter（过滤）全部在索引中完成，不需要回表，consistent gets只有41，五种不同类型应用中，代价最小。
?
4.6 场景六：w