oracle 11g 新增分區

oracle 11g新增了間隔分區、虛擬列分區和引用分區。詳細的介紹請查看官方文件：

Creating Partitions

1. 間隔分區

間隔分區是范圍分區的一種擴展。在引入間隔分區之前，DBA 需要顯式定義每個分區的值范圍，隨著分區值的增長可用分區會逐漸減少直到沒有可使用的分區為止。而間隔分區就是為了解決插入表中的數據超過了所有范圍分區時而不能自動創建分區的問題。必須至少創建一個范圍分區，范圍分區的鍵值確定范圍分區的上限值，超過該上限值數據庫服務器自動創建特定間隔的分區。
主要用在可預知的添加小范圍分區或固定時間類型的分區添加上。


限制條件如下：
1) 只能指定一個分區鍵列，并且該鍵列必須是 NUMBER 或 DATE 類型。
2) 索引表不支持間隔分區。
3) 不能為間隔分區創建域索引
4) 不能為分區指定具體名稱，由系統自動生成，格式為：SYS_Pnnn，n為數字。

Date類型測試：
創建時間類型間隔分區時指定NUMTOYMINTERVAL（n,day|month|year）子句確定分區擴展的條件，n為數字，指定按n天/月/年的方式進行分區新增。

CREATE TABLE SH.SALES_INTERVAL 
PARTITION BY RANGE (time_id) 
INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3,tbs4)
(
PARTITION P1 values less than (TO_DATE('1-1-2002','dd-mm-yyyy')),
PARTITION P2 values less than (TO_DATE('1-1-2003','dd-mm-yyyy')),
PARTITION P3 values less than (TO_DATE('1-1-2004','dd-mm-yyyy')))
AS
SELECT * 
FROM SH.SALES
WHERE TIME_ID < TO_DATE('1-1-2004','dd-mm-yyyy'); 
select partition_name from user_tab_partitions where table_name='SALES_INTERVAL';

PARTITION_NAME
---------------------------
P1
P2
P3

insert into sh.SALES_INTERVAL values(101001,4,to_date('2005-1-10','yyyy-mm-dd'),10,124,100,200);
insert into sh.SALES_INTERVAL values(101002,5,to_date('2006-1-14','yyyy-mm-dd'),11,125,100,300);

select partition_name from user_tab_partitions where table_name='SALES_INTERVAL';

PARTITION_NAME
---------------------------
P1
P2
P3
SYS_P121
SYS_P122

Number類型測試：

create table sh.test_interval
(
id number,
name varchar2(20)
)
PARTITION BY RANGE (id)
INTERVAL (100)
(PARTITION P001 values less than(500) ,
PARTITION P002 values less than(1000) 
);

insert into sh.test_interval select rownum,'A'||rownum from dual connect by level <1201;

select partition_name from user_tab_partitions where table_name='TEST_INTERVAL';

PARTITION_NAME
------------------------------
P001
P002
SYS_P134
SYS_P135
SYS_P136

select count(*) from TEST_INTERVAL PARTITION(SYS_P134);

  COUNT(*)
----------
       100

select count(*) from TEST_INTERVAL PARTITION(SYS_P135);

  COUNT(*)
----------
       100

合并分區：
需要注意，分區一定要連續，否則報ORA-14274

alter table SALES_INTERVAL merge partitions for(to_date('2005-10-10','yyyy-mm-dd')),for(to_date('2005-11-10','yyyy-mm-dd'))  into partition SYS_P137

強制創建：

LOCK TABLE SALES_INTERVAL PARTITION FOR(to_date('2008-1-14','yyyy-mm-dd')) IN SHARE MODE;

范圍分區轉換為間隔分區：

alter table TEST drop partition p_max;
alter table TEST set interval(numtodsinterval(1,'DAY')) 
alter table TEST set STORE IN (P1,P2,P3,P4)

2. 基于虛擬列的分區

如果某個表的列值是通過計算函數或表達式得到的，則這些列就稱為虛擬列。可以在 CREATE 或 ALTER 表操作過程中指定這些列。虛擬列與其它實際表列共享相同的 SQL 名稱空間，并與對其進行描述的基礎表達式的數據類型相一致。可像其它表列一樣在查詢 中使用這些列，因此可在 SQL 語句中提供簡單、優美且一致的訪問表達式機制。
虛擬列的值實際上并未存儲在磁盤上的表行中，而是根據需要進行計算。描述虛擬列的函數或表達式應是明確且唯一的，即相同的輸入值集應返回相同的輸出值。
可以像使用任何其它表列一樣使用虛擬列。可對虛擬列進行索引，可在查詢、DML 和 DDL 語句中使用它們。可在虛擬列上對表和索引進行分區，甚至可以收集它們的統計信息。 可使用虛擬列分區對表的虛擬列上定義的鍵列進行分區。對邏輯分區對象的業務要求經常 與現有列不一一對應。隨著 Oracle Database 11g 的推出，分區功能得到了增強，可以在虛 擬列上定義分區策略，因而可以更全面地滿足業務要求。
如果分區鍵上的謂詞屬于以下類型之一，則將對虛擬列分區鍵執行分區修剪：
? 等式或 Like
? 列表
? 范圍
? 擴展分區名稱

創建測試表

CREATE TABLE employees 
(employee_id number(6) not null, first_name varchar2(30), 
last_name varchar2(40) not null, emailvarchar2(25), 
phone_number varchar2(20), hire_date date not null, 
job_id varchar2(10) not null, salary number(8,2), 
commission_pct number(2,2), manager_id number(6), 
department_id number(4),
total_compensation as (salary *( 1+commission_pct))
)
PARTITION BY RANGE (total_compensation)
( 
PARTITION p1 VALUES LESS THAN (50000),
PARTITION p2 VALUES LESS THAN (100000),
PARTITION p3 VALUES LESS THAN (150000),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

插入數據：

insert into employees_inv
(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID)
select EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID
from employees
where COMMISSION_PCT is not null
and rownum < 10;

確認：

select EMPLOYEE_ID,SALARY,COMMISSION_PCT,TOTAL_COMPENSATION from employees_inv;

EMPLOYEE_ID     SALARY COMMISSION_PCT TOTAL_COMPENSATION
---------- ---------- -------------- ------------------
14000             .4              19600
13500             .3              17550
12000             .3              15600
11000             .3              14300
10500             .2              12600
10000             .3              13000
9500            .25              11875
9000            .25              11250
8000             .2               9600

3. 引用分區

引用分區通過在create table中指定PARTITION BY REFERENCE子句實現分區，不需要指定分區列，分區信息繼承自父表且自動維護。

創建測試表：

 CREATE TABLE orders
    ( order_id           NUMBER(12),
      order_date         date,
      order_mode         VARCHAR2(8),
      customer_id        NUMBER(6),
      order_status       NUMBER(2),
      order_total        NUMBER(8,2),
      sales_rep_id       NUMBER(6),
      promotion_id       NUMBER(6),
      CONSTRAINT orders_pk PRIMARY KEY(order_id)
    )
  PARTITION BY RANGE(order_date)
    ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
      PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
      PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
      PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
    );

創建測試子表：

CREATE TABLE order_items
    ( order_id           NUMBER(12) NOT NULL,
      line_item_id       NUMBER(3)  NOT NULL,
      product_id         NUMBER(6)  NOT NULL,
      unit_price         NUMBER(8,2),
      quantity           NUMBER(8),
      CONSTRAINT order_items_fk
      FOREIGN KEY(order_id) REFERENCES orders(order_id)
    )
    PARTITION BY REFERENCE(order_items_fk);

確認分區：

 select table_name,partition_name from user_tab_partitions where table_name like '%ORDER%'  order by 1,2;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS                         Q1_2005
ORDERS                         Q2_2005
ORDERS                         Q3_2005
ORDERS                         Q4_2005
ORDER_ITEMS                    Q1_2005
ORDER_ITEMS                    Q2_2005
ORDER_ITEMS                    Q3_2005
ORDER_ITEMS                    Q4_2005

父表添加分區

alter table orders add partition Q1_2006 values less than (TO_DATE('01-APR-2006','DD-MON-YYYY'));

Table altered.

SH@PROD3> select table_name,partition_name from user_tab_partitions where table_name like '%ORDER%'  order by 1,2;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS                         Q1_2005
ORDERS                         Q1_2006
ORDERS                         Q2_2005
ORDERS                         Q3_2005
ORDERS                         Q4_2005
ORDER_ITEMS                    Q1_2005
ORDER_ITEMS                    Q1_2006
ORDER_ITEMS                    Q2_2005
ORDER_ITEMS                    Q3_2005
ORDER_ITEMS                    Q4_2005

子表添加分區

SH@PROD3> alter table ORDER_ITEMS add partition Q2_2006 values less than (TO_DATE('01-JUL-2006','DD-MON-YYYY'));
alter table ORDER_ITEMS add partition Q2_2006 values less than (TO_DATE('01-JUL-2006','DD-MON-YYYY'))
            *
ERROR at line 1:
ORA-14650: operation not supported for reference-partitioned tables

其他說明：

1. 如果未顯式指定表空間，則引用分區表的分區將與父表的對應分區保存在同一位置。
2. 與其它分區表一樣，可以指定對象級的默認屬性和覆蓋對象級默認值的分區描述符。
3. 不能禁用引用分區表的外鍵約束條件。
4. 不允許添加或刪除引用分區表的分區。但是，在父表上執行分區維護操作將自動級聯到子表。