Oracle 分区索引介绍和实例演示

前端之家收集整理的这篇文章主要介绍了Oracle 分区索引介绍和实例演示前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长,普通的堆表需要转换到分区表,其索引呢,则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断,在获取所需数据时,只需要访问更小的索引片断(块)即可实现。同时把分区放在不同的表空间可以提高分区的可用性和可靠性。本文主要描述了分区索引的相关特性并给出演示示例。

1、分区索引的相关概念

a、分区索引的几种方式:表被分区而索引未被分区;表未被分区,而索引被分区;表和索引都被分区
b、分区索引可以分为本地分区索引以及全局分区索引

本地分区索引:

本地分区索引信息的存放依赖于父表分区。也就是说对于本地索引一定是基于分区表创建的。
缺省情况下,创建本地索引时,如未指定索引存放表空间,会自动将本地索引存放到数据所在分区定义时的表空间。
本地索引的分区机制和表的分区机制一样,本地索引可以是是B树索引或位图索引。
本地索引是对单个分区的,每个分区索引只指向一个表分区,为对等分区。
本地索引支持分区独立性,因此对于这些单独的分区增加截取删除,分割,脱机等处理无需同时删除或重建。
本地索引多应用于数据仓库环境中。

全局分区索引:

全局分区索引时分区表和全局索引的分区机制不一样,在创建时必须定义分区键的范围和值。
全局分区索引在创建时应指定Global关键字且全局分区索引只能是B树索引。
全局索引可以分区,也可以是不分区索引,全局索引必须是前缀索引,即索引列必须包含分区键。
全局索引分区中,一个分区索引能指向n个表分区,同时,一个表分区,也可能指向n个索引分区。
默认情况下全局索引对于分区增加截取删除,分割等都必须重建或修改时指定update global indexs。
全局分区索引只按范围或者散列hash分区。
全局分区索引多应用于oltp系统中。

c、有前缀索引和无前缀索引

本地和全局分区索引又分为两个子类型即有前缀索引和无前缀索引。
前缀和非前缀索引都可以支持索引分区消除,前提是查询的条件中包含索引分区键。
有前缀索引:
有前缀索引包含了分区键,即分区键列被包含在索引中。
有前缀索引支持本地分区索引以及全局分区索引。
无前缀索引:
无前缀索引即没有把分区键的前导列作为索引的前导列。
无前缀索引仅仅支持本地分区索引。

2、本地分区索引演示

代码如下:
--环境
sql> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

sql> create user leshami identified by xxx;

sql> grant dba to leshami;

--创建演示需要用到的表空间
sql> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10m autoextend on;

sql> alter user leshami default tablespace tbs_tmp;

sql> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10m autoextend on;

sql> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10m autoextend on;

sql> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10m autoextend on;

sql> create tablespace idx1 datafile '/u02/database/SYBO2/oradata/idx1.dbf' size 10m autoextend on;

sql> create tablespace idx2 datafile '/u02/database/SYBO2/oradata/idx2.dbf' size 10m autoextend on;

sql> create tablespace idx3 datafile '/u02/database/SYBO2/oradata/idx3.dbf' size 10m autoextend on;

sql> conn leshami/xxx

-- 创建一个lookup表
CREATE TABLE lookup (
id NUMBER(10),
description VARCHAR2(50)
);

--添加主键约束
ALTER TABLE lookup ADD (
CONSTRAINT lookup_pk PRIMARY KEY (id)
);

--插入数据
INSERT INTO lookup (id,description) VALUES (1,'ONE');
INSERT INTO lookup (id,description) VALUES (2,'TWO');
INSERT INTO lookup (id,description) VALUES (3,'THREE');
COMMIT;

CREATE TABLE big_table (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2012 VALUES LESS THAN (TO_DATE('01/01/2013','DD/MM/YYYY')) tablespace tbs1,
PARTITION big_table_2013 VALUES LESS THAN (TO_DATE('01/01/2014','DD/MM/YYYY')) tablespace tbs2,
PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE)tablespace tbs3 ) ;

--填充数据到分区表
DECLARE
l_lookup_id lookup.id%TYPE;
l_create_date DATE;
BEGIN
FOR i IN 1 .. 10000 LOOP
IF MOD(i,3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE,-24);
l_lookup_id := 2;
ELSIF MOD(i,2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE,-12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;

INSERT INTO big_table (id,created_date,lookup_id,data)
VALUES (i,l_create_date,l_lookup_id,'This is some data for ' || i);
END LOOP;
COMMIT;
END;
/

--未指定索引分区及存储表空间情形下创建索引
sql> CREATE INDEX bita_created_date_i ON big_table(created_date) LOCAL;

Index created.

sql> select index_name,partitioning_type,partition_count from user_part_indexes;

INDEX_NAME PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
BITA_CREATED_DATE_I RANGE 3

--Author : Leshami

--从下面的查询可知,索引直接存放到分表表对应的表空间
sql> select partition_name,high_value,tablespace_name from user_ind_partitions;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
BIG_TABLE_2014 MAXVALUE TBS3
BIG_TABLE_2013 TO_DATE(' 2014-01-01 00:00:00','SYYYY-M TBS2
M-DD HH24:MI:SS','NLS_CALENDAR=GREGORIA

BIG_TABLE_2012 TO_DATE(' 2013-01-01 00:00:00','SYYYY-M TBS1
M-DD HH24:MI:SS','NLS_CALENDAR=GREGORIA

--删除索引
sql> drop index bita_created_date_i;

--指定索引分区名表空间名创建索引
sql> CREATE INDEX bita_created_date_i
2 ON big_table (created_date)
3 LOCAL (
4 PARTITION idx_2012 TABLESPACE idx1,
5 PARTITION idx_2013 TABLESPACE idx2,
6 PARTITION idx_2014 TABLESPACE idx3)
7 PARALLEL 3;

Index created.

sql> select partition_name,tablespace_name from user_ind_partitions;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
IDX_2014 MAXVALUE IDX3
IDX_2013 TO_DATE(' 2014-01-01 00:00:00','SYYYY-M IDX2
M-DD HH24:MI:SS','NLS_CALENDAR=GREGORIA

IDX_2012 TO_DATE(' 2013-01-01 00:00:00','SYYYY-M IDX1
M-DD HH24:MI:SS','NLS_CALENDAR=GREGORIA

sql> select * from big_table where rownum<2;

ID CREATED_ LOOKUP_ID DATA
---------- -------- ---------- --------------------------------------------------
1413 20120625 2 This is some data for 1413

--查看local index是否被使用,从下面的执行计划中可知,索引被使用,支持分区消除
sql> set autot trace exp;
sql> select * from big_table where created_date=to_date('20120625','yyyymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 2556877094

--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| BIG_TABLE | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | BITA_CREATED_DATE_I | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------------

3、全局分区索引演示

代码如下:
--为表添加主键
sql> ALTER TABLE big_table ADD (
2 CONSTRAINT big_table_pk PRIMARY KEY (id)
3 );

Table altered.

sql> select index_name,index_type,tablespace_name,global_stats,partitioned
2 from user_indexes where index_name='BIG_TABLE_PK';

INDEX_NAME INDEX_TYPE TABLESPACE_NAME GLO PAR
------------------------------ --------------------------- ------------------------------ --- ---
BIG_TABLE_PK NORMAL TBS_TMP YES NO

sql> set autot trace exp;
sql> select * from big_table where id=1412;

Execution Plan
----------------------------------------------------------
Plan hash value: 2662411593

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 2 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE | 1 | 62 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | BIG_TABLE_PK | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------
--如上,在其执行计划中,Pstart与Pstop都为ROWID
--出现了GLOBAL INDEX ROWID,我们添加主键时并未指定Global,但其执行计划表明执行了全局索引访问
--这个地方有待证实,对于分区表,非分区键上的主键或唯一索引是否一定是全局索引

sql> drop index bita_created_date_i;

--下面创建全局索引,创建时需要指定分区键的范围和值
sql> CREATE INDEX bita_created_date_i
ON big_table (created_date)
GLOBAL PARTITION BY RANGE (created_date)
(
PARTITION
idx_1 VALUES LESS THAN (TO_DATE ('01/01/2013','DD/MM/YYYY'))
TABLESPACE idx1,
PARTITION
idx_2 VALUES LESS THAN (TO_DATE ('01/01/2014','DD/MM/YYYY'))
TABLESPACE idx2,
PARTITION idx_3 VALUES LESS THAN (maxvalue) TABLESPACE idx3);

sql> select index_name,partition_count,locality from user_part_indexes;

INDEX_NAME PARTITI PARTITION_COUNT LOCALI
------------------------------ ------- --------------- ------
BITA_CREATED_DATE_I_G RANGE 3 GLOBAL

sql> select partition_name,tablespace_name from user_ind_partitions;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ --------------------- ------------------------------
IDX_1 TO_DATE(' 2013-01-01 IDX1
IDX_2 TO_DATE(' 2014-01-01 IDX2
IDX_3 MAXVALUE IDX3

--下面是其执行计划,可以看出支持分区消除
sql> set autot trace exp;
sql> select * from big_table where created_date=to_date('20130625','yyyymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 1378264218

---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 2 | 2 |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE | 1 | 41 | 2 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | BITA_CREATED_DATE_I | 1 | | 1 (0)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------------------------------

--以下为范围查询,Pstart为1,Pstop为2,同样支持分区消除
sql> select * from big_table
2 where created_date>=to_date('20120625','yyyymmdd') and created_date<=to_date('20130625','yyyymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 213633793

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3334 | 133K| 14 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 3334 | 133K| 14 (0)| 00:00:01 | 1 | 2 |
|* 2 | TABLE ACCESS FULL | BIG_TABLE | 3334 | 133K| 14 (0)| 00:00:01 | 1 | 2 |
------------------------------------------------------------------------------------------------------

原文链接:https://www.f2er.com/oracle/65242.html

猜你在找的Oracle相关文章