oracle 11g 新增分區(qū)

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

Creating Partitions

1. 間隔分區(qū)

間隔分區(qū)是范圍分區(qū)的一種擴(kuò)展。在引入間隔分區(qū)之前，DBA 需要顯式定義每個(gè)分區(qū)的值范圍，隨著分區(qū)值的增長可用分區(qū)會(huì)逐漸減少直到?jīng)]有可使用的分區(qū)為止。而間隔分區(qū)就是為了解決插入表中的數(shù)據(jù)超過了所有范圍分區(qū)時(shí)而不能自動(dòng)創(chuàng)建分區(qū)的問題。必須至少創(chuàng)建一個(gè)范圍分區(qū)，范圍分區(qū)的鍵值確定范圍分區(qū)的上限值，超過該上限值數(shù)據(jù)庫服務(wù)器自動(dòng)創(chuàng)建特定間隔的分區(qū)。
主要用在可預(yù)知的添加小范圍分區(qū)或固定時(shí)間類型的分區(qū)添加上。


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

Date類型測試：
創(chuàng)建時(shí)間類型間隔分區(qū)時(shí)指定NUMTOYMINTERVAL（n,day|month|year）子句確定分區(qū)擴(kuò)展的條件，n為數(shù)字，指定按n天/月/年的方式進(jìn)行分區(qū)新增。

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

合并分區(qū)：
需要注意，分區(qū)一定要連續(xù)，否則報(bào)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

強(qiáng)制創(chuàng)建：

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

范圍分區(qū)轉(zhuǎn)換為間隔分區(qū)：

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. 基于虛擬列的分區(qū)

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

創(chuàng)建測試表

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)
);

插入數(shù)據(jù)：

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;

確認(rèn)：

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. 引用分區(qū)

引用分區(qū)通過在create table中指定PARTITION BY REFERENCE子句實(shí)現(xiàn)分區(qū)，不需要指定分區(qū)列，分區(qū)信息繼承自父表且自動(dòng)維護(hù)。

創(chuàng)建測試表：

 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'))
    );

創(chuàng)建測試子表：

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);

確認(rèn)分區(qū)：

 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

父表添加分區(qū)

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

子表添加分區(qū)

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. 如果未顯式指定表空間，則引用分區(qū)表的分區(qū)將與父表的對應(yīng)分區(qū)保存在同一位置。
2. 與其它分區(qū)表一樣，可以指定對象級的默認(rèn)屬性和覆蓋對象級默認(rèn)值的分區(qū)描述符。
3. 不能禁用引用分區(qū)表的外鍵約束條件。
4. 不允許添加或刪除引用分區(qū)表的分區(qū)。但是，在父表上執(zhí)行分區(qū)維護(hù)操作將自動(dòng)級聯(lián)到子表。