oracle(表分区技术)

概念1 在Oracle中创建一张表，这个表不用于其他的什么功能，主要用于自己的软件系统一些特有功能才用的，而当你用完之后表中的数据就没用了。 Oracle的临时表创建之后基本不占用表空间，如果你没有指定临时表（包括临时表的索引）存放的表空的时候，你插入到临时表的数据是存放在ORACLE系统的临时表空间中（TEMP）。

2 表分区技术是在超大型数据库(VLDB)中将大表及其索引通过分区（patition）的形式分割为若干较小、可管理的小块，并且每一分区可进一步划分为更小的子分区（sub partition）。而这种分区对于应用来说是透明的。通过对表进行分区，可以获得以下的好处：

　　1）减少数据损坏的可能性。

　　2）各分区可以独立备份和恢复，增强了数据库的可管理性。

　　3）可以控制分区在硬盘上的分布，以均衡IO，改善了数据库的性能。

3 已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能，在线重定义表由DBMS_REDEFINITION包完成, 执行这个包需要用户有EXECUTE_CATALOG_ROLE 角色以及CREATE/ALTER/DROP/LOCK/SELECT ANY TABLE, CREATE ANY TRIGGER和CREATE ANY INDEX的权限.

重定义的第一种方法是使用主键或者伪主键进行重定义。 伪主键是具有NOT NULL约束的列的组合, 在表中, 伪主键必须是唯一的, 也可以理解为伪主键是没有PRIMARY KEY约束的主键. 使用这种方法时, 重定义前后表的(伪)主键列必须相同. 这种方法更好一些, 并且也是默认的.

重定义的第二种方法是使用rowid. 使用这种方法时, 表不能被重定义为索引组织表. 并且, 重定义后的表会有一个隐藏的列M_ROW$$, 重定义表后, 最好删除该列, 或将该列标识为UNUSED.

创建一个中间表  这个中间表和将被重定义的表在同一schema下, 其定义与重定义后的表相同.

调用DBMS_REDEFINITION.START_REDEF_TABLE()过程  参数如下: 被重定义的表, 中间表, 列影射关系, 重定义方法, 用语排序的列, 指定ORDER BY列.     在中间表上创建(克隆)依赖对象, 包括触发器, 索引, 授权, 约束. 可以使用COPY_TABLE_DEPENDENTS过程或者手工方式创建这些依赖对象.

执行DBMS_REDEFINITION.START_REDEF_TABLE()过程, 这个过程是可选的, 执行这一过程, 将会把在原始表上进行的DML操作在中间表上执行, 执行这一过程将减少在下一步骤执行的时间.

执行 DBMS_REDEFINITION.FINISH_REDEF_TABLE过程完成表的重定义. 在这个过程中, 将被重定义的表被以独占模式锁定, 这个过程与表中的数据无关. 但是FINISH_REDEF_TABLE将回等待所有依赖的DML操作完成, 才开始重定义操作.

 

可以通过这种方式来完成普通表到分区表的转化。

是如果采用了 local 分区索引，那么在增加表分区的时候，索引分区的表空间是不可控制的。如果希望将表和索引的分区分开到不同的表空间且不同索引分区也分散到不同的表空间中，那么只能在增加分区后，对新增的分区索引单独 rebuild。

4 例子：创建表空间

create tablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya01.dnf’size 50M

create tablespace dinya_space02 datafile ’/test/demo/oracle/demodata/dinya02.dnf’size 50M

create tablespace dinya_space03 datafile ’/test/demo/oracle/demodata/dinya03.dnf’size 50M

(1) 范围分区

　　范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。每次插入数据的时候，系统将根据指定的字段的值来自动将记录存储到指定的分区（表空间）中。   　　需求描述：有一个物料交易表，表名：material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。这时候我们可以使用序号分区三个区，每个区中预计存储三千万的数据，也可以使用日期分区，如每五年的数据存储在一个分区上。

(2) Hash分区/散列分区

　　散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在 I/O 设备上进行散列分区，使得这些分区大小一致。   (3) 复合分区

　　有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法。（例子：将物料交易的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中） 5

分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。全局索引建立时 global 子句允许指定索引的范围值，这个范围值为索引字段的范围值。   分区索引有两种类型：global 和 local。对于 local 索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由 Oracle 自动进行；对于 global 索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行维护操作时，通常会导致全局索引的 Invalid，必须在执行完操作后 Rebuild。Oracle9i 提供了 Update Global Indexes 语句，可以在进行分区维护的同时重建全局索引。   （例子：局部索引分区的建立）

create index dinya_idx_t on dinya_test(item_id) local ( partition idx_1 tablespace dinya_space01, partition idx_2 tablespace dinya_space02, partition idx_3 tablespace dinya_space03 );

（例子：全局索引分区的建立）

create index dinya_idx_t on dinya_test(item_id) global partition by range(item_id) ( partition idx_1 values less than (1000) tablespace dinya_space01, partition idx_2 values less than (10000) tablespace dinya_space02, partition idx_3 values less than (maxvalue) tablespace dinya_space03 );

（例子：不指定索引分区名直接对整个表建立索引）

create index dinya_idx_t on dinya_test(item_id);

4.6 其他

（例子：删除一个字段） alter table dinya_test set unused column date_type;   （例子：添加一个字段） alter table dinya_test add date_type number(1);

5 维护分区表

（例子：增加一个分区） alter table dinya_test add partition part_04 values less than(to_date(’2012-01-01’,’yyyy-mm-dd’)) tablespace dinya_space03;   （例子：合并一个分区） alter table dinya_test merge partitions part_01,part_02 into partition part_02; （例子：拆分一个分区） alter table dinya_test split Partition part_03 at (60000000) into (Partition part_03 tablespace dinya_space03, Partition part_04 tablespace dinya_space04);   （例子：改名一个分区） alter table dinya_test rename Partition part_01 to part_01_test;   （例子：删除一个分区） alter table dinya_test drop partition part_01;   （例子：修改一个分区的表空间） alter table dinya_test move Partition part_01 tablespace dinya_space05 nologging;   （补充） * 增加一个分区的时候，增加的分区的条件必须大于现有分区的最大值，否则系统将提示： ORA-14074 partition bound must collate higher than that of the last partition 错误。   * 合并一个分区的例子中将原有的表的 part_01 分区和 part_02 分区进行了合并，合并后的分区为 part_02,如果在合并的时候把合并后的分区定为 part_01 时，系统将提示： ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。   * 删除分区表的一个分区后，查询该表的数据时显示，该分区中的数据已全部丢失，所以执行删除分区动作时要慎重，确保先备份数据后再执行，或将分区合并。

 

例子：

1

创建空间 create tablespace space1 datafile 'D:/oracle/oradata/yssf/space1.dbf' size 50M

create tablespace space2 datafile 'D:/oracle/oradata/yssf/space2.dbf' size 50M

create tablespace space3 datafile 'D:/oracle/oradata/yssf/space3.dbf' size 50M

2

(1) 范围分区 按ID的范围分区  CREATE TABLE T_hchecknothing (ID, HASKER,HASKERTEL,HCITY,HCONTENT,HREPLAY  ,HSTATE,HRECORDTIME,HREPLAYTIME,HUSERID1,HUSERID2,ISDEL,FKSTATE  ) PARTITION BY RANGE (ID) (PARTITION P1 VALUES LESS THAN (10000)  tablespace space1,  PARTITION P2 VALUES LESS THAN (20000)  tablespace space2,   PARTITION P3 VALUES LESS THAN (30000)  tablespace space3,   PARTITION P4 VALUES LESS THAN (40000)  tablespace space4,   PARTITION P5 VALUES LESS THAN (50000)  tablespace space5,  PARTITION P6 VALUES LESS THAN (maxvalue) )  AS SELECT ID, HASKER,HASKERTEL,HCITY,HCONTENT,HREPLAY  ,HSTATE,HRECORDTIME,HREPLAYTIME,HUSERID1,HUSERID2,ISDEL,FKSTATE   FROM hchecknothing;

 然后  drop table HCHECKNOTHING   --> RENAME T_HCHECKNOTHING TO   HCHECKNOTHING;

 ok ！