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

1. 間隔分區(qū)

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

主要用在可預(yù)知的添加小范圍分區(qū)或固定時間類型的分區(qū)添加上。

限制條件如下：

1) 只能指定一個分區(qū)鍵列，并且該鍵列必須是 NUMBER 或 DATE 類型。

2) 索引表不支持間隔分區(qū)。

3) 不能為間隔分區(qū)創(chuàng)建域索引

4) 不能為分區(qū)指定具體名稱，由系統(tǒng)自動生成，格式為：SYS_Pnnn，n為數(shù)字。

Date類型測試：

創(chuàng)建時間類型間隔分區(qū)時指定NUMTOYMINTERVAL(n,day|month|year)子句確定分區(qū)擴展的條件，n為數(shù)字，指定按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ù)，否則報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

強制創(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ū)

如果某個表的列值是通過計算函數(shù)或表達(dá)式得到的，則這些列就稱為虛擬列。可以在 CREATE 或 ALTER 表操作過程中指定這些列。虛擬列與其它實際表列共享相同的 SQL 名稱空間，并與對其進行描述的基礎(chǔ)表達(dá)式的數(shù)據(jù)類型相一致。可像其它表列一樣在查詢 中使用這些列，因此可在 SQL 語句中提供簡單、優(yōu)美且一致的訪問表達(dá)式機制。

虛擬列的值實際上并未存儲在磁盤上的表行中，而是根據(jù)需要進行計算。描述虛擬列的函數(shù)或表達(dá)式應(yīng)是明確且唯一的，即相同的輸入值集應(yīng)返回相同的輸出值。

可以像使用任何其它表列一樣使用虛擬列。可對虛擬列進行索引，可在查詢、DML 和 DDL 語句中使用它們。可在虛擬列上對表和索引進行分區(qū)，甚至可以收集它們的統(tǒng)計信息。 可使用虛擬列分區(qū)對表的虛擬列上定義的鍵列進行分區(qū)。對邏輯分區(qū)對象的業(yè)務(wù)要求經(jīng)常 與現(xiàn)有列不一一對應(yīng)。隨著 Oracle Database 11g 的推出，分區(qū)功能得到了增強，可以在虛 擬列上定義分區(qū)策略，因而可以更全面地滿足業(yè)務(wù)要求。

如果分區(qū)鍵上的謂詞屬于以下類型之一，則將對虛擬列分區(qū)鍵執(zhí)行分區(qū)修剪：

? 等式或 Like

? 列表

? 范圍

? 擴展分區(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子句實現(xiàn)分區(qū)，不需要指定分區(qū)列，分區(qū)信息繼承自父表且自動維護。

創(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

其他說明：

如果未顯式指定表空間，則引用分區(qū)表的分區(qū)將與父表的對應(yīng)分區(qū)保存在同一位置。

與其它分區(qū)表一樣，可以指定對象級的默認(rèn)屬性和覆蓋對象級默認(rèn)值的分區(qū)描述符。

不能禁用引用分區(qū)表的外鍵約束條件。

不允許添加或刪除引用分區(qū)表的分區(qū)。但是，在父表上執(zhí)行分區(qū)維護操作將自動級聯(lián)到子表。

總結(jié)

以上是生活随笔為你收集整理的oracle 增加一个新分区,oracle 11g 新增分区的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。