公司项目数据时时更新很快，数据量也不小，记得上次做压力单单归档就产生了50G,以前用分区表比较少，包括分区表的create、truncate、shrink以及复合分区表更新查询操作.记得发明大王爱迪生说过：需要是发明的母亲，做DBA,我感觉需要是进步的加速剂和助推器。所以下面就将分区表的简单create、truncate、shrink、select等实践相关内容试验总结出来，当然理论还是很重要，线下需要我们足足去补充和完善.还是毛主席的那句古话说的好:数风流人物，还看今朝！
(1)试验目的:系统地试验oracle分区表的create、truncate、shrink、select等操作及注意事项;
(2)试验环境:本地windows XP系统+单实例oracle11.1.0.6.0;
(3)试验步骤:
① 创建单分区表，并做查询、截断、水位线调整试验;
② 创建复合range-list分区表，并做查询、截断、水位线调整试验;
③ 试验心得;
④ 注意事项;
(4)试验过程:
<一>创建单分区表,并做查询、截断、水位线调整试验;
==============================================================================================================================
单分区表的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 AM
T_PARTITION_RANGE T_RANGE_P3 30 1310720 2764 52 2014-4-15 AM
T_PARTITION_RANGE T_RANGE_P1 10 1179648 2386 46 2014-4-15 AM
T_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
4 dawn4
5 dawn5
6 dawn6
7 dawn7
8 dawn8
9 dawn9
........
--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 AM
T_PARTITION_RANGE T_RANGE_P3 30 1310720 2764 52 2014-4-15 AM
T_PARTITION_RANGE T_RANGE_PMAX MAXVALUE 131072 370 14 2014-4-15 AM
T_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 AM
T_PARTITION_RANGE T_RANGE_P3 30 1310720 2764 52 2014-4-15 AM
T_PARTITION_RANGE T_RANGE_PMAX MAXVALUE 131072 370 14 2014-4-15 AM
T_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 AM
T_PARTITION_RANGE T_RANGE_PMAX MAXVALUE 131072 370 14 2014-4-15 AM
T_PARTITION_RANGE T_RANGE_P1 10 0 1 7 2014-4-15 AM
T_PARTITION_RANGE T_RANGE_P2 20 0 0 8 2014-4-15 AM
===============================================================================================
range-list复合分区表的create_select_truncate_shrink试验集
===============================================================================================
<二>创建复合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 "SP4" VALUES ( 4 ),
SUBPARTITION "SP5" VALUES ( 5 ),
SUBPARTITION "SP6" VALUES ( 6 ),
SUBPARTITION "SP7" VALUES ( 7 ),
SUBPARTITION "SP8" VALUES ( 8 ),
SUBPARTITION "SP9" VALUES ( 9 ),
SUBPARTITION "SP10" VALUES ( 10 ),
SUBPARTITION "SP11" VALUES ( 11 ),
SUBPARTITION "SP12" VALUES ( 12 ),
SUBPARTITION "SP13" VALUES ( 13 ),
SUBPARTITION "SP14" VALUES ( 14 ),
SUBPARTITION "SP15" VALUES ( 15 ),
SUBPARTITION "SP16" VALUES ( 16 ),
SUBPARTITION "SP17" VALUES ( 17 ),
SUBPARTITION "SP18" VALUES ( 18 ),
SUBPARTITION "SP19" VALUES ( 19 ),
SUBPARTITION "SP20" VALUES ( 20 ),
SUBPARTITION "SP21" VALUES ( 21 ),
SUBPARTITION "SP22" VALUES ( 22 ),
SUBPARTITION "SP23" VALUES ( 23 ),
SUBPARTITION "SP24" VALUES ( 24 ),
SUBPARTITION "SP25" VALUES ( 25 ),
SUBPARTITION "SP26" VALUES ( 26 ),
SUBPARTITION "SP27" VALUES ( 27 ),
SUBPARTITION "SP28" VALUES ( 28 ),
SUBPARTITION "SP29" VALUES ( 29 ),
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)
)
--插入数据
begin
for i in 1 .. 10 loop
insert into orcl_subpart_table values(201403,i);
end loop;
commit;
end;
begin
for i in 11 .. 20 loop
insert into orcl_subpart_table values(201404,i);
end loop;
commit;
end;
begin
for i in 21 .. 31 loop
insert 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 AM
DATA_PART_201402 DATA_PART_201402_SP29 29 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP30 30 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP31 31 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP27 27 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP21 21 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP25 25 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP24 24 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP23 23 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP22 22 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP26 26 278528 622 18 2014-4-15 AM
DATA_PART_201403 DATA_PART_201403_SP7 7 98304 244 12 2014-4-15 AM
DATA_PART_201403 DATA_PART_201403_SP8 8 98304 244 12 2014-4-15 AM
DATA_PART_201403 DATA_PART_201403_SP9 9 98304 244 12 2014-4-15 AM
DATA_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 AM
DATA_PART_201402 DATA_PART_201402_SP28 28 0 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP29 29 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP30 30 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP31 31 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP21 21 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP25 25 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP24 24 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP23 23 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP22 22 278528 622 18 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP13 13 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP14 14 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP15 15 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP16 16 65536 118 10 2014-4-15 AM
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 AM
DATA_PART_201402 DATA_PART_201402_SP28 28 0 1 7 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP29 29 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP30 30 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP31 31 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP21 21 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP25 25 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP24 24 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP23 23 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP22 22 278528 622 18 2014-4-15 AM
DATA_PART_201402 DATA_PART_201402_SP26 26 278528 622 18 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP20 20 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP19 19 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP18 18 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP17 17 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP11 11 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP13 13 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP14 14 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP15 15 65536 118 10 2014-4-15 AM
DATA_PART_201404 DATA_PART_201404_SP16 16 65536 118 10 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 PM
DATA_PART_201402 DATA_PART_201402_SP25 25 278528 622 18 2014-4-15 PM
DATA_PART_201402 DATA_PART_201402_SP26 26 278528 622 18 2014-4-15 PM
DATA_PART_201402 DATA_PART_201402_SP27 27 278528 622 18 2014-4-15 PM
DATA_PART_201402 DATA_PART_201402_SP30 30 278528 622 18 2014-4-15 PM
DATA_PART_201402 DATA_PART_201402_SP21 21 278528 622 18 2014-4-15 PM
DATA_PART_201402 DATA_PART_201402_SP24 24 278528 622 18 2014-4-15 PM
DATA_PART_201402 DATA_PART_201402_SP23 23 278528 622 18 2014-4-15 PM
DATA_PART_201402 DATA_PART_201402_SP22 22 278528 622 18 2014-4-15 PM
DATA_PART_201402 DATA_PART_201402_SP31 31 278528 622 18 2014-4-15 PM
===============================================================================================
试验心得
===============================================================================================
<三>试验心得:
经过试验，我们发现即使是复合分区,也是可以很方便地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/
----------------------------->>end
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31441231/viewspace-2134779/ ，如需转载，请注明出处，否则将追究法律责任。
