Oracle資料庫開發——瞭解分割槽表
分割槽就是將一個非常大的表或者索引物理地分解為多個較小的、可獨立管理的部分。
分割槽表或索引在邏輯上是一個表或一個索引,但物理上是由多個物理分割槽組成的。
分割槽功能透過改善可管理性、效能、可用性,為各種應用系統帶來了極大的好處。
分割槽功能的好處:
1.增強資料可用性:如果表的一個分割槽因故障或者維護而不能使用時,表的其餘分割槽仍是可用的;
2.維護方便:獨立管理多個分割槽,比維護單個大表要輕鬆;
3.均衡I/O:可以把不同分割槽對映到磁碟以平衡I/O,顯著改善效能;
4.改善查詢效能:對已分割槽物件的某些查詢可以執行更快,因為搜尋僅限於關心的分割槽;
分割槽表有哪些??
Oracle 11g 提供6種表分割槽方法:範圍分割槽(range)、雜湊分割槽(hash)、列表分割槽(list)、符合分割槽、間隔分割槽、引用分割槽。
按表中某個列值的範圍進行分割槽,根據該列的值決定將資料儲存在哪個分割槽上。
建立範圍分割槽需注意以下幾點:
1.指明分割槽方法,分割槽列,和分割槽描述
2.每一個分割槽都有values less than子句
3.在最高分割槽中定義maxvalue,這個maxvalue值高區其他分割槽中的任何鍵值。
例:建立範圍分割槽
create table range_orders
(order_id varchar2(10) constraint OR_PK primary key
,order_date date default sysdate
,qty integer
,payterms varchar2(10)
,book_id number(6)
)
partition by range (order_date)
(partition p1 values less than (to_date('20140331','yyyymmdd')) tablespace user01,
partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02,
partition p3 values less than (to_date('20140531','yyyymmdd')) tablespace user03
)
;
SQL> insert into range_orders values ('10001',to_date('20140321','yyyymmdd'),1,'payterm_1',110345);
1 row inserted
SQL> insert into range_orders values ('10002',to_date('20140421','yyyymmdd'),1,'payterm_2',110745);
1 row inserted
SQL> insert into range_orders values ('10003',to_date('20140521','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
SQL> commit;
Commit complete
SQL> select rowid,r.* from range_orders r;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADwpAAGAAAACFAAA 10001 2014/3/21 1 payterm_1 110345
AAADwqAAHAAAACFAAA 10002 2014/4/21 1 payterm_2 110745
AAADwrAAIAAAACFAAA 10003 2014/5/21 1 payterm_3 110945
---
可以看到AAG,AAH,AAI 分別代表了三條資料的檔案號是6,7,8
備註:這個地方可以看下http://blog.itpub.net/28929558/viewspace-1150766/ 瞭解rowid
驗證下
SQL> select x.FILE#,x.NAME from v$datafile x;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 D:\ORACLE\ORADATA\CRISS_DB\SYSTEM01.DBF
2 D:\ORACLE\ORADATA\CRISS_DB\SYSAUX01.DBF
3 D:\ORACLE\ORADATA\CRISS_DB\UNDOTBS01.DBF
4 D:\ORACLE\ORADATA\CRISS_DB\USERS01.DBF
5 D:\ORACLE\ORADATA\CRISS_DB\TEST01.DBF
6 D:\ORACLE\ORADATA\CRISS_DB\USER01.DBF
7 D:\ORACLE\ORADATA\CRISS_DB\USER02.DBF
8 D:\ORACLE\ORADATA\CRISS_DB\USER03.DBF
8 rows selected
雜湊分割槽指一個或多個列上應用一個雜湊函式,數根據該雜湊值存放在不同的分割槽中。
透過雜湊分割槽,可以將資料比較均勻地分佈到各個分割槽中。
例:建立雜湊分割槽表
SQL> create table hash_orders
2 (order_id varchar2(10) constraint HOR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by hash(order_id)
9 ( partition hash_p1 tablespace user01
10 ,partition hash_p2 tablespace user02
11 );
Table created
SQL> insert into hash_orders select * from range_orders;
3 rows inserted
SQL> select rowid,h.* from hash_orders h;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyIAAGAAAACNAAA 10002 2014/4/21 1 payterm_2 110745
AAADyIAAGAAAACNAAB 10003 2014/5/21 1 payterm_3 110945
AAADyJAAHAAAACNAAA 10001 2014/3/21 1 payterm_1 110345
order_id為 10002,10003的被存在AAG 6號檔案上,第141個塊上的 第1行和第2行
10001 存放在AAH 7號檔案上
當表中某列的值只有幾個的時候,可以採用列表分割槽,即指定在幾個(根據列值個數)表空間中。
例:
SQL> create table list_orders
2 (order_id varchar2(10) constraint LOR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by list(payterms)
9 ( partition list_p1 values('payterm_1') tablespace user01
10 ,partition list_p2 values('payterm_2')tablespace user02
11 ,partition list_p3 values('payterm_3') tablespace user03
12 );
Table created
SQL> select rowid,l.* from list_orders l;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyRAAGAAAACdAAA 10001 2014/3/21 1 payterm_1 110345
AAADySAAHAAAACdAAA 10002 2014/4/21 1 payterm_2 110745
AAADyTAAIAAAACVAAA 10003 2014/5/21 1 payterm_3 110945
基於範圍分割槽和列表分割槽的組合 或 範圍分割槽和雜湊分割槽的組合
SQL> create table comp_orders
2 (order_id varchar2(10) constraint COR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 subpartition by list(payterms)
10 (partition p1 values less than (to_date('20140331','yyyymmdd'))
11 ( subpartition p1_sub1 values('payterm_1') tablespace user01
12 ,subpartition p1_sub2 values('payterm_2') tablespace user02
13 ,subpartition p1_sub3 values('payterm_3') tablespace user03
14 )
15 ,partition p2 values less than (to_date('20140430','yyyymmdd'))
16 ( subpartition p2_sub1 values('payterm_1') tablespace user04
17 ,subpartition p2_sub2 values('payterm_2') tablespace user05
18 ,subpartition p2_sub3 values('payterm_3') tablespace user06
19 )
20 ,partition p3 values less than (maxvalue) tablespace user07
21 )
22 ;
Table created
SQL> select rowid,c.* from comp_orders c;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyZAAGAAAAClAAA 10004 2014/3/21 1 payterm_1 110345
AAADyZAAGAAAAClAAB 10001 2014/3/21 1 payterm_1 110345
AAADydAAKAAAACFAAA 10005 2014/4/21 1 payterm_2 110745
AAADydAAKAAAACFAAB 10002 2014/4/21 1 payterm_2 110745
AAADyfAAMAAAACFAAA 10006 2014/5/21 1 payterm_3 110945
AAADyfAAMAAAACFAAB 10003 2014/5/21 1 payterm_3 110945
6 rows selected
PAYTERMS欄位值相同,且ORDER_DATE時間區間相同的資料被放到同一個表空間下面。
下面對比下PAYTERMS欄位值相同,ORDER_DATE時間區間不同的結果
SQL> insert into comp_orders values('10007',to_date('20140421','yyyymmdd'),1,'payterm_1','113888');
1 row inserted
SQL> select rowid,c.* from comp_orders c;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyZAAGAAAAClAAA 10004 2014/3/21 1 payterm_1 110345
AAADyZAAGAAAAClAAB 10001 2014/3/21 1 payterm_1 110345
AAADycAAJAAAACFAAA 10007 2014/4/21 1 payterm_1 113888
AAADydAAKAAAACFAAA 10005 2014/4/21 1 payterm_2 110745
AAADydAAKAAAACFAAB 10002 2014/4/21 1 payterm_2 110745
AAADyfAAMAAAACFAAA 10006 2014/5/21 1 payterm_3 110945
AAADyfAAMAAAACFAAB 10003 2014/5/21 1 payterm_3 110945
7 rows selected
ORDER_ID為10007的資料存放在了另一個表空間內(AAJ AAADyc[AAJ]AAAACFAAA)
間隔分割槽時oracle 11g release 1 以後版本中新增的特性,它是對範圍分割槽的擴充套件,可以自動進行等距離範圍的分割槽。
實驗對比一下,間隔分割槽與範圍分割槽有什麼區別即可。
首先建立一個間隔分割槽,間隔分割槽以一個範圍分割槽為‘起點’,並定義一個間隔,當有資料插入,依據該間隔為附加的資料建立新的分割槽。
SQL> create table intvl_orders
2 (order_id varchar2(10) constraint INO_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 interval(numtoyminterval(1,'MONTH'))
10 store in (user01,user02,user03)
11 (
12 partition p1 values less than (to_date('20140101','yyyymmdd'))
13 )
14 ;
Table created
注意:partition p1 values less than (to_date('20140101','yyyymmdd')) 起始是月初,不然會報錯 ORA-14767: 無法使用現有上限指定此間隔
至於為什麼,大家自行去查詢下答案吧。
重新建立一個範圍分割槽表並插入資料!
SQL> drop table range_orders purge;
Table dropped
SQL>
SQL> create table range_orders
2 (order_id varchar2(10) constraint OR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 (partition p1 values less than (to_date('20140331','yyyymmdd')) tablespace user01,
10 partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02,
11 partition p3 values less than (to_date('20140531','yyyymmdd')) tablespace user03
12 )
13 ;
Table created
SQL> insert into range_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945);
insert into range_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945)
ORA-14400: 插入的分割槽關鍵字未對映到任何分割槽
SQL> insert into range_orders values ('10003',to_date('20140521','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
可以看到,新建立的範圍分割槽表最大範圍是20140531,當出入的資料超出分割槽範圍就會報錯,需要手工去做分割槽擴充套件。
下面再給間隔分割槽插入資料
SQL> insert into intvl_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
我們插入用樣超出分割槽範圍的資料,資料插入成功了!
我們看一下前後兩次的建表語句有什麼變化
剛剛建好間隔分割槽表時候oracle內的建表語句:
-- Create table
create table INTVL_ORDERS
(
ORDER_ID VARCHAR2(10) not null,
ORDER_DATE DATE default sysdate,
QTY INTEGER,
PAYTERMS VARCHAR2(10),
BOOK_ID NUMBER(6)
)
partition by range (ORDER_DATE)
(
partition P1 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table INTVL_ORDERS
add constraint INO_PK primary key (ORDER_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
插入資料後間隔分割槽表時候oracle內的建表語句:
-- Create table
create table INTVL_ORDERS
(
ORDER_ID VARCHAR2(10) not null,
ORDER_DATE DATE default sysdate,
QTY INTEGER,
PAYTERMS VARCHAR2(10),
BOOK_ID NUMBER(6)
)
partition by range (ORDER_DATE)
(
partition P1 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P22 values less than (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USER01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table INTVL_ORDERS
add constraint INO_PK primary key (ORDER_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
結果自動擴充套件了一個分割槽!這也就是間隔分割槽的特點。
間隔分割槽會根據起始區間和一個間隔(之前我們設定的是一個月),插入資料時,會建立相應的分割槽,而不會因資料超出分割槽範圍導致語句執行失敗。
引用分割槽透過父表繼承分割槽鍵,可以在邏輯上均分具有分子關係的表。分割槽鍵透過現有的父子關係解析,由現行的主鍵或外來鍵約束實施。
SQL> create table range_books
2 (bookid number(6) primary key
3 ,bookname varchar2(40) not null
4 ,booktime date default sysdate
5 )
6 partition by range(booktime)
7 (
8 partition p1 values less than (to_date('20140331','yyyymmdd'))
9 ,partition p2 values less than (to_date('20140430','yyyymmdd'))
10 );
Table created
SQL> create table ref_order
2 (order_id varchar2(20) constraint RFO_PK primary key
3 ,order_date date default sysdate
4 ,book_id number(6) not null
5 ,constraint RFO_FK foreign key (book_id) references range_books(bookid)
6 )
7 partition by reference (RFO_FK)
8 ;
Table created
上面的實驗,透過使用外來鍵發現分割槽機制,外來鍵RFO_PK指向父表range_books,因此,子表ref_orders就按照父表的分割槽方式進行相應的分割槽。
注意:建立引用分割槽表時,透過partition by reference 子句指定分割槽方法,其後括號中指定分割槽使用的約束。
外來鍵約束引用的列必須具有not null約束。
=====================================================================================================================
分割槽表維護:
主要的就是 刪除分割槽、增加分割槽、合併分割槽、移動分割槽、重新命名、截斷分割槽
刪除分割槽: alter table range_orders drop partition p2;
增加分割槽: alter table range_orders add partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02;
合併分割槽: alter table range_orders merge partition p1,p2 into partition p2;
移動分割槽: alter table range_orders move partition p2 tablespace user05;
重新命名: alter table range_orders rename partition p2 to p1;
截斷分割槽: alter table range_orders truncate partition p1;
分割槽表或索引在邏輯上是一個表或一個索引,但物理上是由多個物理分割槽組成的。
分割槽功能透過改善可管理性、效能、可用性,為各種應用系統帶來了極大的好處。
分割槽功能的好處:
1.增強資料可用性:如果表的一個分割槽因故障或者維護而不能使用時,表的其餘分割槽仍是可用的;
2.維護方便:獨立管理多個分割槽,比維護單個大表要輕鬆;
3.均衡I/O:可以把不同分割槽對映到磁碟以平衡I/O,顯著改善效能;
4.改善查詢效能:對已分割槽物件的某些查詢可以執行更快,因為搜尋僅限於關心的分割槽;
分割槽表有哪些??
Oracle 11g 提供6種表分割槽方法:範圍分割槽(range)、雜湊分割槽(hash)、列表分割槽(list)、符合分割槽、間隔分割槽、引用分割槽。
按表中某個列值的範圍進行分割槽,根據該列的值決定將資料儲存在哪個分割槽上。
建立範圍分割槽需注意以下幾點:
1.指明分割槽方法,分割槽列,和分割槽描述
2.每一個分割槽都有values less than子句
3.在最高分割槽中定義maxvalue,這個maxvalue值高區其他分割槽中的任何鍵值。
例:建立範圍分割槽
create table range_orders
(order_id varchar2(10) constraint OR_PK primary key
,order_date date default sysdate
,qty integer
,payterms varchar2(10)
,book_id number(6)
)
partition by range (order_date)
(partition p1 values less than (to_date('20140331','yyyymmdd')) tablespace user01,
partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02,
partition p3 values less than (to_date('20140531','yyyymmdd')) tablespace user03
)
;
SQL> insert into range_orders values ('10001',to_date('20140321','yyyymmdd'),1,'payterm_1',110345);
1 row inserted
SQL> insert into range_orders values ('10002',to_date('20140421','yyyymmdd'),1,'payterm_2',110745);
1 row inserted
SQL> insert into range_orders values ('10003',to_date('20140521','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
SQL> commit;
Commit complete
SQL> select rowid,r.* from range_orders r;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADwpAAGAAAACFAAA 10001 2014/3/21 1 payterm_1 110345
AAADwqAAHAAAACFAAA 10002 2014/4/21 1 payterm_2 110745
AAADwrAAIAAAACFAAA 10003 2014/5/21 1 payterm_3 110945
---
可以看到AAG,AAH,AAI 分別代表了三條資料的檔案號是6,7,8
備註:這個地方可以看下http://blog.itpub.net/28929558/viewspace-1150766/ 瞭解rowid
驗證下
SQL> select x.FILE#,x.NAME from v$datafile x;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 D:\ORACLE\ORADATA\CRISS_DB\SYSTEM01.DBF
2 D:\ORACLE\ORADATA\CRISS_DB\SYSAUX01.DBF
3 D:\ORACLE\ORADATA\CRISS_DB\UNDOTBS01.DBF
4 D:\ORACLE\ORADATA\CRISS_DB\USERS01.DBF
5 D:\ORACLE\ORADATA\CRISS_DB\TEST01.DBF
6 D:\ORACLE\ORADATA\CRISS_DB\USER01.DBF
7 D:\ORACLE\ORADATA\CRISS_DB\USER02.DBF
8 D:\ORACLE\ORADATA\CRISS_DB\USER03.DBF
8 rows selected
雜湊分割槽指一個或多個列上應用一個雜湊函式,數根據該雜湊值存放在不同的分割槽中。
透過雜湊分割槽,可以將資料比較均勻地分佈到各個分割槽中。
例:建立雜湊分割槽表
SQL> create table hash_orders
2 (order_id varchar2(10) constraint HOR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by hash(order_id)
9 ( partition hash_p1 tablespace user01
10 ,partition hash_p2 tablespace user02
11 );
Table created
SQL> insert into hash_orders select * from range_orders;
3 rows inserted
SQL> select rowid,h.* from hash_orders h;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyIAAGAAAACNAAA 10002 2014/4/21 1 payterm_2 110745
AAADyIAAGAAAACNAAB 10003 2014/5/21 1 payterm_3 110945
AAADyJAAHAAAACNAAA 10001 2014/3/21 1 payterm_1 110345
order_id為 10002,10003的被存在AAG 6號檔案上,第141個塊上的 第1行和第2行
10001 存放在AAH 7號檔案上
當表中某列的值只有幾個的時候,可以採用列表分割槽,即指定在幾個(根據列值個數)表空間中。
例:
SQL> create table list_orders
2 (order_id varchar2(10) constraint LOR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by list(payterms)
9 ( partition list_p1 values('payterm_1') tablespace user01
10 ,partition list_p2 values('payterm_2')tablespace user02
11 ,partition list_p3 values('payterm_3') tablespace user03
12 );
Table created
SQL> select rowid,l.* from list_orders l;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyRAAGAAAACdAAA 10001 2014/3/21 1 payterm_1 110345
AAADySAAHAAAACdAAA 10002 2014/4/21 1 payterm_2 110745
AAADyTAAIAAAACVAAA 10003 2014/5/21 1 payterm_3 110945
基於範圍分割槽和列表分割槽的組合 或 範圍分割槽和雜湊分割槽的組合
SQL> create table comp_orders
2 (order_id varchar2(10) constraint COR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 subpartition by list(payterms)
10 (partition p1 values less than (to_date('20140331','yyyymmdd'))
11 ( subpartition p1_sub1 values('payterm_1') tablespace user01
12 ,subpartition p1_sub2 values('payterm_2') tablespace user02
13 ,subpartition p1_sub3 values('payterm_3') tablespace user03
14 )
15 ,partition p2 values less than (to_date('20140430','yyyymmdd'))
16 ( subpartition p2_sub1 values('payterm_1') tablespace user04
17 ,subpartition p2_sub2 values('payterm_2') tablespace user05
18 ,subpartition p2_sub3 values('payterm_3') tablespace user06
19 )
20 ,partition p3 values less than (maxvalue) tablespace user07
21 )
22 ;
Table created
SQL> select rowid,c.* from comp_orders c;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyZAAGAAAAClAAA 10004 2014/3/21 1 payterm_1 110345
AAADyZAAGAAAAClAAB 10001 2014/3/21 1 payterm_1 110345
AAADydAAKAAAACFAAA 10005 2014/4/21 1 payterm_2 110745
AAADydAAKAAAACFAAB 10002 2014/4/21 1 payterm_2 110745
AAADyfAAMAAAACFAAA 10006 2014/5/21 1 payterm_3 110945
AAADyfAAMAAAACFAAB 10003 2014/5/21 1 payterm_3 110945
6 rows selected
PAYTERMS欄位值相同,且ORDER_DATE時間區間相同的資料被放到同一個表空間下面。
下面對比下PAYTERMS欄位值相同,ORDER_DATE時間區間不同的結果
SQL> insert into comp_orders values('10007',to_date('20140421','yyyymmdd'),1,'payterm_1','113888');
1 row inserted
SQL> select rowid,c.* from comp_orders c;
ROWID ORDER_ID ORDER_DATE QTY PAYTERMS BOOK_ID
------------------ ---------- ----------- --------------------------------------- ---------- -------
AAADyZAAGAAAAClAAA 10004 2014/3/21 1 payterm_1 110345
AAADyZAAGAAAAClAAB 10001 2014/3/21 1 payterm_1 110345
AAADycAAJAAAACFAAA 10007 2014/4/21 1 payterm_1 113888
AAADydAAKAAAACFAAA 10005 2014/4/21 1 payterm_2 110745
AAADydAAKAAAACFAAB 10002 2014/4/21 1 payterm_2 110745
AAADyfAAMAAAACFAAA 10006 2014/5/21 1 payterm_3 110945
AAADyfAAMAAAACFAAB 10003 2014/5/21 1 payterm_3 110945
7 rows selected
ORDER_ID為10007的資料存放在了另一個表空間內(AAJ AAADyc[AAJ]AAAACFAAA)
間隔分割槽時oracle 11g release 1 以後版本中新增的特性,它是對範圍分割槽的擴充套件,可以自動進行等距離範圍的分割槽。
實驗對比一下,間隔分割槽與範圍分割槽有什麼區別即可。
首先建立一個間隔分割槽,間隔分割槽以一個範圍分割槽為‘起點’,並定義一個間隔,當有資料插入,依據該間隔為附加的資料建立新的分割槽。
SQL> create table intvl_orders
2 (order_id varchar2(10) constraint INO_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 interval(numtoyminterval(1,'MONTH'))
10 store in (user01,user02,user03)
11 (
12 partition p1 values less than (to_date('20140101','yyyymmdd'))
13 )
14 ;
Table created
注意:partition p1 values less than (to_date('20140101','yyyymmdd')) 起始是月初,不然會報錯 ORA-14767: 無法使用現有上限指定此間隔
至於為什麼,大家自行去查詢下答案吧。
重新建立一個範圍分割槽表並插入資料!
SQL> drop table range_orders purge;
Table dropped
SQL>
SQL> create table range_orders
2 (order_id varchar2(10) constraint OR_PK primary key
3 ,order_date date default sysdate
4 ,qty integer
5 ,payterms varchar2(10)
6 ,book_id number(6)
7 )
8 partition by range (order_date)
9 (partition p1 values less than (to_date('20140331','yyyymmdd')) tablespace user01,
10 partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02,
11 partition p3 values less than (to_date('20140531','yyyymmdd')) tablespace user03
12 )
13 ;
Table created
SQL> insert into range_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945);
insert into range_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945)
ORA-14400: 插入的分割槽關鍵字未對映到任何分割槽
SQL> insert into range_orders values ('10003',to_date('20140521','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
可以看到,新建立的範圍分割槽表最大範圍是20140531,當出入的資料超出分割槽範圍就會報錯,需要手工去做分割槽擴充套件。
下面再給間隔分割槽插入資料
SQL> insert into intvl_orders values ('10003',to_date('20140621','yyyymmdd'),1,'payterm_3',110945);
1 row inserted
我們插入用樣超出分割槽範圍的資料,資料插入成功了!
我們看一下前後兩次的建表語句有什麼變化
剛剛建好間隔分割槽表時候oracle內的建表語句:
-- Create table
create table INTVL_ORDERS
(
ORDER_ID VARCHAR2(10) not null,
ORDER_DATE DATE default sysdate,
QTY INTEGER,
PAYTERMS VARCHAR2(10),
BOOK_ID NUMBER(6)
)
partition by range (ORDER_DATE)
(
partition P1 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table INTVL_ORDERS
add constraint INO_PK primary key (ORDER_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
插入資料後間隔分割槽表時候oracle內的建表語句:
-- Create table
create table INTVL_ORDERS
(
ORDER_ID VARCHAR2(10) not null,
ORDER_DATE DATE default sysdate,
QTY INTEGER,
PAYTERMS VARCHAR2(10),
BOOK_ID NUMBER(6)
)
partition by range (ORDER_DATE)
(
partition P1 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P22 values less than (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace USER01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table INTVL_ORDERS
add constraint INO_PK primary key (ORDER_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
結果自動擴充套件了一個分割槽!這也就是間隔分割槽的特點。
間隔分割槽會根據起始區間和一個間隔(之前我們設定的是一個月),插入資料時,會建立相應的分割槽,而不會因資料超出分割槽範圍導致語句執行失敗。
引用分割槽透過父表繼承分割槽鍵,可以在邏輯上均分具有分子關係的表。分割槽鍵透過現有的父子關係解析,由現行的主鍵或外來鍵約束實施。
SQL> create table range_books
2 (bookid number(6) primary key
3 ,bookname varchar2(40) not null
4 ,booktime date default sysdate
5 )
6 partition by range(booktime)
7 (
8 partition p1 values less than (to_date('20140331','yyyymmdd'))
9 ,partition p2 values less than (to_date('20140430','yyyymmdd'))
10 );
Table created
SQL> create table ref_order
2 (order_id varchar2(20) constraint RFO_PK primary key
3 ,order_date date default sysdate
4 ,book_id number(6) not null
5 ,constraint RFO_FK foreign key (book_id) references range_books(bookid)
6 )
7 partition by reference (RFO_FK)
8 ;
Table created
上面的實驗,透過使用外來鍵發現分割槽機制,外來鍵RFO_PK指向父表range_books,因此,子表ref_orders就按照父表的分割槽方式進行相應的分割槽。
注意:建立引用分割槽表時,透過partition by reference 子句指定分割槽方法,其後括號中指定分割槽使用的約束。
外來鍵約束引用的列必須具有not null約束。
=====================================================================================================================
分割槽表維護:
主要的就是 刪除分割槽、增加分割槽、合併分割槽、移動分割槽、重新命名、截斷分割槽
刪除分割槽: alter table range_orders drop partition p2;
增加分割槽: alter table range_orders add partition p2 values less than (to_date('20140430','yyyymmdd')) tablespace user02;
合併分割槽: alter table range_orders merge partition p1,p2 into partition p2;
移動分割槽: alter table range_orders move partition p2 tablespace user05;
重新命名: alter table range_orders rename partition p2 to p1;
截斷分割槽: alter table range_orders truncate partition p1;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28929558/viewspace-1153474/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 詳解ORACLE資料庫的分割槽表Oracle資料庫
- 分割槽表入無分割槽的資料庫資料庫
- Oracle資料庫中分割槽表的操作方法詳解Oracle資料庫
- Oracle資料庫開發——瞭解索引Oracle資料庫索引
- 分割槽表匯入資料庫資料庫
- Oracle資料庫中分割槽表的操作方法Oracle資料庫
- Oracle資料庫開發——瞭解rowidOracle資料庫
- oracle 分割槽表詳解Oracle
- oracle表分割槽詳解Oracle
- oracle分割槽表詳解Oracle
- Oracle 表分割槽詳解Oracle
- 資料庫分割槽表 什麼情況下需要分割槽資料庫
- Oracle 12cr2 資料庫之間傳輸表,分割槽或子分割槽Oracle資料庫
- Oracle資料庫中分割槽表的操作方法(轉)Oracle資料庫
- ORACLE刪除-表分割槽和資料Oracle
- oracle分割槽表和分割槽表exchangeOracle
- Oracle分割槽表及分割槽索引Oracle索引
- oracle分割槽表和非分割槽表exchangeOracle
- Oracle資料庫表範圍分割槽策略測試過程Oracle資料庫
- 匯入匯出 Oracle 分割槽表資料Oracle
- Oracle 表分割槽Oracle
- oracle分割槽表Oracle
- oracle表分割槽Oracle
- Oracle 分割槽表Oracle
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- 插入遠端資料庫資料遇到分割槽表bug資料庫
- Oracle查詢資料庫中所有表和分割槽表的記錄數Oracle資料庫
- Oracle資料庫分割槽表SPLIT操作導致歸檔瘋漲Oracle資料庫
- Oracle 12cr2 資料庫之間跨網路傳輸表,分割槽或子分割槽Oracle資料庫
- zabbix上對mysql資料庫做分割槽表MySql資料庫
- Oracle查詢Interval partition分割槽表內資料Oracle
- Oracle 建立分割槽表Oracle
- ORACLE分割槽表管理Oracle
- ZT oracle 分割槽表資料定期遷移到其他資料庫測試方案Oracle資料庫
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- 資料庫分割槽表分割槽未分配導致的一些問題資料庫
- 全面剖析Oracle資料庫中的分割槽功能Oracle資料庫
- 自動備份、截斷分割槽表分割槽資料