《2022年Oraclepartition表分区与分区索引几种方式的实验操作 .pdf》由会员分享，可在线阅读，更多相关《2022年Oraclepartition表分区与分区索引几种方式的实验操作 .pdf（8页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Oracle partition表分区与分区索引几种方式的实验操作原创介绍：对于 10gR2 而言，基本上可以分成几类：Range(范围) 分区Hash(哈希) 分区List(列表) 分区以及组合分区： Range-Hash,Range-List 。准备环境：-1 、建三个表空间SQL create tablespace par01 datafile e:oracletestpar01.dbf size 10m ; SQL create tablespace par02 datafile e:oracletestpar02.dbf size 10m ; SQL create tablespac
2、e par03 datafile e:oracletestpar03.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 NU
3、MBER(10), PZXMNAME VARCHAR2(20), DYLX NUMBER(10), DYXM VARCHAR2(100), AMENDBZ NUMBER(10) default 0 ) partition by range(GID) ( 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 8 页 - - - - - - - - - partition par_01 values less than(50000) tablespace par01, partit
4、ion 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
5、 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 ind
6、ex 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_TAB
7、LE_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,part
8、ition_count From user_part_tables; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 8 页 - - - - - - - - - select partition_name,high_value,tablespace_name from user_tab_partitions order by partition_position; select index_name, partitioning_type, partition_count
9、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 tab
10、lespace 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; - 创建一
11、个 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
12、,2) tablespace par01, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 8 页 - - - - - - - - - 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 、
13、创建 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(
14、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_subpartitionsselect partitioning_type,subpartitioning_type,partition_count,def_subpartition_count From user_part_t
15、ables 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
16、_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 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4
17、 页，共 8 页 - - - - - - - - - 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
18、(*) 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)
19、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, subp
20、artition 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) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师
21、精心整理 - - - - - - - 第 5 页，共 8 页 - - - - - - - - - ( 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 (subpar
22、tition 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 par0
23、3) ) 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 (su
24、bpartition 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,II
25、D,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from P_TABLE_2; -7 、创建 range-list组合分区- 创建分区为 range，子分区为 listcreate 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, 名师资料总结 - - -精品资料欢迎下载 -
26、 - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 8 页 - - - - - - - - - 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,
27、 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) t
28、ablespace 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 G
29、ID,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_co
30、unt, 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
31、 1 PAR01 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 8 页 - - - - - - - - - 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 行。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 8 页 - - - - - - - - -