oracle_分區(qū)表的索引類型以及是否帶分區(qū)鍵索引的區(qū)別

One. 介紹一下分區(qū)表的索引類型，以及簡(jiǎn)述各個(gè)類型的適用場(chǎng)景。

Two. 驗(yàn)證一下組合分區(qū)索引帶不帶分區(qū)鍵的區(qū)別，用數(shù)據(jù)來說話。

以下說明都是針對(duì)分區(qū)表的索引介紹。（想著物理存儲(chǔ)屬性更能了解下面索引的說明）

1. 本地索引和全局索引

本地索引：索引分區(qū)鍵值等于表的分區(qū)鍵值

本地前綴：在索引定義中，表的分區(qū)鍵是索引的前導(dǎo)列。

本地非前綴：在索引定義中，表的分區(qū)鍵不是索引的前導(dǎo)列。

全局分區(qū)索引：分區(qū)索引不是本地的。全局分區(qū)索引也可以用于非分區(qū)表上。

全局非分區(qū)索引：索引不是分區(qū)的。

2. 驗(yàn)證帶分區(qū)鍵本地分區(qū)索引的區(qū)別。

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;

--索引不包含分區(qū)鍵

create index idx_nopartkey on parttest(created) local nologging;

-- 索引包含分區(qū)鍵

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;

--收集統(tǒng)計(jì)信息

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.

分析過程分如下幾個(gè)方面

1.用帶分區(qū)鍵值的索引進(jìn)行查詢，但在where條件中不加分區(qū)條件

2.用帶分區(qū)鍵值的索引進(jìn)行查詢，但在where條件中加分區(qū)條件

3.用不帶分區(qū)鍵值的索引進(jìn)行查詢，但在where條件中不加分區(qū)條件

4.用不帶分區(qū)鍵值的索引進(jìn)行查詢，但在where條件中加分區(qū)條

5.用帶分區(qū)鍵值的索引進(jìn)行查詢，但在where條件中加分區(qū)條(與4的索引鍵相同，只是帶索引鍵值)

6.用帶分區(qū)鍵值的索引進(jìn)行查詢，但在where條件中加分區(qū)條(與4的索引鍵相同，只是帶前導(dǎo)索引鍵值)

7.用帶分區(qū)鍵值的索引進(jìn)行跨分區(qū)查詢，但在where條件中加分區(qū)條件(與4的索引鍵相同，只是帶索引前導(dǎo)鍵值)

8.用帶分區(qū)鍵值的索引進(jìn)行跨分區(qū)查詢，但在where條件中加分區(qū)條(與4的索引鍵相同，只是帶后導(dǎo)索引鍵值)

9.用帶分區(qū)鍵值的索引進(jìn)行跨分區(qū)查詢，但在where條件中加分區(qū)條件(與4的索引鍵相同，只是帶索引鍵值)

第一種情況:用帶分區(qū)鍵值的索引進(jìn)行查詢，但是where條件中不加分區(qū)條件

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

第二種情況:用帶分區(qū)鍵值的索引進(jìn)行查詢，但是where條件中加分區(qū)條件

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

第三種情況:用不帶分區(qū)鍵值的索引進(jìn)行查詢，但是where條件中不加分區(qū)條件

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

第四種情況:用不帶分區(qū)鍵值的索引進(jìn)行查詢，但是where條件中加分區(qū)條件

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

第五種情況：用帶分區(qū)鍵值的索引進(jìn)行查詢，但在where條件中加分區(qū)條(與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

第六種情況：用帶分區(qū)鍵值的索引進(jìn)行查詢，但在where條件中加分區(qū)條(與4的索引鍵相同，只是帶前導(dǎo)索引鍵值)

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

第七種情況：用帶分區(qū)鍵值的索引進(jìn)行跨分區(qū)查詢，但在where條件中加分區(qū)條件(與4的索引鍵相同，只是帶索引前導(dǎo)鍵值)

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

第八種情況：用帶分區(qū)鍵值的索引進(jìn)行跨分區(qū)查詢，但在where條件中加分區(qū)條(與4的索引鍵相同，只是帶后導(dǎo)索引鍵值)

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

第九種情況：用帶分區(qū)鍵值的索引進(jìn)行跨分區(qū)查詢，但在where條件中加分區(qū)條件(與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

總結(jié)：

1.在使用分區(qū)表示，WHERE 條件最好帶上分區(qū)鍵，要不然就失去了分區(qū)的意義，一個(gè)分區(qū)在物理上是一個(gè)表，

全分區(qū)表掃描比全非分區(qū)表掃描要更多的IO讀。

2.WHERE 條件帶分區(qū)的情況下，單分區(qū)帶不帶分區(qū)鍵好像意義不大，跨分區(qū)掃描的情況下，帶前導(dǎo)分區(qū)鍵的索引效率高。

綜合所述，如果需要?jiǎng)?chuàng)建組合索引，建議創(chuàng)建帶前導(dǎo)分區(qū)鍵的分區(qū)索引。

3. 測(cè)試在非分區(qū)表上創(chuàng)建全局分區(qū)索引與普通索引區(qū)別，看著意義不大，使用場(chǎng)景未明。

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

本文標(biāo)題：oracle_分區(qū)表的索引類型以及是否帶分區(qū)鍵索引的區(qū)別
瀏覽路徑：http://weahome.cn/article/jpogpo.html

真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

oracle_分區(qū)表的索引類型以及是否帶分區(qū)鍵索引的區(qū)別

其他資訊

網(wǎng)站制作

企業(yè)服務(wù)

網(wǎng)站建設(shè)

服務(wù)器托管