Oracle分區之四：分區維護和管理

　　一分區表的相關實驗
創建一個列表分區表
create table t(id numbercity varchar())
partition by list(city)
(
partition p values (SHJSZJ)
partition p values (BJTJHB)
partition p values (GZSZ)
partition p_others values (default)
);
create or replace procedure proc
as
begin
for i in
loop
execute immediate
INSERT INTO T values(:x:y) USING iSH;
end loop;
end;
/
exec proc
create or replace procedure proc
as
begin
for i in
loop
execute immediate
INSERT INTO T values(:x:y) USING iJS;
end loop;
end;
/
exec proc
create or replace procedure proc
as
begin
for i in
loop
execute immediate
INSERT INTO T values(:x:y) USING iZJ;
end loop;
end;
/
exec proc
create or replace procedure proc
as
begin
for i in
loop
execute immediate
INSERT INTO T values(:x:y) USING iBJ;
end loop;
end;
/
exec proc
create or replace procedure proc
as
begin
for i in
loop
execute immediate
INSERT INTO T values(:x:y) USING iTJ;
end loop;
end;
/
exec proc
create or replace procedure proc
as
begin
for i in
loop
execute immediate
INSERT INTO T values(:x:y) USING iGZ;
end loop;
end;
/
exec proc
create or replace procedure proc
as
begin
for i in
loop
execute immediate
INSERT INTO T values(:x:y) USING iHB;
end loop;
end;
/
exec proc
create or replace procedure proc
as
begin
for i in
loop
execute immediate
INSERT INTO T values(:x:y) USING iSZ;
end loop;
end;
/
exec proc
create or replace procedure proc
as
begin
for i in
loop
execute immediate
INSERT INTO T values(:x:y) USING iAH;
end loop;
end;
/
exec proc
SQL> SET linesize
SQL> select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name=T;
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT

T HR P
T HR P
T HR P
T HR P_OTHERS

　　實驗一(SPLIT 分區)
alter table t split partition p values (JS) into
(partition p_partition p_);
SQL> select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name=T;
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT

T HR P_
T HR P_
T HR P
T HR P
T HR P_OTHERS

　　實驗二(merge 分區)
alter table t merge partitions p_p_ into partition p;
SQL> select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name=T;
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT

T HR P
T HR P
T HR P
T HR P_OTHERS
實驗三
alter table t split partition p values (BJTJ) into
(partition p_partition p_);
SQL> select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name=T;
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT

T HR P
T HR P_
T HR P_
T HR P
T HR P_OTHERS

　　實驗四
alter table t merge partitions p_p_ into partition p;
SQL> select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name=T;
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT

T HR P
T HR P
T HR P
T HR P_OTHERS
實驗五( 向分區某個分區裡增加個分區列值)
SQL> alter table t modify partition p add values(ZQ);
Table altered
二分區索引的相關實驗
實驗六(創建索引分區)
create index idx_t on t(id)
global partition by range(id)
(
partition p values less than ()
partition p values less than (maxvalue)
);
drop index idx_
create index idx_t on t(id)
global partition by hash(id)
partitions ;

create table tt(id numbercreatedate date)
partition by range(createdate)
subpartition by hash(id) subpartitions
(
partition p values less than (to_date(yyyymmdd))
partition p values less than (to_date(yyyymmdd))
);

create table tt(id numbername varchar())
partition by range(name)
(
partition p values less than (h)
partition p values less than (o)
);
create index idx_tt on tt(id) local;
drop indexe idx_tt ;
create index idx_tt on tt(id)
global partition by range(id)
(
partition p values less than ()
partition p values less than (maxvalue)
);
SQL> SET LINESIZE
SQL> select INDEX_OWNER INDEX_NAMEPARTITION_NAME FROM dba_Ind_Partitions where index_name=IDX_TT;
INDEX_OWNER INDEX_NAME PARTITION_NAME

HR IDX_TT P
HR IDX_TT P

　　alter index idx_tt split partition p at () into
(partition ppartition p_max);
SQL> select INDEX_OWNER INDEX_NAMEPARTITION_NAME FROM dba_Ind_Partitions where index_name=IDX_TT;
INDEX_OWNER INDEX_NAME PARTITION_NAME

HR IDX_TT P
HR IDX_TT P
HR IDX_TT P_MAX

　　
三分區表交換的相關實驗
Exchange partition提供了一種方式讓你在表與表或分區與分區之間遷移數據注意不是將表轉換成分區或非分區的形式而僅只是遷移表中數據(互相遷移)
由於其號稱是采用了更改數據字典的方式因此效率最高(幾乎不涉及io操作)Exchange partition適用於所有分區格式你可以將數據從分區表遷移到非分區表
也可以從非分區表遷移至分區表或者從hash partition到range partition諸如此類
其語法alter table tbname exchange partition/subpartition ptname with table tbname;
注意在將未分區表的數據遷移到分區表中時可能出現ora的錯誤雖然可以用without validation去解決但是此時進入分區表的數據可能不符合分區規
則所以without validation一定要慎用
a涉及交換的兩表之間表結構必須一致除非附加with validation子句;
b如果是從非分區表向分區表做交換非分區表中的數據必須符合分區表中指定分區的規則除非附加without validation子句;
c如果從分區表向分區表做交換被交換的分區的數據必須符合分區規則除非附加without validation子句;
dGlobal索引或涉及到數據改動了的global索引分區會被置為unusable除非附加update indexes子句
注意
一旦附加了without validation子句則表示不再驗證數據有效性因此指定該子句時務必慎重

　　創建一個交換分區的普通heap表
SQL> create table exchange_t(id numbercity varchar());
Table created
SQL> select distinct city from t partition (p);
CITY

TJ
BJ
HB
查看下P分區有records
SQL> select count(*) from t partition (p);
COUNT(*)


下面是分區表和普通HEAP表交換
alter table t
exchange partition p
with table exchange_t
including indexes
without validation;
驗證下數據和上面的P分區數據一致
SQL> select count(*) from exchange_t;
COUNT(*)


SQL> select distinct city from exchange_t;
CITY

TJ
BJ
HB
四一個實際應用的例子的相關實驗
創建一個分區表只保留最近年的財務數據
create table ware(wareyear varchar()id number)
partition by range (wareyear)
(
partition p_ values less than()
partition p_ values less than()
partition p_max values less than(maxvalue)
);
創建索引
create index idx_ware_id on ware(id)
global partition by range(id)
(
partition p_id_ values less than()
partition p_id_max values less than(maxvalue)
);
create index idx_ware_wareyear on ware(wareyear) local;
插入測試數據
insert into ware select object_id from dba_objects;
insert into ware select object_id from dba_objects;
commit;
年終歸檔最早的數據並加入新財年的數據
create table ware_(wareyear varchar()id number);
create index idx_ware_ on ware_(wareyear);
insert into ware_ select object_id from dba_objects;
commit;
alter table ware split partition p_max
at () into (partition p_ partition p_max);
將p_分區放入ware_表裡
alter table ware exchange partition p_
with table ware_
including indexes
without validation;
create table ware_(wareyear varchar()id number);
create index idx_ware_ on ware_(wareyear);
alter table ware exchange partition p_
with table ware_
including indexes
without validation;
刪除p_分區
alter table ware drop partition p_;
導出做歸檔
[oracle@even admin]$ exp hr/hr@test file=/home/oracle/ware_dmp tables=ware_ compress=n
Export: Release Production on Fri Jan ::
Copyright (c) Oracle All rights reserved

　　Connected to: Oracle Database g Enterprise Edition Release Production
With the Partitioning Oracle Label Security OLAP and Data Mining options
Export done in USASCII character set and ALUTF NCHAR character set
server uses ALUTF character set (possible charset conversion)
About to export specified tables via Conventional Path
exporting table WARE_ rows exported
Export terminated successfully without warnings
然後刪除表
drop table ware_;

　　五表和索引的維護的常見SQL語句及注意事項
對於分區索引不能整體進行重建只能對單個分區進行重建(也就是物理存在的分區)語法如下
Alter index idx_name rebuild partition index_partition_name [online nologging]
Alter Index IndexName Rebuild Partition P_Name;
有子分區的本地索引不能重建某分區只能對每個子分區進行重建
Alter Index Index_Name Rebuild subPartition P_Sub_Name;
腳本重建所有unUsable的索引
Select alter index || Index_Name || rebuild; From User_Indexes Where Status =UNUSABLE union
Select alter index || Index_Name || rebuild Partition ||Partition_Name ||; From User_Ind_Partitions Where Status =UNUSABLE union
Select alter index || Index_Name || rebuild subPartition ||subPartition_Name ||; From User_Ind_subPartitions Where Status =UNUSABLE;

　　add parttion
Alter Table TestTab Add Partition P Values Less Than ();
如果有子分區且定義了子分區模板所有的子分區會自動添加
新加分區後該區沒有統計信息全是空如果表級不是global_satus則表級的統計信息也會空
新加分區後如果表級統計是global_satus還會出現out of range的問題（CBO估算的選擇率很低）
解決問題的方法是copy_table_stats
exec dbms_statscopy_table_stats(user tabname => TEST_TAB srcpartname =>P_ dstpartname => P_);

　　tuncate and drop partition
truncate和drop可對有子分區的分區進行
ALTER TABLE TEST truncate Partition P_;
ALTER TABLE TEST Drop Partition P_;
它們會導致globl index的某些分區不可用必須這樣做
ALTER TABLE TEST truncate Partition P_ update indexes;
ALTER TABLE TEST truncate Partition P_update global indexes;
ALTER TABLE TEST Drop Partition P_ update indexes;
ALTER TABLE TEST Drop Partition P_ update global indexes;

　　move partition
有子分區的分區不能move只能move每個子分區(也就是物理分區)
Alter Table TEST Move Partition P_;
由於rowid變了會導致所有相關索引unusable必須這樣做
Alter Table TEST Move subPartition P__P update indexes;
Alter Table TEST Move subPartition P__P update global indexes; Local Index沒有更新
split partion
語法
alter table <table_name>
split partition <partition_name> at (<value>)
into (partition <partition_name> partition <partition_name>)
[update [global] indexes];
可以對有子分區的分區進行自動split子分區
由於rowid變了新分區和global index都變為unusable
alter table t merge partitions p_p_ into partition p;
合並range分區
ALTER TABLE Test_Tab
Merge Partitions P_ P_ Into Partition P_
[Update [global] Indexes];
該分區有子分區
有子分區也可以單獨合並子分區merge subpartition

　　可以通過下面的視圖獲取分區的信息
dba_segments
dba_part_key_columns
dba_tables
dba_tab_partitions
dba_indexes
dba_ind_partitions

From:http://tw.wingwit.com/Article/program/Oracle/201311/19037.html