oracle分区表实践案例集

2024-10-25 16:27:59

1、试验目的:系统地试验oracle分区表的create、truncate、shrink、select等操作及注意事项

2、试验环境:本地windowsXP系统+单实例oracle11.1.0.6.0

3、试验步骤:①　创建单分区表，并做查询、截断、水位线调整试验;②　创建复合range-list分区表，并做查询、截断、水位线调整试验;③　试验心得;④　注意事项;

4、试验过程:<一>创建单分区表,并做查询、截断、水位线调整试验 ============================================================== 单分区表的create_select_truncate_shrink试验集 ==============================================================--创建单分区表createtablet_partition_range(idnumber,namevarchar2(50))partitionbyrange(id)(partitiont_range_p1valueslessthan(10)tablespacedawn,partitiont_range_p2valueslessthan(20)tablespacedawn,partitiont_range_p3valueslessthan(30)tablespacedawn,partitiont_range_pmaxvalueslessthan(maxvalue)tablespacedawn);--批量插入beginforiin1..30loopinsertintot_partition_rangevalues(i,'dawn'||i);endloop;commit;end;--为了增强试验效果，多插入些数据insertintot_partition_rangeselect*fromt_partition_range;commit;-->>SQL> select t3.table_name, t3.partition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed 2 from user_tab_partitions t3 where t3.table_name='T_PARTITION_RANGE' order by t3.num_rows desc;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED---- ------------------------------ ---------- ---------- ---------T_PARTITION_RANGE T_RANGE_P2 20 1310720 2764 52 2014-4-15 AMT_PARTITION_RANGE T_RANGE_P3 30 1310720 2764 52 2014-4-15 AMT_PARTITION_RANGE T_RANGE_P1 10 1179648 2386 46 2014-4-15 AMT_PARTITION_RANGE T_RANGE_PMAX MAXVALUE 131072 370 14 2014-4-15 AM--分区查询SQL>select*fromt_partition_rangepartition(t_range_p1);IDNAME------------------------------------------------------------1dawn12dawn23dawn3 ........--delete分区t_range_p1所有数据后重新analyze后num_rows正常(归零),HWM(对应的 blocks字段)不正常[依然是删除前的2386];delete from T_PARTITION_RANGE where id<10;commit;analyze table T_PARTITION_RANGE compute statistics;---->>SQL> select t3.table_name, t3.partition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed 2 from user_tab_partitions t3 where t3.table_name='T_PARTITION_RANGE' order by t3.num_rows desc;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED----------------------- ------------------------------ ---------- -------T_PARTITION_RANGE T_RANGE_P2 20 1310720 2764 52 2014-4-15 AMT_PARTITION_RANGE T_RANGE_P3 30 1310720 2764 52 2014-4-15 AMT_PARTITION_RANGE T_RANGE_PMAX MAXVALUE 131072 370 14 2014-4-15 AMT_PARTITION_RANGE T_RANGE_P1 10 0 2386 46 2014-4-15 AM---shrink子分区，然后重新统计后查询，HWM下降;alter table t_partition_range enable row movement;alter table t_partition_range MODIFY PARTITION t_range_p1 shrink space;analyze table T_PARTITION_RANGE compute statistics;---------->>SQL> select t3.table_name, t3.partition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed 2 from user_tab_partitions t3 where t3.table_name='T_PARTITION_RANGE' order by t3.num_rows desc;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED--------------------- ------------------------------ ---------- -------T_PARTITION_RANGE T_RANGE_P2 20 1310720 2764 52 2014-4-15 AMT_PARTITION_RANGE T_RANGE_P3 30 1310720 2764 52 2014-4-15 AMT_PARTITION_RANGE T_RANGE_PMAX MAXVALUE 131072 370 14 2014-4-15 AMT_PARTITION_RANGE T_RANGE_P1 10 0 1 7 2014-4-15 AM---truncate子分区[t_range_p2]，然后重新统计后查询，HWM下降,而且清理数据比delete快很多，可以说不是一个重量级的;alter table t_partition_range truncate partition t_range_p2;analyze table T_PARTITION_RANGE compute statistics;------>>SQL> select t3.table_name, t3.partition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed 2 from user_tab_partitions t3 where t3.table_name='T_PARTITION_RANGE' order by t3.num_rows desc;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED--------------------- ------------------------------ ---------- -------T_PARTITION_RANGE T_RANGE_P3 30 1310720 2764 52 2014-4-15 AMT_PARTITION_RANGE T_RANGE_PMAX MAXVALUE 131072 370 14 2014-4-15 AMT_PARTITION_RANGE T_RANGE_P1 10 0 1 7 2014-4-15 AMT_PARTITION_RANGE T_RANGE_P2 20 0 0 8 2014-4-15 AM<二>创建复合range-list分区表,并做查询、截断、水位线调整试验--创建二级range-list分区表create table orcl_subpart_table(gather_part number, gather_day number)partition by range(gather_part) subpartition by list(gather_day)SUBPARTITION TEMPLATE ( SUBPARTITION "SP1" VALUES ( 1 ), SUBPARTITION "SP2" VALUES ( 2 ), SUBPARTITION "SP3" VALUES ( 3 ), ...... SUBPARTITION "SP30" VALUES ( 30 ), SUBPARTITION "SP31" VALUES ( 31 ) )(PARTITION "DATA_PART_201402" VALUES LESS THAN (201403),PARTITION "DATA_PART_201403" VALUES LESS THAN (201404),PARTITION "DATA_PART_201404" VALUES LESS THAN (201405))--插入数据beginfor i in 1 .. 10 loopinsert into orcl_subpart_table values(201403,i);end loop;commit;end;beginfor i in 11 .. 20 loopinsert into orcl_subpart_table values(201404,i);end loop;commit;end;beginfor i in 21 .. 31 loopinsert into orcl_subpart_table values(201402,i);end loop;commit;end;--为了增强试验效果，多插入些数据insert intoorcl_subpart_table select * fromorcl_subpart_table ;commit;--测试查询select * from orcl_subpart_table partition(DATA_PART_201402);select * from orcl_subpart_table subpartition(DATA_PART_201402_SP21);--truncate子分区alter table Partition_Table_Name truncate partition/subpartition Partition_Name update indexes;alter table orcl_subpart_table truncate partition DATA_PART_201402 update indexes;alter table orcl_subpart_table truncate subpartition DATA_PART_201402_SP21 update indexes;---试验前复合分区表明细SQL> select t3.partition_name,t3.subpartition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed 2 from user_tab_subpartitions t3 where t3.table_name='ORCL_SUBPART_TABLE' order by t3.num_rows desc;PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED------------------ ------------------------------ ---------- -------DATA_PART_201402 DATA_PART_201402_SP28 28 278528 622 18 2014-4-15 AMDATA_PART_201402 DATA_PART_201402_SP29 29 278528 622 18 2014-4-15 AM......DATA_PART_201403 DATA_PART_201403_SP9 9 98304 244 12 2014-4-15 AMDATA_PART_201403 DATA_PART_201403_SP10 10 98304 244 12 2014-4-15 AM--针对子分区DATA_PART_201402_SP28进行试验。delete该子分区后重新analyze后结果:delete from ORCL_SUBPART_TABLE t5 where t5.gather_part=201402 and t5.gather_day=28;commit;analyze table ORCL_SUBPART_TABLE compute statistics;------>>SQL> select t3.partition_name,t3.subpartition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed 2 from user_tab_subpartitions t3 where t3.table_name='ORCL_SUBPART_TABLE' order by t3.num_rows desc;PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED------------------ ------------------------------ ---------- -------DATA_PART_201402 DATA_PART_201402_SP27 27 278528 622 18 2014-4-15 AMDATA_PART_201402 DATA_PART_201402_SP28 28 0 622 18 2014-4-15 AMDATA_PART_201402 DATA_PART_201402_SP29 29 ......DATA_PART_201404 DATA_PART_201404_SP12 12 65536 118 10 2014-4-15 AM----shrink子分区DATA_PART_201402_SP28后重新analyze再次查询:alter table ORCL_SUBPART_TABLE enable row movement;alter table ORCL_SUBPART_TABLE MODIFY subPARTITION DATA_PART_201402_SP28 shrink space;analyze table ORCL_SUBPART_TABLE compute statistics;---->>SQL> select t3.partition_name,t3.subpartition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed 2 from user_tab_subpartitions t3 where t3.table_name='ORCL_SUBPART_TABLE' order by t3.num_rows desc;PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED--------------------- ------------------------------ ---------- -------DATA_PART_201402 DATA_PART_201402_SP27 27 278528 622 18 2014-4-15 AMDATA_PART_201402 DATA_PART_201402_SP28 28 0 1 7 2014-4-15 AM...... DATA_PART_201404 DATA_PART_201404_SP12 12 65536 118 10 2014-4-15 AM---truncate子分区DATA_PART_201402_SP29后重新analyze后查询:alter table ORCL_SUBPART_TABLE truncate subpartition DATA_PART_201402_SP29;analyze table ORCL_SUBPART_TABLE compute statistics;-------->>SQL> select t3.partition_name,t3.subpartition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed 2 from user_tab_subpartitions t3 where t3.table_name='ORCL_SUBPART_TABLE' order by t3.num_rows desc;PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED------------------- ------------------------------ ---------- -------DATA_PART_201402 DATA_PART_201402_SP29 29 0 0 8 2014-4-15 PMDATA_PART_201402 DATA_PART_201402_SP25 25 278528 622 18 2014-4-15 PMDATA_PART_201402 DATA_PART_201402_SP26 26 278528 622 18 2014-4-15 PMDATA_PART_201402 DATA_PART_201402_SP27 27 278528 622 18 2014-4-15 PMDATA_PART_201402 DATA_PART_201402_SP30 30 278528 622 18 2014-4-15 PMDATA_PART_201402 DATA_PART_201402_SP21 21 278528 622 18 2014-4-15 PMDATA_PART_201402 DATA_PART_201402_SP24 24 278528 622 18 2014-4-15 PMDATA_PART_201402 DATA_PART_201402_SP23 23 278528 622 18 2014-4-15 PMDATA_PART_201402 DATA_PART_201402_SP22 22 278528 622 18 2014-4-15 PMDATA_PART_201402 DATA_PART_201402_SP31 31 278528 622 18 2014-4-15 P

5、试验心得: 经过试验，我们发现即使是复合分区,也是可以很方便地select、truncate,shrink等操作。上次公司有几张大的二级分区表(子分区是用模板)，以为那样它们的子分区就不唯一无法像不同复合分区表一样处理了，后来经同事指点，原来即使是用模板生成的子分区表其子分区名oracle也是设置为唯一的，一般都是partition_name_subpartition_name的形式，可以在DBA_TAB_SUBPARTITIONS，USER_TAB_SUBPARTITIONS，ALL_TAB_SUBPARTITIONS中找到相关信息

6、注意事项: 本次试验中需要注意的是不管是delete还是truncate分区或子分区后，要想获得正确的num_rows和blocks[HWM],必须先重新Analyze下表才可以。另外，分区表的相关理论知识请参见我的另外一篇博客:http://blog.itpub.net/29119536/viewspace-1141934/