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

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 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

文章名称：oracle_分区表的索引类型以及是否带分区键索引的区别
当前链接：http://pwwzsj.com/article/jpogpo.html

平武建站

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

其他资讯