oracle_分区表的索引类型以及是否带分区键索引的区别

千家信息网最后更新 2024年10月27日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 traceonly SELECT 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 calls 0 db block gets 23 consistent gets 0 physical reads 0 redo size 3768 bytes sent via SQL*Net to client 589 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 105 rows processed

第二种情况:用带分区键值的索引进行查询，但是where条件中加分区条件 set autotrace traceonly SELECT 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 calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 2279 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 58 rows processed 第三种情况:用不带分区键值的索引进行查询，但是where条件中不加分区条件
set autotrace traceonly SELECT 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 calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 1780 bytes sent via SQL*Net to client 545 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 41 rows processed 第四种情况:用不带分区键值的索引进行查询，但是where条件中加分区条件
set autotrace traceonly 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: 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 calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1191 bytes sent via SQL*Net to client 534 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 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 calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1191 bytes sent via SQL*Net to client 534 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 21 rows processed 第六种情况：用带分区键值的索引进行查询，但在where条件中加分区条(与4的索引键相同，只是带前导索引键值) set autotrace traceonly 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 calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1191 bytes sent via SQL*Net to client 534 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 21 rows processed
第七种情况：用带分区键值的索引进行跨分区查询，但在where条件中加分区条件(与4的索引键相同，只是带索引前导键值) set autotrace traceonly SELECT 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 calls 0 db block gets 8 consistent gets 1 physical reads 0 redo size 2540 bytes sent via SQL*Net to client 567 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 62 rows processed

第八种情况：用带分区键值的索引进行跨分区查询，但在where条件中加分区条(与4的索引键相同，只是带后导索引键值) set autotrace traceonly SELECT 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 calls 2 db block gets 180 consistent gets 0 physical reads 0 redo size 2497 bytes sent via SQL*Net to client 567 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 13 sorts (memory) 0 sorts (disk) 62 rows processed
第九种情况：用带分区键值的索引进行跨分区查询，但在where条件中加分区条件(与4的索引键相同，只是带索引键值)
set autotrace traceonly SELECT 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 calls 0 db block gets 27 consistent gets 1 physical reads 0 redo size 2497 bytes sent via SQL*Net to client 567 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 0 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 traceonly SELECT * 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 calls 0 db block gets 34 consistent gets 0 physical reads 0 redo size 9616 bytes sent via SQL*Net to client 644 bytes received via SQL*Net from client 13 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 174 rows processed create index idx_gpart2 on 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 traceonly SELECT * 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 calls 0 db block gets 34 consistent gets 0 physical reads 0 redo size 5769 bytes sent via SQL*Net to client 644 bytes received via SQL*Net from client 13 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 174 rows processed