Oracle partition表分区与分区索引

shaoqin

　　介绍：
　　对于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),
　　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>　　或者自定义
　　不过呢分区名称，以及分区所在表空间等信息是可以自定义的，例如：

　　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 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>　　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