ORACLE 分区表

表概述

       表是 Oracle 数据库中最基本的数据存储结构。数据在表中以行（row）和列（column）的形式存储。用户在定义表时，需要设定表名（table name）（例如employees 表），还要设定表内各列的列名（column name）（例如 employee_id，last_name，及job_id 列），数据类型（datatype）（例如VARCHAR2，DATE，或NUMBER），及宽度（width）。有些数据类型的宽度是固定的，例如DATE类型。而对于 NUMBER 类型的列来说，则需要定义精度（precision）及数值范围（scale）。

oracle数据库组织表的四种基本方式

1、堆组织表（Heap Organize Table）       普通的oracle表，数据存储没有顺序。

2、索引组织表（index-organized table）  存储B-树索引结构中排序的数据

3、集群表（cluster table）     共享相同数据库块的一组表

4、分区表（partition-table）  将大量数据的表根据不同的划分条件分解成分区的子表

以及一些特殊表：临时表（temporary table）、外部表（EXTERNAL TABLE）

删除表中数据

delete:DML语句，产生undo，删除数据量量大的时候比较慢，能用rollback撤销。

truncate:DDL语句，不会产生undo，不能撤销

并行创建表、索引

create table  test_f  PARALLEl(DEGREE 4) NOLOGGING as select * from dba_objects;

create index test_c_idx_1 on test_c(id_no) parallel (degree 4) nologging;

指定多个进程并行执行加载数据，且用nologging指定oracle不写undo。

如果像移动表，也可以使用move

alter table table_name move tablespace new_tablespace;

移动表之后，行的rowid发生了变化，因而该表上的索引会不可用。必须重新建立（re-create） 或者重建（rebuild）索引。

rebuild:

alter index index_name rebuild online nologging;

也可以通过rebuild index 来改变索引的表空间；

alter index index_name rebuild  tablespace new_tablespace;

exp:

SQL> alter index test_d_idx_1  rebuild tablespace users02;

Index altered.

分区表

分区技术（partitioning）可以将大表、大索引分解为更小、更易管理的块，这些块被称为分区（partition），通过分区技术可以有效地解决大表、大索引带来的问题。用户对分区表执行的 SQL 查询或 DML 语句与对普通数据表的语句一样。但是定义了分区后，DDL 语句可以访问、操作一个单独的分区，而不是整个表或索引，这样通过分区技术就能简化对大数据库对象的管理工作。分区对应用程序是透明的。

表或索引的所有分区必须具备相同的逻辑结构，例如列名（column name），数据类型（datatype），及数据约束（constraint）等，但每个分区的物理属性可以不同，例如 pctfree，pctused，及表空间等。

分区键

分区表（partitioned table）内的每个数据行都能且只能分配到一个分区中（partition）。分区键（partition key）是决定数据行属于哪个分区的一组数据列。Oracle 在执行插入，更新，及删除操作时能根据分区键自动地选择分区。

分区键的特点如下：

• 由 1 至 16 个数据列顺序构成
• 不能包含 LEVEL，ROWID，或MLSLABEL 虚列（pseudocolumn），也不能包含类型为 ROWID 的列
• 不能包含可为空（NULLable）的列

任何表都能够被分区，除非其中含有数据类型为 LONG 或 LONG RAW 的列。注意，含 CLOB 或 BLOB 类型列的表可以被分区。

oracle提供6种不同的方法分区表的数据：范围分析、间隔分析、散列分区、列表分区、引用分区、系统分区。此外，还可以使用组合分区，组合两个分区的方法来把数据划分成更小的子分区（subpartition）.

分区时，必须指定

• 分区方法：六种分区方式之一
• 分区列：    对表数据进行分区所依据的列，可以单列、多列
• 分区描述：对应于指定的分区方式的在分区列上的具体分区描述

1、范围分区（range partitioning）

范围分区（range partitioning）依据用户创建分区时设定的分区键值（partition key value）范围将数据映射到不同分区。通常针对日期数据使用。

exp:

    create table test_f(
    id_no  number  primary key,
    sale_code  varchar2(10) not null,
    sale_name  varchar2(10) not null,
    op_time    date not null)
    partition by range (op_time)
   (partition op_t1 values less than (to_date('2013-02-01','yyyy-mm-dd')) tablespace users02,
    partition op_t2 values less than (to_date('2013-03-01','yyyy-mm-dd')) tablespace users03,
   partition op_t3 values less than (to_date('2013-04-01','yyyy-mm-dd')) tablespace users04);

在范围分区中，应使用一个包罗万象的分区作为最后一个分区，最后一个分区包含的值小于maxvalue 大于倒数第二个分区的值。

2、间隔分区（Interval Partitioning） --11g新特性

      间隔分区是传统的范围分区的一种扩展。为使用间隔分区，必须先对该表指定一个范围分区的最小数目。范围分区键的高位值成为转变点（transition point），当表中的数据越过转折点之后，数据库自动建立间隔分区。

注意事项：

• 用 interval关键创建间隔分区的表
• 指定间隔分区钱，应该使用分区子句指定至少一个范围分区
• 不能使用包含多于1个的分区键（只能使用单分区键）
• 分区键必须为number或者date类型
• 可以通过create table 包含store in 子句 为分区选择性的指定表空间

exp：

    create table test_f(
    id_no    number,
    sale_code  varchar2(10),
    time_id   date,
    pid    varchar2(4))
    partition by range (time_id)
    interval (numtoyminterval(1,'MONTH'))
   (partition p0 values less than (to_date('2013-01-01','yyyy-mm-dd')) ,
  partition p1 values less than (to_date('2014-01-01','yyyy-mm-dd')));

Table created.

上例中，当插入的数据 date超过2013-01-01，数据库自动建立分区，每个月创建一个分区，。因此没有分区数目限制。

3、散列分区 （hash partitioning）

用户可以采用哈希分区（hash partitioning）将不适于采用范围分区（range partitioning）或列表分区（list partitioning）的数据进行分区。哈希分区的语法（syntax）简单且易于实现。

在以下情况时哈希分区比范围分区更适用：

• 用户无法事先确定一个分区可能存储的数据量
• 各范围分区的容量可能相差很大，或很难通过人工进行平衡
• 采用范围分区可能导致数据不正常的集中
• 应用系统对并行 DML（parallel DML），分区剪除（partition pruning），及基于分区的关联（partition-wise joins）等与性能有关的分区特性要求较高

分割（splitting），移除（dropping ），及融合（merging）等操作不适用于哈希分区。但对哈希分区可以进行添加（add）及接合（coalesce）操作

exp:

  1 create table test_b(
  2  id_no  number,
  3  name   varchar2(10),
  4  birth_date   date,
  5  card_id   number )
  6  partition by hash(card_id)
  7  partitions 4
  8* store in (users02,users03,users04,users05);

Table created.

可以通过dba/user_tab_partitions查看刚刚建立的test_b的4个hash分区名称。

SQL> select table_name,partition_name from dba_tab_partitions where table_name='TEST_B';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST_B                         SYS_P41
TEST_B                         SYS_P42
TEST_B                         SYS_P43
TEST_B                         SYS_P44

--再可以分别查看每个分区存储了哪些数据。

SQL> select * from test_b partition(sys_p41);

no rows selected

SQL> select * from test_b partition(sys_p42);

     ID_NO NAME                 BIRTH_DAT    CARD_ID
---------- -------------------- --------- ----------
         1 ccc                  20-JUL-13      12345
         4 eeeee                20-JUL-13      12355

SQL> select * from test_b partition(sys_p43);

     ID_NO NAME                 BIRTH_DAT    CARD_ID
---------- -------------------- --------- ----------
         2 bbb                  20-JUL-13        123


SQL> select * from test_b partition(sys_p44);

     ID_NO NAME                 BIRTH_DAT    CARD_ID
---------- -------------------- --------- ----------
         3 cdsd                 20-JUL-13       7575

4、列表分区

当数据分布在一些有限的离散数据中时，可以使用列表分区。

exp：

    create table test_list(
    id_no  number,
    name varchar2(10),
    area  varchar2(20))
    partition by list(area)
    (partition p_east values ('shanghai','zhejiang','guangdong') tablespace users02,
    partition p_north  values ('beijing','dongbei','shanxi') tablespace users03,
    partition p_south  values ('sichuan','yunan','guizhou') tablespace users04,
   partition p_west   values ('xijiang','xizang','qinghai') tablespace users05)

5、引用分区--11g新特性

如果两个表示想关联的，可以通过已有的父-子关系上对着两个表进行分区。通过主键和外键约束强制管理的。如果两个表共用一个父-子关系，只需形式上指定父表的分区即可。父表上的任何分区维护都将自动的级联到子表中。

exp：父表

   create table test_dept (
   dept_no  number,
   dept_name  varchar2(20),
  dept_address  varchar2(100),
   constraint test_dept_pk primary key(dept_no)
   )
   partition by range(dept_no)
  (partition p_dept_1  values less than (20) tablespace users02,
   partition p_dept_2  values less than (30) tablespace users03,
   partition p_dept_3 values  less than (40) tablespace users04,
  partition p_dept_4 values less than (100) tablespace users05);

新建子表test_emp，引用父表分区策略

  create table test_emp (
  id_no   number,
  name    varchar2(10),
  salary  number,
  dept_no  number not null ,
   constraint test_emp_fk foreign key (dept_no) references test_dept(dept_no)
   )
   partition by reference(test_emp_fk);

注意：

• 外键列必须为not null 否则会报错ORA-14652: reference partitioning foreign key is not supported
• 可以对引用分区使用除间隔分区以外的所有分区策略
• 引用分区中，不指定分区名称，将沿用父表派生出来的分区名称

查看子表分区的名称

SQL> select table_name,partition_name from dba_tab_partitions where table_name='TEST_EMP';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST_EMP                       P_DEPT_1
TEST_EMP                       P_DEPT_2
TEST_EMP                       P_DEPT_3
TEST_EMP                       P_DEPT_4

6、 系统分区

系统分区是一种特殊的分区，其中数据由应用程序控制而不是由数据库控制存放放置的。数据库仅仅让你把表分成区，而不管每个分区中存放的具体内容。应用程序插入值的时候，应该制定插入到哪一个分区之中，不然会报错。

exp：

  create table test_system (
  id_no   number,
  first  varchar2(10))
   partition by system
  (partition p_1 tablespace users02,
   partition p_2  tablespace users03,
  partition p_3 tablespace users04);

注：

向系统分区插入数据，必须指定将数据插入到那个分区。insert必须指定插入到的分区但是删除和更新系统分区表里的数据的时候，可以不指定分区进行操作。即delete和update不需要明确指定分区，但是建议指定分区，以便数据库使用分区修建，避免扫描整个表。系统分区不支持create table as select 和insert  into table  as语句，因为数据行与分区之间没有隐射关系。

7、组合分区

复合分区（composite partitioning）首先根据范围（range）进行分区，再使用哈希或列表方式创建子分区。复合范围-哈希分区既能够发挥范围分区的可管理性优势，也能够发挥哈希分区的数据分布（data placement），条带化（striping），及并行化（parallelism）优势。复合范围-列表分区能够发挥范围分区的可管理性优势，也能利用列表分区的显示控制能力。
1、Range-Hash ：

exp：

create table test_composite
    (
    id_no      number,
    name       varchar2(10),
    loc        varchar2(100),
    birth_date   date not null
    )
partition by range(birth_date)
subpartition by hash(id_no)
subpartitions 4 store in (users02,users03,users04,users05)
(
partition p_1  values less than (to_date('2013-02-01','yyyy-mm-dd')) tablespace users02,
partition p_2  values less than (to_date('2013-07-01','yyyy-mm-dd')) tablespace users03,
partition p_3 values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace users04,
partition p_4 values less than (to_date('9999-12-31','yyyy-mm-dd')) tablespace users05
);

SQL> select table_name ,tablespace_name from dba_tables where table_name='TEST_COMPOSITE';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST_COMPOSITE

理论上，分区表在user_tables中是没有显示指定的tablespace_name ，分区表必须从user_tab_partition查看分区表的各个分区分布在哪些表空间。

查看组合分区创建的分区，子分区：

SQL> select table_name,partition_name,subpartition_name from dba_tab_subpartitions where table_name ='TEST_COMPOSITE' order by partition_name;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
TEST_COMPOSITE                 P_1                            SYS_SUBP335
TEST_COMPOSITE                 P_1                            SYS_SUBP334
TEST_COMPOSITE                 P_1                            SYS_SUBP333
TEST_COMPOSITE                 P_1                            SYS_SUBP336
TEST_COMPOSITE                 P_2                            SYS_SUBP339
TEST_COMPOSITE                 P_2                            SYS_SUBP338
TEST_COMPOSITE                 P_2                            SYS_SUBP337
TEST_COMPOSITE                 P_2                            SYS_SUBP340
TEST_COMPOSITE                 P_3                            SYS_SUBP343
TEST_COMPOSITE                 P_3                            SYS_SUBP342
TEST_COMPOSITE                 P_3                            SYS_SUBP341

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
TEST_COMPOSITE                 P_3                            SYS_SUBP344
TEST_COMPOSITE                 P_4                            SYS_SUBP347
TEST_COMPOSITE                 P_4                            SYS_SUBP346
TEST_COMPOSITE                 P_4                            SYS_SUBP345
TEST_COMPOSITE                 P_4                            SYS_SUBP348

16 rows selected.

查看系统中所有分区表

SQL> select count(1) from dba_tables where partitioned='YES';

  COUNT(1)
----------
        71

几个与分区表相关的数据字典：

显示数据库所有分区表的信息：DBA_PART_TABLES

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

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

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

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

--仅仅列出dba组，有对应的user/all组数据字典

2、范围-列表分区示例

create table test_com_rl (
id_no  number,
name   varchar2(10),
area   varchar2(10) not null,
birth_day  date)
partition by range(birth_day)
subpartition by list(area)
subpartition template
(subpartition sb_1 values ('shanghai','chongqing','beijing','chengdu') tablespace users02,
subpartition sb_2 values ('shenzhen','jiangxi','nanchang','wuhan') tablespace users03,
subpartition sb_3 values ('hannan','kunming','guiyang','lasha') tablespace users04
)
(partition p1 values less than (to_date('2013-04-01','yyyy-mm-dd')) tablespace users02,
partition p2 values less than (to_date('2013-07-01','yyyy-mm-dd')) tablespace users03,
partition p3 values less than (to_date('2013-10-01','yyyy-mm-dd')) tablespace users04,
partition p4 values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace users05);

SUBPARTITION TEMPLATE：子分区模板，指定子分区创建方式。

3、间隔-范围分区

create table test_com_ir (
    id_no   number,
    name    varchar2(10),
    hire_date  date,
    birth_day  date)
partition by range(hire_date) interval(numtoyminterval(1,'MONTH'))
subpartition by range(birth_day)
subpartition template
(
subpartition sp_1 values less than (to_date('2013-03-01','yyyy-mm-dd')) tablespace users02,
subpartition sp_2 values less than (to_date('2013-07-01','yyyy-mm-dd')) tablespace users03,
subpartition sp_3 values less than (to_date('2013-11-01','yyyy-mm-dd')) tablespace users04,
subpartition sp_4 values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace users05
)
(
partition p_1 values less than (to_date('2013-03-01','yyyy-mm-dd'))
);

分区维护操作

注：

• 范围和列表分区不能接合
• 散列分区不能被删除，分割或者合并
• 只有列表分区能通过增加和删除分区来修改分区

除上述操作特殊外，其他分区维护操作相同

1、增加分区

alter table  test_c

add partition p_5 values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace users05;

2、分割分区

从一个已有的分区取出数据，再将其分不到两个分区中

alter table test_d

split partition p_3 at (2000) into (partition p_4,p_5)

3、合并分区

merge partition 可以将两个邻接分区的内容合并

alter table test_c

merge partition p_1,p_2 into partition p_2;

4、重命名分区

与重命名表一样
alter table test_com_ir rename subpartition p_1_sp_1 to sp1;

5、交换分区

exchange partition 命令可以将一个普通未分区表转换为分区表的一个分区。分区交换不涉及具体的数据移动，oracle重命名原来的表为一个分区，并把目标分区作为原表。数据库完成的是无数据移动的装载过程。

alter table test_c 

exchage partition p_2 with test_cc;

test_cc--未分区表

6、删除分区

alter table test_c

drop partition p_3;

7、结合分区

alter table test_c  coalesce partition