Oracle partition operation collection

最新推荐文章于 2024-03-24 20:34:08 发布

cuixianqin2731

最新推荐文章于 2024-03-24 20:34:08 发布

阅读量147

点赞数

文章标签：数据库

http://blog.chinaunix.net/u3/116463/showart_2322471.html

Oracle 分区表维护

建立实验表和索引

SQL> Create Table t(owner, object_name, subobject_name, object_id, data_object_id, object_type, created,

                                     last_ddl_time, timestamp, status, temporary, generated, secondary)
      Partition By Range(object_id)
        (Partition p_3000 Values Less Than(3000) Tablespace users,
         Partition p_6000 Values Less than(6000) Tablespace users,
         Partition p_max Values less than(maxvalue) tablespace users
         )
     As
     Select owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
                    last_ddl_time, timestamp, status, temporary, generated, secondary
          From dba_objects;
SQL> create index idx_global_t_object_id on t(owner) global;
SQL> create index idx_local_t_object_id on t(object_id) local;

主要的分区维护操作

1.新增分区:如果分区边界不是maxvalue，那么可以直接add一个新的分区，如果边界是maxvalue，则需要先drop掉原有分区，然后再add，或者采用分区的拆分split
SQL> alter table t drop partition p_max;
Table altered
SQL> alter table t add partition p_9000 values less than(9000) tablespace users;
对于局部索引，oracle会自动增加一个局部分区索引。

2.移动分区
SQL> alter table t move partition p_6000 tablespace system;
Table altered
SQL> Select index_name,status From user_indexes Where table_name='T';
INDEX_NAME                    STATUS
------------------------------ --------
IDX_GLOBAL_T_OBJECT_ID        UNUSABLE
IDX_LOCAL_T_OBJECT_ID         N/A

分区移动会自动维护局部分区索引，oracle不会自动维护全局索引，所以需要我们重新rebuild分区索引，具体需要rebuild哪些索引，可以通过dba_part_indexes,dba_ind_partitions去判断。

3.截断分区
SQL> alter table t truncate partition p_3000 ;
Table truncated
SQL> Select index_name,status From user_indexes Where table_name='T';
INDEX_NAME                    STATUS
------------------------------ --------
IDX_GLOBAL_T_OBJECT_ID        UNUSABLE
IDX_LOCAL_T_OBJECT_ID         N/A

Truncate相对delete操作很快，数据仓库中的大量数据的批量数据加载可能会有用到；截断分区同样会自动维护局部分区索引，同时会使全局索引unusable,需要重建

4. Drop分区
SQL> alter table t drop partition p_6000;
Table altered
同样会自动维护局部分区索引，同时会使全局索引unusable,需要重建

5. 分区拆分split
通过user_tab_partitions视图来看table有哪些分区
SQL> Select table_name,partition_name From user_tab_partitions Where table_name='T';
TABLE_NAME                    PARTITION_NAME
------------------------------ ------------------------------
T                             P_3000
T                             P_9000
T                             P_MAX

现在想要把p_9000分区分成p_6000,用户存放object_id >=3000 and object_id<6000,p_9000用户存放object_id>=6000 and object_id<9000的记录，利用split技术，就可以实现

SQL> alter table tsplit partitionp_9000 at(6000) into (partitionp_6000 tablespace users,partitionp_9000 tablespace system);
SQL>
SQL> Select table_name,partition_name From user_tab_partitions Where table_name='T';
TABLE_NAME                    PARTITION_NAME
------------------------------ ------------------------------
T                             P_3000
T                             P_6000
T                             P_9000
T                             P_MAX

SQL> Select Max(object_id) ,Min(object_id) From t Partition (p_6000);
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
5999 3000

SQL> Select Max(object_id) ,Min(object_id) From t Partition (p_9000);
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
8999 6000

对于剧本索引IDX_LOCAL_T_OBJECT_ID，通过查看user_ind_partitions ,可以看到split后会自动一个局部分区索引，索引名字等同于新增分区的名字，全局索引会失效，需要rebuild。

6. 分区合并merge
相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区，原先的局部索引相应也会合并，全局索引会失效，需要rebuild。

SQL> alter table t merge partitions p_6000,p_9000 into partition p_9000;
Table altered
SQL> Select table_name,partition_name From user_tab_partitions Where table_name='T';
TABLE_NAME                    PARTITION_NAME
------------------------------ ------------------------------
T                             P_3000
T                             P_9000
T                             P_MAX

SQL> Select Max(object_id) ,Min(object_id) From t Partition (p_9000);
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
8999 3000

7. 分区交换exchange

分区的交换可以把一个表和分区表中的一个分区中的数据进行对换，分区的交换只是一个数据字典的操作，因此操作速度很快，对于数据仓库中的load阶段，因为已经做了数据的清洗动作，还可以用without validation来避免对表中数据的验证(需要全表扫描)

SQL> select count(*) from t partition(p_6000);
COUNT(*)
----------
0
SQL> create table t_6000 as select * from dba_objects where object_id>=3000 and object_id<6000;
SQL> alter table t exchange partition p_6000 with table t_6000;
SQL> select count(*) from t partition(p_6000);
COUNT(*)
----------
2955

SQL> select count(*) from t_6000;
COUNT(*)
----------
0

如果交换的表中包含的记录不符合分区的规定，那么可以用without validation子句跳过检查。

SQL> create table t_6000 as select * from dba_objects where object_id>=3000 and object_id<7000;
Table created

SQL> alter table t exchange partition p_6000 with table t_6000;
alter table t exchange partition p_6000 with table t_6000

ORA-14099:未对指定分区限定表中的所有行

SQL> alter table t exchange partition p_6000 with table t_6000 without validation;
Table altered

Exchange还有一个子句including indexes,指分区和表的索引相互交换，索引也可以交换，采用前面的例子，分区表有2个索引，一个在object_id列上的局部索引，一个是owner上的全局索引，实验在t_6000的object_id上建立所以，exchange可以完成，但在owner上，还是报错奥…

SQL>alter table t exchange partition p_6000 with table t_6000 including indexes without validation
ORA-14098: ALTER TABLE EXCHANGE PARTITION中的表索引不匹配

SQL> create index t_idx_object_owner on t_6000(owner);
建立所以后，交换成功。
SQL> drop index t_idx_object_id;
SQL> create index t_idx_object_owner on t_6000(owner);
SQL> alter table t exchange partition p_6000 with table t_6000 including indexes without validation ;
ORA-14098: ALTER TABLE EXCHANGE PARTITION中的表索引不匹配

添加分区实例

1.建表和相关索引

drop

table system .custaddr cascade constraints ;

create table system .custaddr
(
id varchar2 ( 15 byte ) not null ,
addrabb varchar2 ( 100 byte ) not null ,
teamid varchar2 ( 20 byte ),
address varchar2 ( 150 byte ),
areacode varchar2 ( 4 byte ),
addrone varchar2 ( 20 byte ),
addrtwo varchar2 ( 20 byte ),
addrthree varchar2 ( 100 byte ),
addrfour varchar2 ( 100 byte ),
compabb varchar2 ( 100 byte ),
custid varchar2 ( 15 byte ) not null
)
tablespace system
pctused 40
pctfree 10
initrans 1
maxtrans 255
logging
partition by list ( areacode )
(
partition t_list556 values ( '556' )
logging
nocompress
tablespace system
pctused 40
pctfree 10
initrans 1
maxtrans 255
storage (
initial 64 k
minextents 1
maxextents unlimited
freelists 1
freelist groups 1
buffer_pool default
),
partition p_other values ( default )
logging
nocompress
tablespace system
pctused 40
pctfree 10
initrans 1
maxtrans 255
storage (
initial 64 k
minextents 1
maxextents unlimited
freelists 1
freelist groups 1
buffer_pool default
)
)
nocompress
nocache
noparallel
monitoring ;

create index system .ix_custaddr_addrabb_tp on system .custaddr
( addrabb )
initrans 2
maxtrans 255
local (
partition t_list556
logging
nocompress
tablespace system
pctfree 10
initrans 2
maxtrans 255
storage (
initial 64 k
minextents 1
maxextents unlimited
freelists 1
freelist groups 1
buffer_pool default
),
partition p_other
logging
nocompress
tablespace system
pctfree 10
initrans 2
maxtrans 255
storage (
initial 64 k
minextents 1
maxextents unlimited
freelists 1
freelist groups 1
buffer_pool default
)
)
noparallel ;

2. 添加新的分区

alter

table custaddr drop partition p_other ;

alter table custaddr add partition t_list558 values ( '558' )
logging
nocompress
tablespace system
pctused 40
pctfree 10
initrans 1
maxtrans 255
storage (
initial 64 k
minextents 1
maxextents unlimited
freelists 1
freelist groups 1
buffer_pool default
);

alter table custaddr add partition p_other values ( default )
logging
nocompress
tablespace system
pctused 40
pctfree 10
initrans 1
maxtrans 255
storage (
initial 64 k
minextents 1
maxextents unlimited
freelists 1
freelist groups 1
buffer_pool default
);

3. 重建分区索引

对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进行。对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行维护操作时，通常会导致全局索引的INVALDED，必须在执行完操作后 REBUILD。

分区索引是不能整体重建的，不能用
SQL>alter index loc_xxxx_col rebuild;

可以指定分区进行重建：
SQL>alter index loc_xxxx_col rebuild partition 分区名 online;
使用online来尽可能减少创建过程中出现的任何加锁问题。

如果你要整体新建，必须先drop 原有分区索引，然后
SQL>create index loc_xxxx_col on xxxx(col) local tablespace SYSTEM;
这是一个代价比较大的操作，要求有较大的临时表空间和排序区

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/781883/viewspace-677061/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/781883/viewspace-677061/