oracle11gR2_linux_x86软件
plsqldevelop软件
试验目的:系统地试验oracle分区表的create、truncate、shrink、select等操作及注意事项
试验环境:本地windows XP系统+单实例oracle11.1.0.6.0
试验步骤:① 创建单分区表，并做查询、截断、水位线调整试验;② 创建复合range-list分区表，并做查询、截断、水位线调整试验;③ 试验心得;④ 注意事项;
试验过程:<一>创建单分区表,并做查询、截断、水位线调整试验 ============================================================== 单分区表的create_select_truncate_shrink试验集 ============================================================== --创建单分区表create table t_partition_range (id number,name varchar2(50)) partition by range(id)( partition t_range_p1 values less than (10) tablespace dawn, partition t_range_p2 values less than (20) tablespace dawn, partition t_range_p3 values less than (30) tablespace dawn, partition t_range_pmax values less than (maxvalue) tablespace dawn ); --批量插入 begin for i in 1 .. 30 loop insert into t_partition_range values(i,'dawn'||i); end loop; commit; end;--为了增强试验效果，多插入些数据insert into t_partition_range select * from t_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 * from t_partition_range partition(t_range_p1); ID NAME---------- -------------------------------------------------- 1 dawn1 2 dawn2 3 dawn3 ........--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 into orcl_subpart_table select * from orcl_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
试验心得: 经过试验，我们发现即使是复合分区,也是可以很方便地select、truncate,shrink等操作。 有几张大的二级分区表(子分区是用模板)，以为那样它们的子分区就不唯一无法像不同复合分区表一样处理了，后来经同事指点，原来即使是用模板生成的 子分区表其子分区名oracle也是设置为唯一的，一般都是partition_name_subpartition_name的形式，可以在 DBA_TAB_SUBPARTITIONS， USER_TAB_SUBPARTITIONS，ALL_TAB_SUBPARTITIONS中找到相关信息
注意事项: 本次试验中需要注意的是不管是delete还是truncate分区或子分区后，要想获得正确的num_rows和blocks[HWM],必须先重新Analyze下表才可以。 另外，分区表的相关理论知识请参见我的另外一篇博客:http://blog.itpub.net/29119536/viewspace-1141934/
oracle数据库软件版本差异