溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
一、分區(qū)概念
Oracle允許將表、索引、索引組織表細分成更小的片，每個片我們稱之為分區(qū)。分區(qū)有其自己的名字和存儲參數。
如下圖：
每行數據只能屬于一個分區(qū)，分區(qū)鍵決定數據行屬于哪個分區(qū)。分區(qū)鍵由一個或多個列組成。Oracle自動的將數據的DML操作映射到相應的分區(qū)中。
何時使用分區(qū)表：
Tables greater than 2 GB should always be considered as candidates for partitioning.
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.
When the contents of a table must be distributed across different types of storage devices.
何時使用分區(qū)索引：
Avoid rebuilding the entire index when data is removed.
Perform maintenance on parts of the data without invalidating the entire index.
Reduce the effect of index skew caused by an index on a column with a monotonically increasing value.
分區(qū)的優(yōu)點：
分區(qū)裁剪，DML操作的數據如果可以定位到某個或者某些分區(qū)，那么只需對這部分數據進行讀寫。
分區(qū)智能join，如果關聯(lián)查詢只需用到部分分區(qū)，那么避免了全表數據的掃描。
易于維護數據。
分區(qū)策略：
LIST、RANGE、HASH、SYSTEM、INTERVAL、reference、虛擬列分區(qū)。
表分區(qū)類型：
單級別分區(qū)
組合分區(qū)
索引分區(qū)類型：
本地分區(qū)
全局分區(qū)
全局索引
二、創(chuàng)建分區(qū)
創(chuàng)建LIST分區(qū)
SQL> CREATE TABLE accounts
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
( PARTITION p_northwest VALUES ('OR', 'WA')
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
, PARTITION p_southeast VALUES ('FL', 'GA')
, PARTITION p_northcentral VALUES ('SD', 'WI')
, PARTITION p_southcentral VALUES ('OK', 'TX')
);
Table created.
SQL>插入數據
SET DEFINE OFF; Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (378326946, 894594273, 1292607495, 142784215, 'OR', 'k'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (584618757, 1364130187, 438768827, 554439762, 'WA', 'o'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (41523719, 656248010, 469613013, 1849874408, 'OR', 'o'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1484487356, 1308908163, 1088179442, 1201623497, 'OR', 'w'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (151951677, 2077813008, 139635883, 1324220110, 'OR', 'L'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (537989506, 1256709056, 5665768, 1397516214, 'WA', 'j'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1054752157, 511468132, 2033193426, 183536554, 'WA', 'q'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (2038555798, 1761488065, 1281290484, 1134105071, 'OR', 'y'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1749170397, 1025971133, 1336822659, 219993587, 'OR', 'G'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1308900569, 1076156248, 496158144, 1160914382, 'OR', 'Z'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1742657481, 1697703100, 1276715127, 249769402, 'OR', 'g'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1730173163, 690868494, 420676729, 1001701868, 'OR', 'K'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1113431343, 1306177470, 912447414, 39868117, 'OR', 'b'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1813999643, 1738970302, 98672809, 54100636, 'OR', 'M'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (60561891, 1229089094, 1626776049, 1373758116, 'OR', 'l'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1194099877, 966075960, 973792659, 106417526, 'WA', 'E'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (972828393, 1964561103, 715385951, 581092182, 'AZ', 'f'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1387745036, 811991623, 884849418, 924597781, 'AZ', 'B'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (4042014, 1008692733, 524884932, 755313715, 'NM', 'U'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (9035402, 85439893, 333606312, 233856899, 'UT', 'j'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (300439305, 809521660, 602721207, 1167124218, 'UT', 'C'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1261574467, 1642196402, 1309279369, 811128713, 'AZ', 'Z'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1734634260, 1893138191, 36547218, 1696588381, 'AZ', 'd'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1393860542, 207139753, 1053973916, 2015424087, 'NM', 'L'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1104007962, 703835058, 1058040433, 973595416, 'NM', 'X'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1829413354, 1360120309, 475884296, 275389923, 'NM', 'o'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (644314976, 1910013550, 770148070, 1201992324, 'AZ', 'B'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1479078753, 501738145, 2081089479, 1365686422, 'NM', 'H'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (340748485, 1276107083, 11726459, 1736071185, 'NM', 'k'); COMMIT;
查詢分區(qū)中的數據
SQL> select count(*) from accounts; COUNT(*) ---------- 29 SQL> select count(*) from accounts partition(p_northwest); COUNT(*) ---------- 16 SQL> select count(*) from accounts PARTITION (p_southwest); COUNT(*) ---------- 13 SQL> select count(*) from accounts PARTITION (p_northeast); COUNT(*) ---------- 0
分區(qū)裁剪測試
SQL> set autotrace on SQL> select count(*) from accounts where REGION='AZ'; COUNT(*) ---------- 5 Execution Plan ---------------------------------------------------------- Plan hash value: 3505378 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 14 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 3 | | | | | | 2 | PARTITION LIST SINGLE| | 5 | 15 | 14 (0)| 00:00:01 | KEY | KEY | |* 3 | TABLE ACCESS FULL | ACCOUNTS | 5 | 15 | 14 (0)| 00:00:01 | 2 | 2 | ---------------------------------------------------------------------------------------------------
由執(zhí)行計劃中可以看出，Oracle只查詢了一個分區(qū)的數據。
創(chuàng)建RANGE分區(qū)
CREATE TABLE SALES
(
PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (TIME_ID)
(
PARTITION SALES_Q1_2003 VALUES LESS THAN (TO_DATE('2003-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION SALES_Q2_2003 VALUES LESS THAN (TO_DATE('2003-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION SALES_Q3_2003 VALUES LESS THAN (TO_DATE('2003-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION SALES_Q4_2003 VALUES LESS THAN (TO_DATE('2004-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
);11g新增了一個時間間隔分區(qū)，本質上是RANGE分區(qū)的一個擴展。
CREATE TABLE SALES2
(
PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (TIME_ID) INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
(
PARTITION SALES_Q1_2003 VALUES LESS THAN (TO_DATE('2003-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
);時間間隔分區(qū)會自動根據數據維護分區(qū)。我們做如下數據插入
SET DEFINE OFF;
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(15, 519, TO_DATE('01/25/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 1003.49);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(32, 12824, TO_DATE('03/13/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 73.43);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(47, 3289, TO_DATE('01/15/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 999,
1, 29.89);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(116, 2945, TO_DATE('09/23/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 14.35);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(48, 1580, TO_DATE('12/24/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 13.13);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(19, 2671, TO_DATE('03/16/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 999,
1, 52.4);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(142, 6834, TO_DATE('02/02/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 25.4);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(33, 5579, TO_DATE('07/23/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 999,
1, 45.71);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(35, 10985, TO_DATE('08/20/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 59.78);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(28, 13125, TO_DATE('10/13/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 999,
1, 203.18);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(46, 6719, TO_DATE('10/10/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 25.83);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(23, 33729, TO_DATE('01/16/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 999,
1, 23.51);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(23, 9927, TO_DATE('03/28/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 23.19);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(118, 818, TO_DATE('01/24/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 999,
1, 8.86);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(135, 5860, TO_DATE('02/27/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 55.43);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(41, 2292, TO_DATE('04/13/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 999,
1, 48.78);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(30, 7859, TO_DATE('07/14/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 10.48);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(128, 3266, TO_DATE('07/15/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 30.07);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(145, 3461, TO_DATE('10/27/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 13.76);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(24, 9170, TO_DATE('02/07/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 351,
1, 63.97);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(118, 1156, TO_DATE('01/29/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 999,
1, 17.53);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(26, 11267, TO_DATE('06/10/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 999,
1, 156.71);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(31, 8630, TO_DATE('04/02/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 8.95);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(23, 392, TO_DATE('08/30/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 999,
1, 21.22);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(47, 280, TO_DATE('08/13/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 999,
1, 29.02);
Insert into SALES2
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, AMOUNT_SOLD)
Values
(119, 6822, TO_DATE('08/06/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 999,
1, 7.31);
COMMIT;現(xiàn)在我們查看sales2的分區(qū)情況
SQL> SELECT TABLE_NAME, PARTITION_NAME FROM dba_tab_partitions WHERE TABLE_OWNER = 'SCOTT' AND TABLE_NAME = 'SALES2'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ SALES2 SALES_Q1_2003 SALES2 SYS_P41 SALES2 SYS_P42 SALES2 SYS_P43
可見系統(tǒng)根據數據的實際情況，給我們新建了3個分區(qū)，分區(qū)由系統(tǒng)自命名。
創(chuàng)建HASH分區(qū)
CREATE TABLE sales_hash (s_productid NUMBER, s_saledate DATE, s_custid NUMBER, s_totalprice NUMBER) PARTITION BY HASH(s_productid) ( PARTITION p1 TABLESPACE users , PARTITION p2 TABLESPACE users , PARTITION p3 TABLESPACE users , PARTITION p4 TABLESPACE users );
根據s_productionid的值，算出hash值，將對于的數據行插入到相應的分區(qū)中。各分區(qū)數據要想分別均勻，分區(qū)的個數為2的次方數。
創(chuàng)建SYSTEM分區(qū)
此分區(qū)與其他的都不同，數據插入到哪個分區(qū)與數據本身無關，完全有用戶自定義。
CREATE TABLE t_system ( id NUMBER, name VARCHAR2 (30) ) PARTITION BY SYSTEM (PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4);
此時往表中直接插入數據就會報錯
SQL> insert into t_system(id,name)values(1,'kevin'); insert into t_system(id,name)values(1,'kevin') * ERROR at line 1: ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
插入數據，必須指定分區(qū)名稱
SQL> insert into t_system partition(p1)(id,name)values(1,'kevin'); 1 row created.
并且重復數據可以插入到不同的分區(qū)
SQL> insert into t_system partition(p2)(id,name)values(1,'kevin'); 1 row created.
創(chuàng)建虛擬列分區(qū)
Oracle11g新增了虛擬列功能，虛擬列的值從其他的列推導而來，Oracle只保存源數據，這個列不占存儲空間。虛擬列其中一個引申功能就是虛擬列分區(qū)功能。11g增加對虛擬列的支持，這使得分區(qū)功能更加靈活。如：表中有一個日期列，希望根據日期列進行分區(qū)，每個月份一個分區(qū)，總共12個分區(qū)，不管是哪一年的，只要是那個月，就放在那個月的分區(qū)中。
CREATE TABLE T_PARTITION_MONTH ( ID NUMBER, NAME VARCHAR2 (30), CREATE_DATE DATE, PARTITION_MONTH AS (TO_NUMBER (TO_CHAR (CREATE_DATE, 'MM'))) ) PARTITION BY LIST (PARTITION_MONTH) ( PARTITION P1 VALUES (1), PARTITION P2 VALUES (2), PARTITION P3 VALUES (3), PARTITION P4 VALUES (4), PARTITION P5 VALUES (5), PARTITION P6 VALUES (6), PARTITION P7 VALUES (7), PARTITION P8 VALUES (8), PARTITION P9 VALUES (9), PARTITION P10 VALUES (10), PARTITION P11 VALUES (11), PARTITION P12 VALUES (12));
創(chuàng)建參考列分區(qū)
子表的分區(qū)依賴于父表，我們看如下例子
CREATE TABLE orders
( order_id NUMBER(12),
order_date TIMESTAMP,
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'))
);訂單表，按order_date做范圍分區(qū)。
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);
訂單項目表，該表分區(qū)參考主表。
查看order_items的分區(qū)信息
SQL> SELECT TABLE_NAME, PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'ORDER_ITEMS'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ ORDER_ITEMS Q1_2005 ORDER_ITEMS Q2_2005 ORDER_ITEMS Q3_2005 ORDER_ITEMS Q4_2005
對于range和hash分區(qū)，你可以指定多列作為分區(qū)鍵。列數最多16個。
CREATE TABLE sales_demo ( year NUMBER, month NUMBER, day NUMBER, amount_sold NUMBER) PARTITION BY RANGE (year,month) (PARTITION before2001 VALUES LESS THAN (2001,1), PARTITION q1_2001 VALUES LESS THAN (2001,4), PARTITION q2_2001 VALUES LESS THAN (2001,7), PARTITION q3_2001 VALUES LESS THAN (2001,10), PARTITION q4_2001 VALUES LESS THAN (2002,1), PARTITION future VALUES LESS THAN (MAXVALUE,0)); REM 12-DEC-2000 INSERT INTO sales_demo VALUES(2000,12,12, 1000); REM 17-MAR-2001 INSERT INTO sales_demo VALUES(2001,3,17, 2000); REM 1-NOV-2001 INSERT INTO sales_demo VALUES(2001,11,1, 5000); REM 1-JAN-2002 INSERT INTO sales_demo VALUES(2002,1,1, 4000);
組合分區(qū)有如下六種：
RANGE-RANGE、RANGE-LIST、RANGE-HASH、LIST-RANGE、LIST-LIST、LIST-HASH 。
組合分區(qū)的創(chuàng)建與單分區(qū)類似。
創(chuàng)建RANGE-RANGE組合分區(qū)
CREATE TABLE shipments
( order_id NUMBER NOT NULL
, order_date DATE NOT NULL
, delivery_date DATE NOT NULL
, customer_id NUMBER NOT NULL
, sales_amount NUMBER NOT NULL
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (delivery_date)
( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy'))
( SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE('15-AUG-2006','dd-MON-yyyy'))
, SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
, SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
( SUBPARTITION p06_aug_e VALUES LESS THAN (TO_DATE('15-SEP-2006','dd-MON-yyyy'))
, SUBPARTITION p06_aug_a VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
( SUBPARTITION p06_sep_e VALUES LESS THAN (TO_DATE('15-OCT-2006','dd-MON-yyyy'))
, SUBPARTITION p06_sep_a VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
, SUBPARTITION p06_sep_l VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
( SUBPARTITION p06_oct_e VALUES LESS THAN (TO_DATE('15-NOV-2006','dd-MON-yyyy'))
, SUBPARTITION p06_oct_a VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
, SUBPARTITION p06_oct_l VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
( SUBPARTITION p06_nov_e VALUES LESS THAN (TO_DATE('15-DEC-2006','dd-MON-yyyy'))
, SUBPARTITION p06_nov_a VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
, SUBPARTITION p06_nov_l VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
( SUBPARTITION p06_dec_e VALUES LESS THAN (TO_DATE('15-JAN-2007','dd-MON-yyyy'))
, SUBPARTITION p06_dec_a VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy'))
, SUBPARTITION p06_dec_l VALUES LESS THAN (MAXVALUE)
)
);查看分區(qū)信息
SQL> SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'SHIPMENTS'; TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT ------------------------------ ------------------------------ ------------------ SHIPMENTS P_2006_JUL 3 SHIPMENTS P_2006_AUG 3 SHIPMENTS P_2006_SEP 3 SHIPMENTS P_2006_OCT 3 SHIPMENTS P_2006_NOV 3 SHIPMENTS P_2006_DEC 3 6 rows selected.
查看子分區(qū)信息
SQL> SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME = 'SHIPMENTS'; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ ------------------------------ SHIPMENTS P_2006_DEC P06_DEC_E SHIPMENTS P_2006_DEC P06_DEC_A SHIPMENTS P_2006_DEC P06_DEC_L SHIPMENTS P_2006_NOV P06_NOV_E SHIPMENTS P_2006_NOV P06_NOV_A SHIPMENTS P_2006_NOV P06_NOV_L SHIPMENTS P_2006_OCT P06_OCT_E SHIPMENTS P_2006_OCT P06_OCT_A SHIPMENTS P_2006_OCT P06_OCT_L SHIPMENTS P_2006_SEP P06_SEP_E SHIPMENTS P_2006_SEP P06_SEP_A TABLE_NAME PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ ------------------------------ SHIPMENTS P_2006_SEP P06_SEP_L SHIPMENTS P_2006_AUG P06_AUG_E SHIPMENTS P_2006_AUG P06_AUG_A SHIPMENTS P_2006_AUG P06_AUG_L SHIPMENTS P_2006_JUL P06_JUL_E SHIPMENTS P_2006_JUL P06_JUL_A SHIPMENTS P_2006_JUL P06_JUL_L 18 rows selected.
創(chuàng)建RANGE-LIST
CREATE TABLE quarterly_regional_sales
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
TABLESPACE ts4
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
(SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
)
);創(chuàng)建RANGE-HASH分區(qū)
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) SUBPARTITION BY HASH (cust_id)
SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);子分區(qū)也可以指定模板
如下：
CREATE TABLE sales_range_range
( 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) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY RANGE(amount_sold)
SUBPARTITION TEMPLATE
( SUBPARTITION p_low VALUES LESS THAN (1000)
, SUBPARTITION p_medium VALUES LESS THAN (4000)
, SUBPARTITION p_high VALUES LESS THAN (8000)
, SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue)
)
( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')))
或者
CREATE TABLE sales_range_range
( 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)
SUBPARTITION BY RANGE(amount_sold)
SUBPARTITION TEMPLATE
( SUBPARTITION p_low VALUES LESS THAN (1000)
, SUBPARTITION p_medium VALUES LESS THAN (4000)
, SUBPARTITION p_high VALUES LESS THAN (8000)
, SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue)
)
( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')))CREATE TABLE emp_sub_template (deptno NUMBER, empname VARCHAR(32), grade NUMBER) PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname) SUBPARTITION TEMPLATE (SUBPARTITION a TABLESPACE ts1, SUBPARTITION b TABLESPACE ts2, SUBPARTITION c TABLESPACE ts3, SUBPARTITION d TABLESPACE ts4 ) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (MAXVALUE) );
CREATE TABLE stripe_regional_sales
( deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE
(SUBPARTITION northwest VALUES ('OR', 'WA') TABLESPACE tbs_1,
SUBPARTITION southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE tbs_2,
SUBPARTITION northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE tbs_3,
SUBPARTITION southeast VALUES ('FL', 'GA') TABLESPACE tbs_4,
SUBPARTITION midwest VALUES ('SD', 'WI') TABLESPACE tbs_5,
SUBPARTITION south VALUES ('AL', 'AK') TABLESPACE tbs_6,
SUBPARTITION others VALUES (DEFAULT ) TABLESPACE tbs_7
)
(PARTITION q1_1999 VALUES LESS THAN ( TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION q3_1999 VALUES LESS THAN ( TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
);創(chuàng)建分區(qū)索引
本地索引
SQL> create index idx_accounts_number on accounts(account_number) local; Index created.
創(chuàng)建索引時，添加local關鍵字，即為表的每個分區(qū)單獨創(chuàng)建一個索引。
SQL> select index_name,partition_name from dba_ind_partitions where index_name='IDX_ACCOUNTS_NUMBER'; INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------ IDX_ACCOUNTS_NUMBER P_NORTHCENTRAL IDX_ACCOUNTS_NUMBER P_NORTHEAST IDX_ACCOUNTS_NUMBER P_NORTHWEST IDX_ACCOUNTS_NUMBER P_SOUTHCENTRAL IDX_ACCOUNTS_NUMBER P_SOUTHEAST IDX_ACCOUNTS_NUMBER P_SOUTHWEST 6 rows selected.
可見，表有幾個分區(qū)，就創(chuàng)建幾個索引分區(qū)。
全局索引
創(chuàng)建方法與普通索引一致。
SQL> create index idx_accounts_id on accounts(id); Index created.
SQL> select index_name,partition_name from dba_ind_partitions where index_name='IDX_ACCOUNTS_ID'; no rows selected
索引沒有分區(qū)。
全局分區(qū)索引
SQL> create index idx_accounts_customer_id on accounts(customer_id) global partition by hash(customer_id) partitions 4; Index created.
SQL> select index_name,partition_name from dba_ind_partitions where index_name='IDX_ACCOUNTS_CUSTOMER_ID'; INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------ IDX_ACCOUNTS_CUSTOMER_ID SYS_P61 IDX_ACCOUNTS_CUSTOMER_ID SYS_P62 IDX_ACCOUNTS_CUSTOMER_ID SYS_P63 IDX_ACCOUNTS_CUSTOMER_ID SYS_P64
三、維護分區(qū)
添加分區(qū)
LIST分區(qū)
SQL> ALTER TABLE accounts
ADD PARTITION p_nonmainland VALUES ('HI', 'PR');
Table altered.
SQL>RANGE分區(qū)
SQL> ALTER TABLE sales
ADD PARTITION sale_q1_2004
VALUES LESS THAN (TO_DATE ('2004-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
Table altered.
SQL>HASH分區(qū)
SQL> ALTER TABLE sales_hash ADD PARTITION; Table altered.
對于組合分區(qū)
SQL> ALTER TABLE shipments
ADD PARTITION p_2007_jan
VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy'));
Table altered.如果添加分區(qū)時，不指定子分區(qū)，那么系統(tǒng)默認幫你建立一個子分區(qū)
SQL> SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME = 'SHIPMENTS' and PARTITION_NAME='P_2007_JAN'; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ ------------------------------ SHIPMENTS P_2007_JAN SYS_SUBP82
添加分區(qū)時，也可以指定子分區(qū)
SQL> ALTER TABLE shipments
ADD PARTITION p_2008_jan
VALUES LESS THAN (TO_DATE('01-FEB-2008','dd-MON-yyyy'))
( SUBPARTITION p07_jan_e VALUES LESS THAN (TO_DATE('15-FEB-2007','dd-MON-yyyy'))
, SUBPARTITION p07_jan_a VALUES LESS THAN (TO_DATE('01-MAR-2007','dd-MON-yyyy'))
, SUBPARTITION p07_jan_l VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy'))
) ;
Table altered.此時子分區(qū)信息如下
SQL> SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME = 'SHIPMENTS' and PARTITION_NAME='P_2008_JAN'; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ ------------------------------ SHIPMENTS P_2008_JAN P07_JAN_E SHIPMENTS P_2008_JAN P07_JAN_A SHIPMENTS P_2008_JAN P07_JAN_L
如果組合分區(qū)的子分區(qū)指定了模板，那么添加分區(qū)時，無需指定子分區(qū)。
SQL> ALTER TABLE SALES_RANGE_RANGE
ADD PARTITION P_2000
VALUES LESS THAN (TO_DATE ('01-JAN-2001', 'dd-MON-yyyy'));
Table altered.查看分區(qū)信息
SQL> SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME = 'SALES_RANGE_RANGE' and PARTITION_NAME='P_2000'; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME ------------------------------ ------------------------------ ------------------------------ SALES_RANGE_RANGE P_2000 P_2000_P_LOW SALES_RANGE_RANGE P_2000 P_2000_P_MEDIUM SALES_RANGE_RANGE P_2000 P_2000_P_HIGH SALES_RANGE_RANGE P_2000 P_2000_P_ULTIMATE
刪除分區(qū)
Use one of the following statements to drop a table partition or subpartition:
ALTER TABLE ... DROP PARTITION to drop a table partition
ALTER TABLE ... DROP SUBPARTITION to drop a subpartition of a composite *-[range | list] partitioned table
SQL> ALTER TABLE SALES DROP PARTITION SALE_Q1_2004; Table altered.
刪除子分區(qū)
SQL> alter table sales_range_range drop subpartition P_2000_P_LOW; Table altered.
hash分區(qū)是不能被刪除的
SQL> ALTER TABLE SALES_HASH DROP PARTITION SYS_P81; ALTER TABLE SALES_HASH DROP PARTITION SYS_P81 * ERROR at line 1: ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method
hash分區(qū)需要使用COALESCE關鍵字合并分區(qū)
SQL> SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_HASH'; 2 3 TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT ------------------------------ ------------------------------ ------------------ SALES_HASH P1 0 SALES_HASH P2 0 SALES_HASH P3 0 SALES_HASH P4 0 SALES_HASH SYS_P81 0 SQL> ALTER TABLE SALES_HASH COALESCE PARTITION; Table altered. SQL> SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_HASH'; 2 3 TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT ------------------------------ ------------------------------ ------------------ SALES_HASH P1 0 SALES_HASH P2 0 SALES_HASH P3 0 SALES_HASH P4 0
刪除hash子分區(qū)用如下語法
ALTER TABLE diving MODIFY PARTITION us_locations COALESCE SUBPARTITION;
分區(qū)交換
exchange partition是用來將分區(qū)表與普通表之間做轉換。但是普通表只能和分區(qū)表中的某個分區(qū)做交換。而不能簡單的將普通表變成分區(qū)表。
CREATE TABLE t ( empno NUMBER, sal NUMBER ) PARTITION BY RANGE (sal) ( PARTITION sal_less_3000 VALUES LESS THAN (3000), PARTITION sal_between_3000_5000 VALUES LESS THAN (5000));
生成兩個普通表
SQL> create table t_part1 as select empno,sal from emp where sal<3000; Table created. SQL> create table t_part2 as select empno,sal from emp where sal>=3000 and sal<5000; Table created.
查看表的data_object_id
SQL> COL OBJECT_NAME FOR A30
SQL> COL SUBOBJECT_NAME FOR A30
SQL> select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name in ('T','T_PART1','T_PART2');
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ------------------------------ ---------- --------------
T SAL_LESS_3000 88777 88777
T 88776
T_PART2 88775 88775
T_PART1 88774 88774
T SAL_BETWEEN_3000_5000 88778 88778進行分區(qū)交換
SQL> alter table t exchange partition sal_less_3000 with table t_part1; Table altered. SQL> alter table t exchange partition sal_between_3000_5000 with table t_part2; Table altered. SQL> select count(*) from t; COUNT(*) ---------- 26
再次查看data_object_id
SQL> select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name in ('T','T_PART1','T_PART2');
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ------------------------------ ---------- --------------
T SAL_LESS_3000 88777 88774
T 88776
T_PART2 88775 88778
T_PART1 88774 88777
T SAL_BETWEEN_3000_5000 88778 88775可見t_part1、t_part2的data_object_id與分區(qū)的值發(fā)生了對調。交換分區(qū)執(zhí)行修改了數據字典的內容。效率非常高。
子分區(qū)也可以發(fā)生交換，請參考官方文檔。
分區(qū)合并
將兩個分區(qū)合并成一個分區(qū)，hash分區(qū)例外
SQL> alter table accounts merge partitions p_northcentral ,p_southcentral into partition p_central; Table altered.
如果是interval分區(qū)，可以使用如下語法
ALTER TABLE transactions
MERGE PARTITIONS FOR(TO_DATE('15-JAN-2007','dd-MON-yyyy'))
, FOR(TO_DATE('16-JAN-2007','dd-MON-yyyy'));分區(qū)拆分
LIST分區(qū)
ALTER TABLE sales_by_region
SPLIT PARTITION region_east VALUES ('CT', 'MA', 'MD')
INTO
( PARTITION region_east_1
TABLESPACE tbs2,
PARTITION region_east_2
STORAGE (INITIAL 8M))
PARALLEL 5;The literal value list for the original region_east partition was specified as:
PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')The two new partitions are:
region_east_1 with a literal value list of ('CT','MA','MD')
region_east_2 inheriting the remaining literal value list of ('NY','NH','ME','VA','PA','NJ')
范圍分區(qū)
ALTER TABLE vet_cats SPLIT PARTITION fee_katy at (100) INTO ( PARTITION fee_katy1, PARTITION fee_katy2);
假設fee_katy分區(qū)的取值為[90-110)，那么新拆分的兩個分區(qū)的取值為fee_katy1=[90-100),fee_katy2=[100-110) .
時間間隔分區(qū)
ALTER TABLE transactions
SPLIT PARTITION FOR(TO_DATE('01-MAY-2007','dd-MON-yyyy'))
AT (TO_DATE('15-MAY-2007','dd-MON-yyyy'));免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
