一分区表的相关实验
创建一个列表分区表
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
