公司項目數據時時更新很快，數據量也不小，記得上次做壓力單單歸檔就產生了50G,以前用分區表比較少，包括分區表的create、truncate、shrink以及複合分區表更新查詢操作.記得發明大王愛迪生說過：需要是發明的母親，做DBA,我感覺需要是進步的加速劑和助推器。所以下面就將分區表的簡單create、truncate、shrink、select等實踐相關內容試驗總結出來，當然理論還是很重要，線下需要我們足足去補充和完善.還是毛主席的那句古話說的好:數風流人物，還看今朝！
工具/原料
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) )
--插入數據
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 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下表才可以。 另外，分區表的相關理論知識請參見我的另外一篇博客:
注意事項
oracle數據庫軟件版本差異