Oracle partition表分区与分区索引
介绍：对于10gR2 而言，基本上可以分成几类：
Range(范围)分区
Hash(哈希)分区
List(列表)分区
以及组合分区：Range-Hash,Range-List。
准备环境：
--1、建三个表空间
SQL> create tablespace par01 datafile 'e:\oracle\test\par01.dbf'>
SQL> create tablespace par02 datafile 'e:\oracle\test\par02.dbf'>
SQL> create tablespace par03 datafile 'e:\oracle\test\par03.dbf'> --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),
AMENDBZNUMBER(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> 或者自定义
不过呢分区名称，以及分区所在表空间等信息是可以自定义的，例如：
SQL> create index> 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> 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 bypartition_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_01tablespace par01,
partition par_02tablespace par02,
partition par_03tablespace 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> 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> 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行。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]