Oracle 分区表小结(下)

1>新增分区

如果分区边界不是maxvalue，那么可以直接add一个新的分区，如果边界是maxvalue，则需要先drop掉原有分区，然后再add，或者采用分区的拆分split 

SQL>alter table t_t drop partition p_max;

SQL>alter table t_t add partition p_9000 values less than(9000) tablespace users;

SQL>alter table t_t add partition p_max values less than(maxvalue) tablespace users;

对于局部索引，oracle会自动增加一个局部分区索引

2>移动分区

SQL>alter table t_t move partition p_6000 tablespace test5;

SQL>select index_name,status from user_indexes Where table_name='T_T'; 

INDEX_NAME                     STATUS

------------------------------ --------

IDX_LOCAL_T_OBJECT_ID          N/A

IDX_GLOBAL_T_OBJECT_ID         UNUSABLE

分区移动会自动维护局部索引，Oracle不会自动维护全局索引。需要rebuild，通过dba_part_indexes,dba_ind_partition去判断

3>截断分区

删除了该分区内的数据，不会删除表

SQL>alter table t_t truncate partition p_3000;

SQL>select index_name,status from user_indexes Where table_name='T_T'; 

INDEX_NAME                     STATUS

------------------------------ --------

IDX_LOCAL_T_OBJECT_ID          N/A

IDX_GLOBAL_T_OBJECT_ID         UNUSABLE

Truncate 相对delete操作快，数据仓库中的大量数据的批量数据加载可能会用到。

自动维护局部索引，全局索引需要重建

4>drop 分区

SQL>alter table t drop partition p_6000

如果要删除的分区是唯一的分区不能被删除，先删除表。

自动维护局部索引，全局索引需要重建

5>分区拆分split

--查看表有哪些分区,user_tab_partitions

SQL>select table_name,partition_name from user_tab_partitions where table_name='T_T'; 

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

T_T                            P_3000

T_T                            P_6000

T_T                            P_9000

T_T                            P_MAX

P_9000分区分成p_7000,

object_id >=6000 and object_id<7000 

object_id >=7000 and object_id<9000 

SQL>alter table t_t split partitionp_9000 at(7000) into (partitionp_7000 tablespace users,partitionp_9000 tablespace users); 

注意:

不能对HASH类型的分区进行拆分。 

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

SS

7>分区交换exchange

可以把一个表和分区表的一个分区的数据进行对换。

分区交换只是一个数据字典的操作，因此速度快。

对于数据创库中的load阶段，做了数据的清洗动作，可以用without validateion来避免

数据的验证。

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子句跳过检查。

还有一个including indexes，指分区和表的索引相互交换

8>重命名分区

以下代码将P21更改为P2 
Alter TABLE SALES RENAME PARTITION P21 TO P2; 

添加分区实例

1>建表和相关索引

 create table partition_test1   

(      
       transaction_id number primary key,
       item_id number(8) not null,
       item_description varchar2(300),
       transaction_date date  not null
)
STORAGE (INITIAL 100K NEXT 50K) LOGGING
partition by range (transaction_id)
(         
       partition part_01 values less than(30000000) tablespace par01 
storage(initial 50k next 20k)     ,
       partition part_02 values less than(60000000) tablespace par02,
       partition part_03 values less than(maxvalue) tablespace par03
); 

-- 局部索引 

CREATE INDEX ix_l_test1_transaction_date ON partition_test1(transaction_date ) LOCAL; 

--创建分去表2的全局分区索引
create index ix_g_test2_transaction_id on partition_test2(transaction_id)
global partition by range(transaction_id)
( partition part_01 values less than (30000000) tablespace par01,
  partition part_02 values less than (60000000) tablespace par02,
  partition part_03 values less than (maxvalue) tablespace par03 
); 

--如果区分是局部索引还是全局索引

select index_name,table_name,locality from dba_part_indexes 

IDX_LOCAL_T_OBJECT_ID          T_T                            LOCAL

IX_L_TEST1_TRANSACTION_DATE    PARTITION_TEST1                LOCAL

IX_G_TEST2_TRANSACTION_ID      PARTITION_TEST2                GLOBAL

2>重建分区索引

对于局部索引(local)：索引信息存放位置依赖于父表的Partiton信息。每个表的分区对应一个索引，当表的分区发生变化时，索引的维护由Oracle自动进行。

索引信息存放在父表分区所在的表空间，仅可以创建在父表为hashtable 或者composite分区表的local store in(tablespace),并且指定的分区数目要与父表的分区数目一致

对于全局索引：可以选择是否分区，索引的分区可以不与表分区相对应。当对分区进行维护操作后，会导致全局索引的invalid，必须执行操作后rebuild

索引信息的存放位置与父表的Partition信息完全不相干，甚至父表式不是分区表都无所谓

--如果删除父表的一个分区必须更新global index

--查询索引 
select object_name,object_type,tablespace_name,sum(value) 
from v$segment_statistics 
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX' 
group by object_name,object_type,tablespace_name 
order by 4 desc 

注：

分区索引不能整体重建，不能用

阅读(1214) | 评论(1) | 转发(0) |

0

上一篇：Oracle 分区表小结(上)

下一篇：Oracle 等待事件小结(上)