oracle_分区表的索引类型以及是否带分区键索引的区别
One. 介绍一下分区表的索引类型，以及简述各个类型的适用场景。Two. 验证一下组合分区索引带不带分区键的区别，用数据来说话。
以下说明都是针对分区表的索引介绍。（想着物理存储属性更能了解下面索引的说明）
1. 本地索引和全局索引
本地索引 ： 索引分区键值等于表的分区键值本地前缀： 在索引定义中，表的分区键是索引的前导列。本地非前缀： 在索引定义中， 表的分区键不是索引的前导列。
全局分区索引： 分区索引不是本地的。全局分区索引也可以用于非分区表上。
全局非分区索引： 索引不是分区的。
2. 验证带分区键本地分区索引的区别。
SQL> SELECT * FROM v$version;
BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL>create table parttest(owner varchar2(20) not null ,object_id number not null ,object_name varchar2(32) ,created date) partition by list(owner)(partition part1 values ('SYS') ,partition part2 values ('OUTLN') ,partition part3 values ('SYSTEM') ,partition part4 values ('SUN') ,partition part5 values ('SQLTXPLAIN') ,partition part6 values ('APPQOSSYS') ,partition part7 values ('DBSNMP') ,partition part8 values ('SQLTXADMIN') ,partition part9 values ('DIP'), partition part10 values ('ORACLE_OCM'),partition part11 values (default))/
DROP TABLE parttest;
insert into parttest select owner,object_id,object_name,created from DBA_OBJECTS;commit;
--索引不包含分区键create index idx_nopartkey on parttest(created) local nologging;
-- 索引包含分区键
create index idx_partkey on parttest(created,owner) local nologging;create index idx_partkey2 on parttest(object_NAME,owner) local nologging;create index idx_partkey3 on parttest(owner,object_NAME) local nologging;create index idx_nopartkey2 on parttest(object_NAME) local nologging;
--收集统计信息SQL> exec dbms_stats.gather_table_stats('SUN','PARTTEST',cascade=>true,no_invalidate=>false,method_opt=>'for all columns size 1',estimate_percent=>dbms_stats.auto_sample_size,degree=>24) ;
PL/SQL procedure successfully completed.
分析过程分如下几个方面1.用带分区键值的索引进行查询，但在where条件中不加分区条件2.用带分区键值的索引进行查询，但在where条件中加分区条件3.用不带分区键值的索引进行查询，但在where条件中不加分区条件4.用不带分区键值的索引进行查询，但在where条件中加分区条5.用带分区键值的索引进行查询，但在where条件中加分区条(与4的索引键相同，只是带索引键值)6.用带分区键值的索引进行查询，但在where条件中加分区条(与4的索引键相同，只是带前导索引键值)7.用带分区键值的索引进行跨分区查询，但在where条件中加分区条件(与4的索引键相同，只是带索引前导键值)8.用带分区键值的索引进行跨分区查询，但在where条件中加分区条(与4的索引键相同，只是带后导索引键值)9.用带分区键值的索引进行跨分区查询，但在where条件中加分区条件(与4的索引键相同，只是带索引键值)
第一种情况:用带分区键值的索引进行查询，但是where条件中不加分区条件set autotrace traceonlySELECT object_name FROM parttest WHERE object_name LIKE 'OR%';
Execution Plan----------------------------------------------------------Plan hash value: 3693814982
---------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 57 | 12 (0)| 00:00:01 | | || 1 | PARTITION LIST ALL| | 3 | 57 | 12 (0)| 00:00:01 | 1 | 11 ||* 2 | INDEX RANGE SCAN | IDX_PARTKEY2 | 3 | 57 | 12 (0)| 00:00:01 | 1 | 11 |---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("OBJECT_NAME" LIKE 'OR%')filter("OBJECT_NAME" LIKE 'OR%')
Statistics----------------------------------------------------------1 recursive calls0 db block gets23 consistent gets0 physical reads0 redo size3768 bytes sent via SQL*Net to client589 bytes received via SQL*Net from client8 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)105 rows processed
第二种情况:用带分区键值的索引进行查询，但是where条件中加分区条件set autotrace traceonlySELECT object_name FROM parttest WHERE object_name LIKE 'OR%' AND owner='SYS';
Execution Plan----------------------------------------------------------Plan hash value: 2753556796
------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 46 | 2 (0)| 00:00:01 | | || 1 | PARTITION LIST SINGLE| | 2 | 46 | 2 (0)| 00:00:01 | KEY | KEY ||* 2 | INDEX RANGE SCAN | IDX_PARTKEY2 | 2 | 46 | 2 (0)| 00:00:01 | 1 | 1 |------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("OBJECT_NAME" LIKE 'OR%' AND "OWNER"='SYS')filter("OBJECT_NAME" LIKE 'OR%')
Statistics----------------------------------------------------------1 recursive calls0 db block gets6 consistent gets0 physical reads0 redo size2279 bytes sent via SQL*Net to client556 bytes received via SQL*Net from client5 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)58 rows processed第三种情况:用不带分区键值的索引进行查询，但是where条件中不加分区条件
set autotrace traceonlySELECT object_name FROM parttest WHERE created=to_date('2014-12-15 22:29:22','YYYY-MM-DD HH24:MI:SS');
Execution Plan----------------------------------------------------------Plan hash value: 646636157
--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 35 | 945 | 13 (0)| 00:00:01 | | || 1 | PARTITION LIST ALL | | 35 | 945 | 13 (0)| 00:00:01 | 1 | 11 || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTTEST | 35 | 945 | 13 (0)| 00:00:01 | 1 | 11 ||* 3 | INDEX RANGE SCAN | IDX_NOPARTKEY | 35 | | 12 (0)| 00:00:01 | 1 | 11 |--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd hh34:mi:ss'))
Statistics----------------------------------------------------------1 recursive calls0 db block gets24 consistent gets0 physical reads0 redo size1780 bytes sent via SQL*Net to client545 bytes received via SQL*Net from client4 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)41 rows processed第四种情况:用不带分区键值的索引进行查询，但是where条件中加分区条件
set autotrace traceonlySELECT object_name FROM parttest a WHERE created=to_date('2014-12-15 22:29:22','YYYY-MM-DD HH24:MI:SS') AND owner='SYS';
Execution Plan----------------------------------------------------------Plan hash value: 3242664717
--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 28 | 868 | 2 (0)| 00:00:01 | | || 1 | PARTITION LIST SINGLE | | 28 | 868 | 2 (0)| 00:00:01 | KEY | KEY || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTTEST | 28 | 868 | 2 (0)| 00:00:01 | 1 | 1 ||* 3 | INDEX RANGE SCAN | IDX_NOPARTKEY | 28 | | 1 (0)| 00:00:01 | 1 | 1 |--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd hh34:mi:ss'))
Statistics----------------------------------------------------------0 recursive calls0 db block gets7 consistent gets0 physical reads0 redo size1191 bytes sent via SQL*Net to client534 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)21 rows processed
第五种情况：用带分区键值的索引进行查询，但在where条件中加分区条(与4的索引键相同，只是带索引键值)
SELECT object_name FROM parttest a WHERE created=to_date('2014-12-15 22:29:22','YYYY-MM-DD HH24:MI:SS') AND owner='SYS'; Execution Plan----------------------------------------------------------Plan hash value: 1150146376
------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 28 | 868 | 2 (0)| 00:00:01 | | || 1 | PARTITION LIST SINGLE | | 28 | 868 | 2 (0)| 00:00:01 | KEY | KEY || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTTEST | 28 | 868 | 2 (0)| 00:00:01 | 1 | 1 ||* 3 | INDEX RANGE SCAN | IDX_PARTKEY | 17 | | 1 (0)| 00:00:01 | 1 | 1 |------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd hh34:mi:ss') AND "OWNER"='SYS')
Statistics----------------------------------------------------------0 recursive calls0 db block gets7 consistent gets0 physical reads0 redo size1191 bytes sent via SQL*Net to client534 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)21 rows processed第六种情况：用带分区键值的索引进行查询，但在where条件中加分区条(与4的索引键相同，只是带前导索引键值)set autotrace traceonlySELECT object_name FROM parttest a WHERE created=to_date('2014-12-15 22:29:22','YYYY-MM-DD HH24:MI:SS') AND owner='SYS';
Execution Plan----------------------------------------------------------Plan hash value: 1150146376
------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 28 | 868 | 2 (0)| 00:00:01 | | || 1 | PARTITION LIST SINGLE | | 28 | 868 | 2 (0)| 00:00:01 | KEY | KEY || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTTEST | 28 | 868 | 2 (0)| 00:00:01 | 1 | 1 ||* 3 | INDEX RANGE SCAN | IDX_PARTKEY | 17 | | 1 (0)| 00:00:01 | 1 | 1 |------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd hh34:mi:ss') AND "OWNER"='SYS')
Statistics----------------------------------------------------------0 recursive calls0 db block gets7 consistent gets0 physical reads0 redo size1191 bytes sent via SQL*Net to client534 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)21 rows processed
第七种情况：用带分区键值的索引进行跨分区查询，但在where条件中加分区条件(与4的索引键相同，只是带索引前导键值)set autotrace traceonlySELECT object_name FROM parttest a WHERE object_name LIKE 'OR%' AND owner IN ('SYS','SUN');Execution Plan----------------------------------------------------------Plan hash value: 1341146800
---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 | | || 1 | INLIST ITERATOR | | | | | | | || 2 | PARTITION LIST ITERATOR| | 1 | 25 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) ||* 3 | INDEX RANGE SCAN | IDX_PARTKEY3 | 1 | 25 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) |---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access(("OWNER"='SUN' OR "OWNER"='SYS') AND "OBJECT_NAME" LIKE 'OR%')filter("OBJECT_NAME" LIKE 'OR%')
Statistics----------------------------------------------------------1 recursive calls0 db block gets8 consistent gets1 physical reads0 redo size2540 bytes sent via SQL*Net to client567 bytes received via SQL*Net from client6 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)62 rows processed
第八种情况：用带分区键值的索引进行跨分区查询，但在where条件中加分区条(与4的索引键相同，只是带后导索引键值)set autotrace traceonlySELECT object_name FROM parttest a WHERE object_name LIKE 'OR%' AND owner IN ('SYS','SUN');
Execution Plan----------------------------------------------------------Plan hash value: 2095150599
------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 | | || 1 | PARTITION LIST INLIST| | 1 | 25 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) ||* 2 | INDEX RANGE SCAN | IDX_PARTKEY2 | 1 | 25 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) |------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("OBJECT_NAME" LIKE 'OR%')filter("OBJECT_NAME" LIKE 'OR%')
Statistics----------------------------------------------------------209 recursive calls2 db block gets180 consistent gets0 physical reads0 redo size2497 bytes sent via SQL*Net to client567 bytes received via SQL*Net from client6 SQL*Net roundtrips to/from client13 sorts (memory)0 sorts (disk)62 rows processed
第九种情况：用带分区键值的索引进行跨分区查询，但在where条件中加分区条件(与4的索引键相同，只是带索引键值)
set autotrace traceonlySELECT object_name FROM parttest a WHERE object_name LIKE 'OR%' AND owner IN ('SYS','SUN');
Execution Plan----------------------------------------------------------Plan hash value: 2097624711
---------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 5 (0)| 00:00:01 | | || 1 | PARTITION LIST INLIST | | 1 | 25 | 5 (0)| 00:00:01 |KEY(I) |KEY(I) || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTTEST | 1 | 25 | 5 (0)| 00:00:01 |KEY(I) |KEY(I) ||* 3 | INDEX RANGE SCAN | IDX_NOPARTKEY2 | 3 | | 3 (0)| 00:00:01 |KEY(I) |KEY(I) |---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("OBJECT_NAME" LIKE 'OR%')filter("OBJECT_NAME" LIKE 'OR%')
Statistics----------------------------------------------------------1 recursive calls0 db block gets27 consistent gets1 physical reads0 redo size2497 bytes sent via SQL*Net to client567 bytes received via SQL*Net from client6 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)62 rows processed
总结：
1.在使用分区表示，WHERE 条件最好带上分区键，要不然就失去了分区的意义，一个分区在物理上是一个表，全分区表扫描比全非分区表扫描要更多的IO读。2.WHERE 条件带分区的情况下，单分区带不带分区键好像意义不大， 跨分区扫描的情况下，带前导分区键的索引效率高。综合所述，如果需要创建组合索引，建议创建带前导分区键的分区索引。
3. 测试在非分区表上创建全局分区索引与普通索引区别，看着意义不大，使用场景未明。
CREATE TABLE gpart AS select owner,object_id,object_name,created from DBA_OBJECTS;SELECT distinct TO_char(created,'YYYY-MM-DD') FROM gpart;
exec dbms_stats.gather_table_stats('SUN','GPART',cascade=>true,no_invalidate=>false,method_opt=>'for all columns size 1',estimate_percent=>dbms_stats.auto_sample_size,degree=>24) ;
create index idx_gpart1 ON gpart(created) nologging;DROP INDEX idx_gpart1;
set autotrace traceonlySELECT * FROM gpart t WHERE created > TO_DATE('2015-04-02','YYYY-MM-DD') ;
Execution Plan----------------------------------------------------------Plan hash value: 4136711861
------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1005 | 36180 | 13 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| GPART | 1005 | 36180 | 13 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_GPART1 | 1005 | | 4 (0)| 00:00:01 |------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("CREATED">TO_DATE(' 2015-04-02 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
Statistics----------------------------------------------------------1 recursive calls0 db block gets34 consistent gets0 physical reads0 redo size9616 bytes sent via SQL*Net to client644 bytes received via SQL*Net from client13 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)174 rows processedcreate index idx_gpart2on gpart(created)global partition by range (created)(partition GLOBAL1 values less than (TO_DATE('2014-12-15','YYYY-MM-DD')),partition GLOBAL2 values less than (TO_DATE('2015-03-11','YYYY-MM-DD')),partition GLOBAL3 values less than (TO_DATE('2015-03-24','YYYY-MM-DD')),partition GLOBAL4 values less than (TO_DATE('2015-04-01','YYYY-MM-DD')),partition GLOBAL5 values less than (MAXVALUE)) nologging;DROP INDEX idx_gpart2;
set autotrace traceonlySELECT * FROM gpart t WHERE created > TO_DATE('2015-04-02','YYYY-MM-DD') ;
Execution Plan----------------------------------------------------------Plan hash value: 4217733073
-----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1005 | 36180 | 13 (0)| 00:00:01 | | || 1 | PARTITION RANGE SINGLE | | 1005 | 36180 | 13 (0)| 00:00:01 | 5 | 5 || 2 | TABLE ACCESS BY INDEX ROWID| GPART | 1005 | 36180 | 13 (0)| 00:00:01 | | ||* 3 | INDEX RANGE SCAN | IDX_GPART2 | 1005 | | 4 (0)| 00:00:01 | 5 | 5 |-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("CREATED">TO_DATE(' 2015-04-02 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
Statistics----------------------------------------------------------1 recursive calls0 db block gets34 consistent gets0 physical reads0 redo size5769 bytes sent via SQL*Net to client644 bytes received via SQL*Net from client13 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)174 rows processed
以下说明都是针对分区表的索引介绍。（想着物理存储属性更能了解下面索引的说明）
1. 本地索引和全局索引
本地索引 ： 索引分区键值等于表的分区键值本地前缀： 在索引定义中，表的分区键是索引的前导列。本地非前缀： 在索引定义中， 表的分区键不是索引的前导列。
全局分区索引： 分区索引不是本地的。全局分区索引也可以用于非分区表上。
全局非分区索引： 索引不是分区的。
2. 验证带分区键本地分区索引的区别。
SQL> SELECT * FROM v$version;
BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL>create table parttest(owner varchar2(20) not null ,object_id number not null ,object_name varchar2(32) ,created date) partition by list(owner)(partition part1 values ('SYS') ,partition part2 values ('OUTLN') ,partition part3 values ('SYSTEM') ,partition part4 values ('SUN') ,partition part5 values ('SQLTXPLAIN') ,partition part6 values ('APPQOSSYS') ,partition part7 values ('DBSNMP') ,partition part8 values ('SQLTXADMIN') ,partition part9 values ('DIP'), partition part10 values ('ORACLE_OCM'),partition part11 values (default))/
DROP TABLE parttest;
insert into parttest select owner,object_id,object_name,created from DBA_OBJECTS;commit;
--索引不包含分区键create index idx_nopartkey on parttest(created) local nologging;
-- 索引包含分区键
create index idx_partkey on parttest(created,owner) local nologging;create index idx_partkey2 on parttest(object_NAME,owner) local nologging;create index idx_partkey3 on parttest(owner,object_NAME) local nologging;create index idx_nopartkey2 on parttest(object_NAME) local nologging;
--收集统计信息SQL> exec dbms_stats.gather_table_stats('SUN','PARTTEST',cascade=>true,no_invalidate=>false,method_opt=>'for all columns size 1',estimate_percent=>dbms_stats.auto_sample_size,degree=>24) ;
PL/SQL procedure successfully completed.
分析过程分如下几个方面1.用带分区键值的索引进行查询，但在where条件中不加分区条件2.用带分区键值的索引进行查询，但在where条件中加分区条件3.用不带分区键值的索引进行查询，但在where条件中不加分区条件4.用不带分区键值的索引进行查询，但在where条件中加分区条5.用带分区键值的索引进行查询，但在where条件中加分区条(与4的索引键相同，只是带索引键值)6.用带分区键值的索引进行查询，但在where条件中加分区条(与4的索引键相同，只是带前导索引键值)7.用带分区键值的索引进行跨分区查询，但在where条件中加分区条件(与4的索引键相同，只是带索引前导键值)8.用带分区键值的索引进行跨分区查询，但在where条件中加分区条(与4的索引键相同，只是带后导索引键值)9.用带分区键值的索引进行跨分区查询，但在where条件中加分区条件(与4的索引键相同，只是带索引键值)
第一种情况:用带分区键值的索引进行查询，但是where条件中不加分区条件set autotrace traceonlySELECT object_name FROM parttest WHERE object_name LIKE 'OR%';
Execution Plan----------------------------------------------------------Plan hash value: 3693814982
---------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 57 | 12 (0)| 00:00:01 | | || 1 | PARTITION LIST ALL| | 3 | 57 | 12 (0)| 00:00:01 | 1 | 11 ||* 2 | INDEX RANGE SCAN | IDX_PARTKEY2 | 3 | 57 | 12 (0)| 00:00:01 | 1 | 11 |---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("OBJECT_NAME" LIKE 'OR%')filter("OBJECT_NAME" LIKE 'OR%')
Statistics----------------------------------------------------------1 recursive calls0 db block gets23 consistent gets0 physical reads0 redo size3768 bytes sent via SQL*Net to client589 bytes received via SQL*Net from client8 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)105 rows processed
第二种情况:用带分区键值的索引进行查询，但是where条件中加分区条件set autotrace traceonlySELECT object_name FROM parttest WHERE object_name LIKE 'OR%' AND owner='SYS';
Execution Plan----------------------------------------------------------Plan hash value: 2753556796
------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 46 | 2 (0)| 00:00:01 | | || 1 | PARTITION LIST SINGLE| | 2 | 46 | 2 (0)| 00:00:01 | KEY | KEY ||* 2 | INDEX RANGE SCAN | IDX_PARTKEY2 | 2 | 46 | 2 (0)| 00:00:01 | 1 | 1 |------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("OBJECT_NAME" LIKE 'OR%' AND "OWNER"='SYS')filter("OBJECT_NAME" LIKE 'OR%')
Statistics----------------------------------------------------------1 recursive calls0 db block gets6 consistent gets0 physical reads0 redo size2279 bytes sent via SQL*Net to client556 bytes received via SQL*Net from client5 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)58 rows processed第三种情况:用不带分区键值的索引进行查询，但是where条件中不加分区条件
set autotrace traceonlySELECT object_name FROM parttest WHERE created=to_date('2014-12-15 22:29:22','YYYY-MM-DD HH24:MI:SS');
Execution Plan----------------------------------------------------------Plan hash value: 646636157
--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 35 | 945 | 13 (0)| 00:00:01 | | || 1 | PARTITION LIST ALL | | 35 | 945 | 13 (0)| 00:00:01 | 1 | 11 || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTTEST | 35 | 945 | 13 (0)| 00:00:01 | 1 | 11 ||* 3 | INDEX RANGE SCAN | IDX_NOPARTKEY | 35 | | 12 (0)| 00:00:01 | 1 | 11 |--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd hh34:mi:ss'))
Statistics----------------------------------------------------------1 recursive calls0 db block gets24 consistent gets0 physical reads0 redo size1780 bytes sent via SQL*Net to client545 bytes received via SQL*Net from client4 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)41 rows processed第四种情况:用不带分区键值的索引进行查询，但是where条件中加分区条件
set autotrace traceonlySELECT object_name FROM parttest a WHERE created=to_date('2014-12-15 22:29:22','YYYY-MM-DD HH24:MI:SS') AND owner='SYS';
Execution Plan----------------------------------------------------------Plan hash value: 3242664717
--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 28 | 868 | 2 (0)| 00:00:01 | | || 1 | PARTITION LIST SINGLE | | 28 | 868 | 2 (0)| 00:00:01 | KEY | KEY || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTTEST | 28 | 868 | 2 (0)| 00:00:01 | 1 | 1 ||* 3 | INDEX RANGE SCAN | IDX_NOPARTKEY | 28 | | 1 (0)| 00:00:01 | 1 | 1 |--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd hh34:mi:ss'))
Statistics----------------------------------------------------------0 recursive calls0 db block gets7 consistent gets0 physical reads0 redo size1191 bytes sent via SQL*Net to client534 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)21 rows processed
第五种情况：用带分区键值的索引进行查询，但在where条件中加分区条(与4的索引键相同，只是带索引键值)
SELECT object_name FROM parttest a WHERE created=to_date('2014-12-15 22:29:22','YYYY-MM-DD HH24:MI:SS') AND owner='SYS'; Execution Plan----------------------------------------------------------Plan hash value: 1150146376
------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 28 | 868 | 2 (0)| 00:00:01 | | || 1 | PARTITION LIST SINGLE | | 28 | 868 | 2 (0)| 00:00:01 | KEY | KEY || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTTEST | 28 | 868 | 2 (0)| 00:00:01 | 1 | 1 ||* 3 | INDEX RANGE SCAN | IDX_PARTKEY | 17 | | 1 (0)| 00:00:01 | 1 | 1 |------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd hh34:mi:ss') AND "OWNER"='SYS')
Statistics----------------------------------------------------------0 recursive calls0 db block gets7 consistent gets0 physical reads0 redo size1191 bytes sent via SQL*Net to client534 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)21 rows processed第六种情况：用带分区键值的索引进行查询，但在where条件中加分区条(与4的索引键相同，只是带前导索引键值)set autotrace traceonlySELECT object_name FROM parttest a WHERE created=to_date('2014-12-15 22:29:22','YYYY-MM-DD HH24:MI:SS') AND owner='SYS';
Execution Plan----------------------------------------------------------Plan hash value: 1150146376
------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 28 | 868 | 2 (0)| 00:00:01 | | || 1 | PARTITION LIST SINGLE | | 28 | 868 | 2 (0)| 00:00:01 | KEY | KEY || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTTEST | 28 | 868 | 2 (0)| 00:00:01 | 1 | 1 ||* 3 | INDEX RANGE SCAN | IDX_PARTKEY | 17 | | 1 (0)| 00:00:01 | 1 | 1 |------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd hh34:mi:ss') AND "OWNER"='SYS')
Statistics----------------------------------------------------------0 recursive calls0 db block gets7 consistent gets0 physical reads0 redo size1191 bytes sent via SQL*Net to client534 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)21 rows processed
第七种情况：用带分区键值的索引进行跨分区查询，但在where条件中加分区条件(与4的索引键相同，只是带索引前导键值)set autotrace traceonlySELECT object_name FROM parttest a WHERE object_name LIKE 'OR%' AND owner IN ('SYS','SUN');Execution Plan----------------------------------------------------------Plan hash value: 1341146800
---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 | | || 1 | INLIST ITERATOR | | | | | | | || 2 | PARTITION LIST ITERATOR| | 1 | 25 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) ||* 3 | INDEX RANGE SCAN | IDX_PARTKEY3 | 1 | 25 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) |---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access(("OWNER"='SUN' OR "OWNER"='SYS') AND "OBJECT_NAME" LIKE 'OR%')filter("OBJECT_NAME" LIKE 'OR%')
Statistics----------------------------------------------------------1 recursive calls0 db block gets8 consistent gets1 physical reads0 redo size2540 bytes sent via SQL*Net to client567 bytes received via SQL*Net from client6 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)62 rows processed
第八种情况：用带分区键值的索引进行跨分区查询，但在where条件中加分区条(与4的索引键相同，只是带后导索引键值)set autotrace traceonlySELECT object_name FROM parttest a WHERE object_name LIKE 'OR%' AND owner IN ('SYS','SUN');
Execution Plan----------------------------------------------------------Plan hash value: 2095150599
------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 | | || 1 | PARTITION LIST INLIST| | 1 | 25 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) ||* 2 | INDEX RANGE SCAN | IDX_PARTKEY2 | 1 | 25 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) |------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("OBJECT_NAME" LIKE 'OR%')filter("OBJECT_NAME" LIKE 'OR%')
Statistics----------------------------------------------------------209 recursive calls2 db block gets180 consistent gets0 physical reads0 redo size2497 bytes sent via SQL*Net to client567 bytes received via SQL*Net from client6 SQL*Net roundtrips to/from client13 sorts (memory)0 sorts (disk)62 rows processed
第九种情况：用带分区键值的索引进行跨分区查询，但在where条件中加分区条件(与4的索引键相同，只是带索引键值)
set autotrace traceonlySELECT object_name FROM parttest a WHERE object_name LIKE 'OR%' AND owner IN ('SYS','SUN');
Execution Plan----------------------------------------------------------Plan hash value: 2097624711
---------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 5 (0)| 00:00:01 | | || 1 | PARTITION LIST INLIST | | 1 | 25 | 5 (0)| 00:00:01 |KEY(I) |KEY(I) || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTTEST | 1 | 25 | 5 (0)| 00:00:01 |KEY(I) |KEY(I) ||* 3 | INDEX RANGE SCAN | IDX_NOPARTKEY2 | 3 | | 3 (0)| 00:00:01 |KEY(I) |KEY(I) |---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("OBJECT_NAME" LIKE 'OR%')filter("OBJECT_NAME" LIKE 'OR%')
Statistics----------------------------------------------------------1 recursive calls0 db block gets27 consistent gets1 physical reads0 redo size2497 bytes sent via SQL*Net to client567 bytes received via SQL*Net from client6 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)62 rows processed
总结：
1.在使用分区表示，WHERE 条件最好带上分区键，要不然就失去了分区的意义，一个分区在物理上是一个表，全分区表扫描比全非分区表扫描要更多的IO读。2.WHERE 条件带分区的情况下，单分区带不带分区键好像意义不大， 跨分区扫描的情况下，带前导分区键的索引效率高。综合所述，如果需要创建组合索引，建议创建带前导分区键的分区索引。
3. 测试在非分区表上创建全局分区索引与普通索引区别，看着意义不大，使用场景未明。
CREATE TABLE gpart AS select owner,object_id,object_name,created from DBA_OBJECTS;SELECT distinct TO_char(created,'YYYY-MM-DD') FROM gpart;
exec dbms_stats.gather_table_stats('SUN','GPART',cascade=>true,no_invalidate=>false,method_opt=>'for all columns size 1',estimate_percent=>dbms_stats.auto_sample_size,degree=>24) ;
create index idx_gpart1 ON gpart(created) nologging;DROP INDEX idx_gpart1;
set autotrace traceonlySELECT * FROM gpart t WHERE created > TO_DATE('2015-04-02','YYYY-MM-DD') ;
Execution Plan----------------------------------------------------------Plan hash value: 4136711861
------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1005 | 36180 | 13 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| GPART | 1005 | 36180 | 13 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_GPART1 | 1005 | | 4 (0)| 00:00:01 |------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("CREATED">TO_DATE(' 2015-04-02 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
Statistics----------------------------------------------------------1 recursive calls0 db block gets34 consistent gets0 physical reads0 redo size9616 bytes sent via SQL*Net to client644 bytes received via SQL*Net from client13 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)174 rows processedcreate index idx_gpart2on gpart(created)global partition by range (created)(partition GLOBAL1 values less than (TO_DATE('2014-12-15','YYYY-MM-DD')),partition GLOBAL2 values less than (TO_DATE('2015-03-11','YYYY-MM-DD')),partition GLOBAL3 values less than (TO_DATE('2015-03-24','YYYY-MM-DD')),partition GLOBAL4 values less than (TO_DATE('2015-04-01','YYYY-MM-DD')),partition GLOBAL5 values less than (MAXVALUE)) nologging;DROP INDEX idx_gpart2;
set autotrace traceonlySELECT * FROM gpart t WHERE created > TO_DATE('2015-04-02','YYYY-MM-DD') ;
Execution Plan----------------------------------------------------------Plan hash value: 4217733073
-----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1005 | 36180 | 13 (0)| 00:00:01 | | || 1 | PARTITION RANGE SINGLE | | 1005 | 36180 | 13 (0)| 00:00:01 | 5 | 5 || 2 | TABLE ACCESS BY INDEX ROWID| GPART | 1005 | 36180 | 13 (0)| 00:00:01 | | ||* 3 | INDEX RANGE SCAN | IDX_GPART2 | 1005 | | 4 (0)| 00:00:01 | 5 | 5 |-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("CREATED">TO_DATE(' 2015-04-02 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
Statistics----------------------------------------------------------1 recursive calls0 db block gets34 consistent gets0 physical reads0 redo size5769 bytes sent via SQL*Net to client644 bytes received via SQL*Net from client13 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)174 rows processed
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
