分类： Oracle
2010-09-09 12:01:08
建立实验表和索引
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 64K
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 64K
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 64K
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 64K
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 64K
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 64K
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;
这是一个代价比较大的操作，要求有较大的临时表空间和排序区
