Oracle 分区表小结(上)

普通表转化为分区表，必须通过重建方式进行转变。

方式一：利用原表创建分区表

--原始表

SQL>create table t(id number);

SQL>insert into T select OBJECT_ID from dba_objects;

SQL>commit;

--Change，创建表，并导入

SQL>

create table t_new(id)

PARTITION by range(id)

(PARTITION p1 values less than (4000),

PARTITION p2 values less than (6000),

PARTITION p3 values less than (7000),

PARTITION p4 values less than (maxvalue))

as select id from t;

--更改表名，进行替换

SQL> rename t to t_old

SQL> rename t_new to t;

--观察

SQL> select count(*) from t;

  COUNT(*)

----------

     49819

SQL> select count(*) from t partition(p1);

  COUNT(*)

----------

      3941

SQL> select count(*) from t partition(p2);

  COUNT(*)

----------

     1866

SQL> select count(*) from t partition(p3);

  COUNT(*)

----------

       997

SQL> select count(*) from t partition(p4);

  COUNT(*)

----------

     43015

优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。 
不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。 
适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。 

方式二：使用交换分区的方法

--原表

SQL>create table t(id number);

SQL>insert into T select OBJECT_ID from dba_objects;

SQL>commit;

--新表结构

SQL>

create table t_new(id number)

PARTITION by range(id number)

(PARTITION p1 values less than (4000),

PARTITION p2 values less than (6000),

PARTITION p3 values less than (7000),

PARTITION p4 values less than (maxvalue));

--交换分区,id<4000，前提是要交换的是满足条件的

SQL>alter table t_new exchange partition p4 with table t;

--更改表名，进行替换

SQL> rename t to t_old

SQL> rename t_new to t;

优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。 
不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。 
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。 

方式三：Oracle 9i以上版本，利用在线重定义功能

--原表

SQL>create table t(id number);

SQL>insert into T select OBJECT_ID from dba_objects;

SQL>commit;

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', 

DBMS_REDEFINITION.CONS_USE_PK);

--表结构

create table t_new(id number)

PARTITION by range(id number)

(PARTITION p1 values less than (4000),

PARTITION p2 values less than (6000),

PARTITION p3 values less than (7000),

PARTITION p4 values less than (maxvalue));

--检查表是否能重定义

exec sys.dbms_redefinition.can_redef_table

(uname => 'aicbs',

tname => 'rta_all_sum_579_200909',

options_flag => dbms_redefinition.cons_use_rowid);

--开始重定义表

exec sys.dbms_redefinition.start_redef_table

(uname => 'aicbs',

orig_table => 'rta_all_sum_579_200909',

int_table => 'rta_all_sum_579_200909_tmp',

options_flag => dbms_redefinition.cons_use_rowid);

--结束重定义表

dbms_redefinition.finish_redef_table 

(uname => 'aicbs',

orig_table => 'rta_all_sum_579_200909',

int_table => 'rta_all_sum_579_200909_tmp');

优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。 
不足：实现上比上面两种略显复杂 

分区表

1>范围分区。

使用列的一组值，通常讲该列成为分区键

Create TABLE CUSTOMER 
( 
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
FIRST_NAME VARCHAR2(30) NOT NULL, 
LAST_NAME VARCHAR2(30) NOT NULL, 
PHONE VARCHAR2(15) NOT NULL, 
EMAIL VARCHAR2(80), 
STATUS CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID) 
( 
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, 
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 
) 

2>列表分区

该分区的特点是某列的值只有几个。

Create TABLE PROBLEM_TICKETS 
( 
PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY, 
DESCRIPTION VARCHAR2(2000), 
CUSTOMER_ID NUMBER(7) NOT NULL, 
DATE_ENTERED DATE NOT NULL, 
STATUS VARCHAR2(20) 
) 
PARTITION BY LIST (STATUS) 
( 
PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01, 
PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02 
) 

3>散列分区

在列值上使用散列算法，以确定将行放入哪个分区中。

当列的值没有合适的条件时，使用散列分区。

Create TABLE HASH_TABLE 
( 
COL NUMBER(8), 
INF VARCHAR2(100) 
) 
PARTITION BY HASH (COL) 
( 
PARTITION PART01 TABLESPACE HASH_TS01, 
PARTITION PART02 TABLESPACE HASH_TS02, 
PARTITION PART03 TABLESPACE HASH_TS03 
) 

4>复合范围列表分区

先基于范围分区，后基于列表分区。

分区中的分区被称为子分区

Create TABLE SALES 
( 
PRODUCT_ID VARCHAR2(5), 
SALES_DATE DATE, 
SALES_COST NUMBER(10), 
STATUS VARCHAR2(20) 
) 
PARTITION BY RANGE(SALES_DATE) 
SUBPARTITION BY LIST (STATUS) 
( 
PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE P1_TS 
( 
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE SUBP1_TS1, 
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE SUBP1_TS2 
), 
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE P2_TS 
( 
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE SUBP2_TS1, 
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE SUBP2_TS2 
) 
)

5>复合范围散列分区

不指定表分区查看SALES表信息： 
Select * FROM SALES; 结果如下所示： 
指定P1表分区查询SALES表信息： 
Select * FROM SALES PARTITION(P1); 结果如下所示： 
指定P1SUB1子分区查询SALES表信息: 
Select * FROM SALES SUBPARTITION(P1SUB1); 结果如下所示： 
示例2：(此示例基于：四、复合范围列表分区的示例二) 
示例2基于TEMPLATE模板的表分区，查询稍稍烦琐一点。 
指定P1表分区查询SALES表信息： 
Select * FROM SALES PARTITION(P1); 结果如下所示,和刚才查询一致。 
指定SUB1子分区查询SALES表信息: 
Select * FROM SALES SUBPARTITION(SUB1); 出现如下错误信息： 
怎么解决以上问题呢？我们通过sys模式查看分区信息的数据字典,如下： 
可以看出子分区不叫SUB1，而是P1_SUB1,重新查询信息

分区表的维护

--建立实验表和索引

  Create Table t_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_t(owner) global;
SQL>create index idx_local_t_object_id on t_t(object_id) local;

下篇_link

http://blogold.chinaunix.net/u1/46911/showart.php?id=2502547