oracle in() 边界,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_tmove partition p_6000 tablespacetest5;

SQL>select index_name,statusfrom 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,statusfrom 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_namefrom user_tab_partitionswhere 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_tsplit partitionp_9000 at(7000) into (partitionp_7000 tablespace users,partitionp_9000 tablespaceusers);

注意:

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

6>合并分区merge

相邻的分区可以merge为一个分区，

新分区的下边界为原来边界值较低的分区，

上边界为远离边界值较高的分区。

局部索引会合并。全局索引失效，需要rebuild

SQL> alter table t merge partitions p_6000,p_9000 into partition p_9000;Table alteredSQL> Select table_name,partition_name From user_tab_partitions Where table_name='T';TABLE_NAME                    PARTITION_NAME------------------------------ ------------------------------T                             P_3000T                             P_9000T                             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更改为P2Alter 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) LOGGINGpartition by range (transaction_id)(partition part_01 values less than(30000000) tablespace par01storage(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_statisticswhere statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'group by object_name,object_type,tablespace_nameorder by 4 desc

注：

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

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