【Oracle 表分区管理】Oracle partition表分区与分区索引几种方式测试过程

介绍：
对于10gR2 而言，基本上可以分成几类：
Range(范围)分区
Hash(哈希)分区
List(列表)分区
以及组合分区：Range-Hash,Range-List。

准备环境：
--1、建三个表空间
SQL> create tablespace par01 datafile 'e:\oracle\test\par01.dbf' size 10m ;
SQL> create tablespace par02 datafile 'e:\oracle\test\par02.dbf' size 10m ;
SQL> create tablespace par03 datafile 'e:\oracle\test\par03.dbf' size 10m ;

--2、并加上权限
alter user fmismain quota unlimited on par01;
alter user fmismain quota unlimited on par02;
alter user fmismain quota unlimited on par03;

--3、创建一张分区表于三个表空间中（rang分区）：
create table P_TABLE_PAR
(
GID NUMBER(10) not null,
IID NUMBER(10),
FLID NUMBER(10),
PZXMNAME VARCHAR2(20),
DYLX NUMBER(10),
DYXM VARCHAR2(100),
AMENDBZ NUMBER(10) default 0
)
partition by range(GID)
(
partition par_01 values less than(50000) tablespace par01,
partition par_02 values less than(100000) tablespace par02,
partition par_03 values less than(maxvalue) tablespace par03
);
---或者直接创建有数据的表分区
create table P_TABLE_PAR(GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)
partition by range(GID)
(
partition par_01 values less than(50000) tablespace par01,
partition par_02 values less than(100000) tablespace par02,
partition par_03 values less than(maxvalue) tablespace par03
)
as
Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2;

----创建一个local索引rang分区

create index idx_local_p_gid on p_table_par(GID) local;
或者自定义
不过呢分区名称，以及分区所在表空间等信息是可以自定义的，例如：
SQL> create index IDX_PART_RANGE_ID ON T_PARTITION_RANGE(id) local (
2 partition i_range_p1 tablespace tbspart01,
3 partition i_range_p2 tablespace tbspart01,
4 partition i_range_p3 tablespace tbspart02,
5 partition i_range_pmax tablespace tbspart02
6 );
----创建一个global索引rang分区
create index idx_p_global_gid on P_TABLE_PAR(gid)
global partition by range(gid)(
partition i_range_par_01 values less than (50000) tablespace par01,
partition i_range_par_02 values less than (100000) tablespace par02,
partition i_range_par_03 values less than (maxvalue) tablespace par03
);

--4、查询

select table_name,partitioning_type,partition_count From user_part_tables;
select partition_name,high_value,tablespace_name from user_tab_partitions order by partition_position;
select index_name, partitioning_type, partition_count from user_part_indexes
user_part_tables：记录分区的表的信息;
user_tab_partitions：记录表的分区的信息
user_part_indexes：查询用户索引信息。

--5、hash分区
create table P_TABLE_PAR(GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)
partition by hash(GID)
(
partition par_01 tablespace par01,
partition par_02 tablespace par02,
partition par_03 tablespace par03
)
as
Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2;

或者用下面相同的语句实现相同的效果
create table P_TABLE_PAR(GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)
partition by hash(gid)
partitions 3 store in(par01,par02,par03)
as
Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2;
--创建一个global索引hash分区
create index idx_part_hash_gid on p_table_par(gid)
global partition by hash(gid)
partitions 3 store in(par01,par02,par03);
----创建一个local索引rang分区
与range相同

--5、list分区
create table P_TABLE_PAR(GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)
partition by list(GID)
(
partition par_01 values (1,2) tablespace par01,
partition par_02 values (3,4) tablespace par02,
partition par_03 values(default) tablespace par03
)
as
Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2;

--6、创建range-hash组合分区
--为所有分区各创建3个hash子分区
create table P_TABLE_PAR(GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)
partition by range(GID) subpartition by hash(PZXMNAME)
subpartitions 3 store in(par01,par02,par03)
(
partition par_01 values less than(50000) tablespace par01,
partition par_02 values less than(100000) tablespace par02,
partition par_03 values less than(maxvalue) tablespace par03
)
as
Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2;

---查询：
此处学到的一个查询子分区的字典表：user_tab_subpartitions
select partitioning_type,subpartitioning_type,partition_count,def_subpartition_count From user_part_tables where table_name='P_TABLE_PAR';
结果如下：
PARTITIONING_TYPE SUBPARTITIONING_TYPE PARTITION_COUNT DEF_SUBPARTITION_COUNT
1 RANGE HASH 3 3
select partition_name,subpartition_count,high_value from user_tab_partitions where table_name='P_TABLE_PAR';
结果如下：
PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE
1 PAR_01 3 50000
2 PAR_02 3 100000
3 PAR_03 3 MAXVALUE
select partition_name,subpartition_name,tablespace_name from user_tab_subpartitions where table_name='P_TABLE_PAR';
结果如下：
PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
1 PAR_01 SYS_SUBP32 PAR01
2 PAR_01 SYS_SUBP31 PAR01
3 PAR_01 SYS_SUBP30 PAR01
4 PAR_02 SYS_SUBP35 PAR02
5 PAR_02 SYS_SUBP34 PAR02
6 PAR_02 SYS_SUBP33 PAR02
7 PAR_03 SYS_SUBP38 PAR03
8 PAR_03 SYS_SUBP37 PAR03
9 PAR_03 SYS_SUBP36 PAR03

--跨分区查询
select sum(*) from
(select count(*) from P_TABLE_PAR PARTITION (PAR_01)
union all
select count(*) from P_TABLE_PAR PARTITION (PAR_02)
);

--子分区数据查询
SQL> select count(*) from p_table_par subpartition(par_01_h1);
COUNT(*)
----------
11876

---对某个分区创建hash子分区
create table P_TABLE_PAR(GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)
partition by range(GID) subpartition by hash(PZXMNAME)
(
partition par_01 values less than(50000) tablespace par01,
partition par_02 values less than(100000) tablespace par02,
partition par_03 values less than(maxvalue) tablespace par03
(subpartition par_03_h1 tablespace par01,
subpartition par_03_h2 tablespace par02,
subpartition par_03_h3 tablespace par03)
)
as
Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2;

--给各个分区指定不同的子分区
create table P_TABLE_PAR(GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)
partition by range(GID) subpartition by hash(PZXMNAME)
(
partition par_01 values less than(50000) tablespace par01
(subpartition par_01_h1 tablespace par01,
subpartition par_01_h2 tablespace par02,
subpartition par_01_h3 tablespace par03),
partition par_02 values less than(100000) tablespace par02
(subpartition par_02_h1 tablespace par01,
subpartition par_02_h2 tablespace par02,
subpartition par_02_h3 tablespace par03),
partition par_03 values less than(maxvalue) tablespace par03
(subpartition par_03_h1 tablespace par01,
subpartition par_03_h2 tablespace par02,
subpartition par_03_h3 tablespace par03)
)
as
Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2;

---分区模板的应用(template)
oracle 还提供了一种称为分区模板的功能，在指定子分区信赖列之后，制订子分区的存储模板，各个
分区即会按照子分区模式创建子分区
create table P_TABLE_PAR(GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)
partition by range(GID) subpartition by hash(PZXMNAME)
subpartition template
(subpartition h1 tablespace par01,
subpartition h2 tablespace par02,
subpartition h3 tablespace par03)
(
partition par_01 values less than(50000) tablespace par01,
partition par_02 values less than(100000) tablespace par02,
partition par_03 values less than(maxvalue) tablespace par03
)
as
Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2;

---7、创建range-list组合分区
--创建分区为range，子分区为list
create table P_TABLE_PAR(GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)
partition by range(GID) subpartition by list(flid)
subpartition template
(subpartition l1 values('1') tablespace par01,
subpartition l2 values('2') tablespace par02,
subpartition l3 values(default) tablespace par03)
(
partition par_01 values less than(50000) tablespace par01,
partition par_02 values less than(100000) tablespace par02,
partition par_03 values less than(maxvalue) tablespace par03
)
as
Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2;
或者：
create table P_TABLE_PAR(GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)
partition by range(GID) subpartition by list(flid)
subpartition template
(subpartition l1 values('1') tablespace par01,
subpartition l2 values('2') tablespace par02,
subpartition l3 values('3') tablespace par03)
(
partition par_01 values less than(50000) tablespace par01,
partition par_02 values less than(100000) tablespace par02,
partition par_03 values less than(maxvalue) tablespace par03
)
as
Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2;

---８、关于复合分区索引的创建与查询
create index idx_p_table_gid on p_table_par(gid) local;
select table_name,partitioning_type,partition_count,def_subpartition_count from user_part_indexes　where index_name = 'IDX_P_TABLE_GID';
select partition_name, subpartition_count, high_value　from user_ind_partitions where index_name = 'IDX_P_TABLE_GID';
select partition_name, subpartition_name, high_value, tablespace_name from user_ind_subpartitions where index_name = 'IDX_P_TABLE_GID';
结果如下：
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ------------------------------ ---------- ------------------------------
PAR_01 PAR_01_L1 '1' PAR01
PAR_01 PAR_01_L2 '2' PAR01
PAR_01 PAR_01_L3 default PAR01
PAR_02 PAR_02_L1 '1' PAR02
PAR_02 PAR_02_L2 '2' PAR02
PAR_02 PAR_02_L3 default PAR02
PAR_03 PAR_03_L1 '1' PAR03
PAR_03 PAR_03_L2 '2' PAR03
PAR_03 PAR_03_L3 default PAR03
已选择9行。