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, high_value, 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, partitioning_type, 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, high_value, 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 |
------------------------------------------------------------------------------------------------------
上一篇： python爬取“堆糖网”小姐姐图片