Oracle分区

2022-07-09 12:38:32

可以参考文档：http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#insertedID0 （支持11g和12c的文档）

Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。

Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

概念

表空间：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。

分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

段：分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。

什么时候需要分区表？

（1）Tables greater than 2GB should always be considered for partitioning.表的大小超过2GB

（2）Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.表中包含历史数据，新的数据被增加都新的分区中。

分区优缺点：

（1）由于将数据分散到各个分区中，减少了数据损坏的可能性；

（2）可以对单独的分区进行备份和恢复；

（3）可以将分区映射到不同的物理磁盘上，来分散IO；

（4）提高可管理性、可用性和性能。

10g提供的分区类型：

（1）范围分区（range）；

（2）哈希分区（hash）；

（3）列表分区（list）；

（4）范围－哈希复合分区（range-hash）；

（5）范围－列表复合分区（range-list）。

注意事项：

ENABLE ROW MOVEMENT（允许记录在分区间的自动迁移）The ENABLE ROW MOVEMENT clause is specified to allow the automatic migration of a row to a new partition if an update to a key value is made that would place the row in a different partition
赋予表alter table tableName enable/disable row movement;
数据在内部变化是经过了源数据插入新分区、新数据更新、源老数据删除的步骤，因此rowid将会改变;数据索引也将重走。
分区表对于分区字段是不允许进行update操作的，如果有对分区字段行进update，就会报错——ORA-14402: 更新分区关键字列将导致分区的更改。但是可以通过打开表的row movement属性来允许对分区字段的update操作。
已经存在的表无法直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。

范围分区Range

每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
范围分区 range(A,B)的分区法则，范围分区都是 values less than(A,B)的，通常情况下以A为准，如果小于A的不用考虑B，直接插进去，如果等于A那么考虑B，要是满足B的话也插进去。

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

  )

 PARTITION BY RANGE (time_id)

 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

    TABLESPACE tsa

 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

    TABLESPACE tsb

 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

    TABLESPACE tsc

 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

    TABLESPACE tsd

 );

Interval分区

INTERVAL分区是Oracle11g新增的特性，它是针对Range类型分区的一种功能拓展。对连续数据类型的Range分区，如果插入的新数据值与当前分区均不匹配，Interval-Partition特性可以实现自动的分区创建。

createtable TMP_LXQ_1(

proposalnovarchar2(22),

startdatedate )

partitionby range(STARTDATE)

interval(numtoyminterval(1,'year'))(

partitionpart_t01 values less than(to_date('2008-01-01','yyyy-mm-dd')) ,

partitionpart_t02 values less than(to_date('2009-01-01','yyyy-mm-dd')) ,

partitionpart_t03 values less than(to_date('2010-01-01','yyyy-mm-dd')) ,

partitionpart_t04 values less than(to_date('2011-01-01','yyyy-mm-dd')) ,

partitionpart_t05 values less than(to_date('2012-01-01','yyyy-mm-dd')) ,

partitionpart_t06 values less than(to_date('2013-01-01','yyyy-mm-dd'))

);

interval分区和range分区的转换

ALTER TABLE TMP_LXQ_1 SET INTERVAL (numtoyminterval(1,'year'));

ALTER TABLE TMP_LXQ_1 SET INTERVAL ();

对于INTERVAL分区表，新增的超过分区上限的数据会自动导致对应的INTERVAL分区被建立。

同样INTERVAL分区表可以方便的转化为RANGE分区表，只需要不输入INTERVAL的值即可.

interval分区的特点

1.由range分区派生而来

2.以定长宽度创建分区（比如年、月、具体的数字（比如100、500等））

3.分区字段必须是number或date类型

4.必须至少指定一个range分区（永久分区）

5.当有记录插入时，系统根据需要自动创建新的分区和本地索引

6.已有的范围分区可被转换成间隔分区（通过ALTER TABLE SET INTERVAL选项完成）

7.IntervalPartitioning不支持支持索引组织表

8.在Interval Partitioning表上不能创建domain index

9.在oracle自动创建分区的时候，系统会默认指定一个分区名

列表分区List

某列的值只有几个，基于这样的特点我们可以采用列表分区。
通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录。

CREATE TABLE q1_sales_by_region

      (deptno number,

       deptname varchar2(20),

       quarterly_sales number(10, 2),

       state varchar2(2))

   PARTITION BY LIST (state)

      (PARTITION q1_northwest VALUES ('OR', 'WA'),

       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),

       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),

       PARTITION q1_southeast VALUES ('FL', 'GA'),

       PARTITION q1_northcentral VALUES ('SD', 'WI'),

       PARTITION q1_southcentral VALUES ('OK', 'TX'));

散列分区Hash

这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。
当列的值没有合适的条件时，建议使用散列分区。
建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))

     STORAGE (INITIAL 10K)

     PARTITION BY HASH(deptno)

       (PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,

        PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);

组合分区

表首先按某列进行范围/列表（Range/List—List只在11g和12c以上支持，10g不支持）分区，然后再按某列进行范围/列表/散列（Range/List/Hash）分区，分区之中的分区被称为子分区。

CREATE TABLE emp (deptno NUMBER, empname VARCHAR(32), grade NUMBER)

     PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)

        SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)

    (PARTITION p1 VALUES LESS THAN (1000),

     PARTITION p2 VALUES LESS THAN (2000)

        STORE IN (ts2, ts4, ts6, ts8),

     PARTITION p3 VALUES LESS THAN (MAXVALUE)

       (SUBPARTITION p3_s1 TABLESPACE ts4,

        SUBPARTITION p3_s2 TABLESPACE ts5));

删除一个分区会同时删除其下的子分区。合并多个分区也会把他们的子分区自动合并。分裂分区时注意分裂点。
子分区模板：带有子分区模板的分区表在添加分区时候自动添加子分区，不带模板子分区的分区表没有这个功能；带有子分区模板的分区表在更改分区时只需更改分区，不带模板子分区的分区表在更改分区时一定注意连同子分区一起更改。

分区的维护性操作

添加分区
1. ALTER TABLE TableName ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01'，'YYYY-MM-DD'));
删除分区：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。
1. ALTER TABLE TableName DROP PARTITION P3;
截断分区：指删除某个分区中的数据，并不会删除分区
1. ALTER TABLE TableName TRUNCATE PARTITION PartitionName;
2. ALTER TABLE TableName TRUNCATE SUBPARTITION SubPartitionName;
合并分区：是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限（不能将分区合并到界限较低的分区）。
1. ALTER TABLE TableName MERGE PARTITIONS P1，P2 INTO PARTITION P2;
拆分分区：将一个分区拆分两个新分区，拆分后原来分区不再存在（不能对HASH类型的分区进行拆分）。
1. ALTER TABLE TableName SBLIT PARTITION P2 AT(TO_DATE('2003-02-01'，'YYYY-MM-DD')) INTO (PARTITION P21，PARTITION P22);
结合分区：是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合（只能用于散列分区中）。
1. ALTER TABLE TableName COALESCA PARTITION;
重命名分区：
1. ALTER TABLE TableName RENAME PARTITION P21 TO P2;

How to Partition a Non-partitioned Table【转】

Export/import method
1. Export your table: exp usr/pswd tables=numbers file=exp.dmp
2. Drop the table: drop table numbers;
3. Recreate the table with partitions:
  
  create table numbers (qty number(3), name varchar2(15))
  
  partition by range (qty)
  (partition p1 values less than (501),
  
  partition p2 values less than (maxvalue));
4. Import the table with ignore=y: imp usr/pswd file=exp.dmp ignore=y
  
  The ignore=y causes the import to skip the table creation and continues to load all rows.
Insert with a subquery method
1. Create the table with partitions:like 1.3
2. Insert into the partitioned table with a subquery from the non-partitioned table：insert into partbl (qty, name) select * from origtbl;
3. drop the original table and rename the new table
Partition exchange method(交换分区)
- ALTER TABLE EXCHANGE PARTITION can be used to convert a partition (or subpartition) into a non-partitioned table and a non-partitioned table into a partition (or subpartition) of a partitioned table by exchanging their data and index segments.
  - convert a non-partitioned table into a partition ：
    Create table NP_TableName as select with the required partitions
  - convert a partition into a non-partitioned table :
    
    Alter table EXCHANGE partition partition_name with table NP_TableName;
DBMS_REDEFINITION(在线重定义)
1. 创建临时的分区表B。
2. 开始重定义，将基表A的数据导入临时分区表B。
  1. 检查重定义的合理性:exec dbms_redefinition.can_redef_table(USER, NP_TableName,DBMS_Redefinition.cons_use_pk);
  2. 开始重定义：（耗时）
    
    EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, NP_TableName, P_TableName)
  3. 把中间表的内容和数据源表进行同步：
    
    EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, NP_TableName, P_TableName)
3. 结束重定义，此时在DB的 Name Directory里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。此时我们可以删除我们创建的临时表B。它已经是普通表。
  1. EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, NP_TableName, P_TableNameST');
  2. 执行在线重定义的过程中出错时，可以放弃执行在线重定义。
    EXECUTE DBMS_REDEFINITION.ABORT_REDEF_TABLE(USER, NP_TableName, P_TableName)

常用操作

通过数据字典“user_part_tables记录分区表的信息”，“user_tab_partitions记录表分区的信息”可查询创建分区的信息。
查询索引的分区信息可通过user_part_indexes,user_ind_partitions两个数据字典。

——显示数据库所有分区表的信息：select * from DBA_PART_TABLES

——显示当前用户可访问的所有分区表信息：select * from ALL_PART_TABLES

——显示当前用户所有分区表的信息：select * from USER_PART_TABLES

——显示表分区信息显示数据库所有分区表的详细分区信息：select * from DBA_TAB_PARTITIONS

——显示当前用户可访问的所有分区表的详细分区信息：select * from ALL_TAB_PARTITIONS

——显示当前用户所有分区表的详细分区信息：select * from USER_TAB_PARTITIONS

——显示子分区信息显示数据库所有组合分区表的子分区信息：select * from DBA_TAB_SUBPARTITIONS

——显示当前用户可访问的所有组合分区表的子分区信息：select * from ALL_TAB_SUBPARTITIONS

——显示当前用户所有组合分区表的子分区信息：select * from USER_TAB_SUBPARTITIONS

——显示分区列显示数据库所有分区表的分区列信息：select * from DBA_PART_KEY_COLUMNS

——显示当前用户可访问的所有分区表的分区列信息：select * from ALL_PART_KEY_COLUMNS

——显示当前用户所有分区表的分区列信息：select * from USER_PART_KEY_COLUMNS

——显示子分区列显示数据库所有分区表的子分区列信息：select * from DBA_SUBPART_KEY_COLUMNS

——显示当前用户可访问的所有分区表的子分区列信息：select * from ALL_SUBPART_KEY_COLUMNS

——显示当前用户所有分区表的子分区列信息：select * from USER_SUBPART_KEY_COLUMNS

——怎样查询出oracle数据库中所有的的分区表select * from user_tables a where a.partitioned='YES'

码农公寓

概念