手工生成方法為PARTITION BY子句中列示的每個(gè)范圍創(chuàng)建一個(gè)新數(shù)據(jù)分區(qū)?這種語(yǔ)法格式提高了定義范圍時(shí)的靈活性,從而增加了數(shù)據(jù)和LOB布置選項(xiàng)?對(duì)清單12所創(chuàng)建的示例分區(qū)表TEST3,如果用手工方式創(chuàng)建,可以在DB2CLP窗口中,連上數(shù)據(jù)庫(kù),先發(fā)出DROP TABLE命令,刪除示例分區(qū)表TEST3,再發(fā)出CREATE TABLE命令創(chuàng)建示例分區(qū)表TEST3,其分區(qū)鍵RQ允許錄入的時(shí)間段為“2007-01-01”到“2007-12-31”,具體如清單13所示:
- - 清單13 .手工創(chuàng)建示例分區(qū)表TEST3
C:\> DB2 CONNECT TO DB2TEST1
數(shù)據(jù)庫(kù)連接信息
數(shù)據(jù)庫(kù)服務(wù)器 = DB2 / NT 9.1.0
SQL 授權(quán)標(biāo)識(shí) = RHETTE
本地?cái)?shù)據(jù)庫(kù)別名 = DB2TEST1
C:\> db2 drop table test3
DB20000I SQL命令成功完成?
C:\> db2 CREATE TABLE TEST3 ( ID INTEGER NOT NULL , RQ DATE NOT NULL , IMAGE BLOB (1 M ) NOT NULL LOGGED NOT COMPACT ) PARTITION BY RANGE (RQ NULLS LAST) (PARTITION DATAPATITION1 STARTING FROM ('2007-01-01') INCLUSIVE ENDING AT ('2007-02-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION2 STARTING FROM ('2007-02-01') INCLUSIVE ENDING AT ('2007-03-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION3 STARTING FROM ('2007-03-01') INCLUSIVE ENDING AT ('2007-04-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION4 STARTING FROM ('2007-04-01') INCLUSIVE ENDING AT ('2007-05-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION5 STARTING FROM ('2007-05-01') INCLUSIVE ENDING AT ('2007-06-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 ,PARTITION DATAPATITION6 STARTING FROM ('2007-06-1') INCLUSIVE ENDING AT ('2007-07-1') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION7 STARTING FROM ('2007-07-01') INCLUSIVE ENDING AT ('2007-08-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION8 STARTING FROM ('2007-08-01') INCLUSIVE ENDING AT ('2007-09-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION9 STARTING FROM ('2007-09-01') INCLUSIVE ENDING AT ('2007-10-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION10 STARTING FROM ('2007-10-01') INCLUSIVE ENDING AT ('2007-11-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION11 STARTING FROM ('2007-11-01') INCLUSIVE ENDING AT ('2007-12-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION12 STARTING FROM ('2007-12-01') INCLUSIVE ENDING AT ('2007-12-31') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3) INDEX IN USERSPACE1
DB20000I SQL命令成功完成?
這樣我們通過(guò)手工方式創(chuàng)建了示例分區(qū)表TEST3,用來(lái)對(duì)表數(shù)據(jù)進(jìn)行分區(qū)的列是RQ,表數(shù)據(jù)將存儲(chǔ)在下列表空間中:TABLESPACE1,TABLESPACE2和TABLESPACE3?缺省情況下,索引將存儲(chǔ)在以下表空間中:USERSPACE1?此語(yǔ)句生成12個(gè)數(shù)據(jù)分區(qū),每個(gè)數(shù)據(jù)分區(qū)包含1個(gè)鍵值:
? (RQ) >= ( ' 2007-01-01 ' ) , (RQ) < (' 2007-02-01 ' )
? (RQ) >= ( ' 2007-02-01 ' ) , (RQ) < (' 2007-03-01 ' )
? . . . . . .
? (RQ) >= ( ' 2007-12-01 ' ) , (RQ) <= (' 2007-12-31 ' )
每個(gè)數(shù)據(jù)分區(qū)通過(guò)PARTITION命令進(jìn)行了命名,名字分別DATAPARTITION1,DATAPARTITION2, . . . . . . DATAPARTITION12?
接下來(lái)我們對(duì)示例分區(qū)表TEST3插入數(shù)據(jù),在DB2CLP窗口中,發(fā)出INSERT INTO命令,具體如清單14所示:
- - 清單14 .對(duì)示例分區(qū)表TEST3插入一條記錄
C:\> DB2 INSERT INTO TEST3 VALUES ( 1 , '2007-01-10' , blob ( 'the first rows' ) )
DB20000I SQL命令成功完成?
命令成功完成,把行插入示例分區(qū)表時(shí),根據(jù)該行的鍵值以及它所處的范圍自動(dòng)將其放入正確的數(shù)據(jù)分區(qū),這樣我們數(shù)據(jù)就插入到了數(shù)據(jù)分區(qū)DATAPARTITION1中了?如果該行處于對(duì)該表定義的所有范圍之外,插入就會(huì)失敗,并且將把以下錯(cuò)誤返回給應(yīng)用程序,比如我們插入一條如清單15所示的記錄,RQ的值為'2006-01-10',不在'2007-01-01'和'2007-12-31'的范圍內(nèi):
- - 清單15 .對(duì)示例分區(qū)表TEST3插入一條分區(qū)鍵范圍外的記錄
C:\> DB2 INSERT INTO TEST3 VALUES (1 , '2006-01-10' , blob ( 'the first rows' ) )
DB21034E 該命令被當(dāng)作 SQL語(yǔ)句來(lái)處理,因?yàn)樗皇怯行У?#8220;命令行處理器”命令｡在 SQL 處理期間,它返回:
SQL0327N 無(wú)法將行插入表 " RHETTE.TEST3 " 中,因?yàn)樗诙x的數(shù)據(jù)分區(qū)范圍之外｡
SQLSTATE = 22525
此時(shí)我們對(duì)查看示例分區(qū)表中的記錄,通過(guò)在WHERE子句中指定分區(qū)鍵的值,可以能夠避免掃描不相關(guān)的數(shù)據(jù),直接從DATAPARTITION1數(shù)據(jù)分區(qū)中得到想要的記錄,從而提高查詢處理性能,具體如清單16所示:
- - 清單16 .查詢示例分區(qū)表TEST3
C:\> db2 select id,rq from test3 where rq='2007-01-10'
ID RQ
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1 2007-01-10
1 條記錄已選擇｡
如果想在范圍之間允許存在間隔,可以使用MINVALUE和MAXVALUE指定間隔的范圍,MINVALUE表示最小的值,就是比你插入的任何值都會(huì)小的意思,MAXVALUE表示最大的值,就是比你插入的任何值都要大的意思?對(duì)示例分區(qū)表TEST3,我們可以增量?jī)蓚€(gè)數(shù)據(jù)分區(qū):
? 數(shù)據(jù)分區(qū)OTHERS1,范圍從MINVALUE到 ” 2007-01-01” ,不包含” 2007-01-01”;所有比” 2007-01-01 ”小的值,都會(huì)放入到此分區(qū)中?
? 數(shù)據(jù)分區(qū)OTHERS2,范圍從 ” 2008-01-01” 到MAXVALUE,包含” 2008-01-01”;所有比 ” 2008-01-01” 大的值,都會(huì)放入到此分區(qū)中?
具體如清單17中藍(lán)色標(biāo)記部分所示:
- - 清單17 .手工創(chuàng)建示例分區(qū)表TEST3
C:\> db2 drop table test3
DB20000I SQL命令成功完成?
C:\> db2 CREATE TABLE TEST3 ( ID INTEGER NOT NULL , RQ DATE NOT NULL , IMAGE BLOB (1 M ) NOT NULL LOGGED NOT COMPACT ) PARTITION BY RANGE (RQ NULLS LAST) (PARTITION DATAPATITION1 STARTING FROM ('2007-01-01') INCLUSIVE ENDING AT ('2007-02-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION2 STARTING FROM ('2007-02-01') INCLUSIVE ENDING AT ('2007-03-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION3 STARTING FROM ('2007-03-01') INCLUSIVE ENDING AT ('2007-04-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION4 STARTING FROM ('2007-04-01') INCLUSIVE ENDING AT ('2007-05-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION5 STARTING FROM ('2007-05-01') INCLUSIVE ENDING AT ('2007-06-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 ,PARTITION DATAPATITION6 STARTING FROM ('2007-06-1') INCLUSIVE ENDING AT ('2007-07-1') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION7 STARTING FROM ('2007-07-01') INCLUSIVE ENDING AT ('2007-08-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION8 STARTING FROM ('2007-08-01') INCLUSIVE ENDING AT ('2007-09-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION9 STARTING FROM ('2007-09-01') INCLUSIVE ENDING AT ('2007-10-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION10 STARTING FROM ('2007-10-01') INCLUSIVE ENDING AT ('2007-11-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION11 STARTING FROM ('2007-11-01') INCLUSIVE ENDING AT ('2007-12-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION12 STARTING FROM ('2007-12-01') INCLUSIVE ENDING AT ('2007-12-31') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 ,PARTITION OTHERS1 STARTING FROM (MINVALUE) EXCLUSIVE ENDING AT ('2007-01-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION OTHERS2 STARTING FROM ('2008-01-01') INCLUSIVE ENDING AT (MAXVALUE) EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 ) INDEX IN USERSPACE1
DB20000I SQL命令成功完成?
此時(shí)我們?cè)俅尾迦肭鍐?5中所插入的值,就可以成功了,具體如清單18所示:
- - 清單18 .對(duì)示例分區(qū)表TEST3插入一條記錄
C:\> DB2 INSERT INTO TEST3 VALUES (1,'2006-01-10' , blob ( ' the first rows ' ) )
DB20000I SQL命令成功完成?
對(duì)清單10中,把數(shù)值型的列作為表分區(qū)鍵,也可以用手工的方式進(jìn)行創(chuàng)建,具體如清單19所示:
- - 清單19 .創(chuàng)建示例分區(qū)表TEST4
C:\> DB2 CREATE TABLE RHETTE.TEST4 ( COL1 INTEGER NOT NULL , COL2 BIGINT NOT NULL ) PARTITION BY RANGE ( COL2 NULLS LAST ) ( PARTITION DATAPARTION1 STARTING FROM ( 1 ) INCLUSIVE ENDING AT ( 10 ) INCLUSIVE IN TABLESPACE1 , PARTITION DATAPARTITION2 STARTING FROM ( 11 ) INCLUSIVE ENDING AT ( 20 ) INCLUSIVE )
DB20000I SQL命令成功完成?
使用手工方式創(chuàng)建分區(qū)表可以將多個(gè)列用作表分區(qū)鍵,比如我們?cè)贒B2CLP窗口中創(chuàng)建示例分區(qū)表TEST5,具體如清單20所示:
- - 清單20 .創(chuàng)建示例分區(qū)表TEST5
C:\> DB2 CREATE TABLE TEST5 ( year INT , month INT ) PARTITION BY RANGE( year , month ) ( STARTING FROM ( 2007 , 1 ) ENDING ( 2007 , 3 ) IN TABLESPACE1 , ENDING ( 2007 , 6 ) IN TABLESPACE2 , ENDING ( 2007 , 9 ) IN TABLESPACE3 )
DB20000I SQL命令成功完成?
命令成功完成,這樣我們就成功地創(chuàng)建了示例分區(qū)表TEST5,其包含3個(gè)數(shù)據(jù)分區(qū),即2007年前3個(gè)季度,每個(gè)季度一個(gè)數(shù)據(jù)分區(qū)?需要注意的是,當(dāng)將多個(gè)列用作表分區(qū)鍵時(shí),將把這些列視為組合鍵(類(lèi)似于索引中的組合鍵),其中,后面的列依賴于前面的列?指定的每個(gè)起始值或結(jié)束值(所有列一起)不能超出512個(gè)字符?此限制與 SYSCAT.DATAPARTITIONS目錄視圖中的LOWVALUE和HIGHVALUE 列大小對(duì)應(yīng)?如果指定超出512個(gè)字符的起始值或結(jié)束值,就會(huì)導(dǎo)致錯(cuò)誤SQL0636N,原因碼為9? 表分區(qū)是多列的,而不是多維的?在表分區(qū)中,使用的所有列都包含在單個(gè)維中?
另外,還可以將生成列用作表分區(qū)鍵?接下來(lái)我們?cè)贒B2CLP窗口中,創(chuàng)建示例分區(qū)表TEST6,其包含 12 個(gè)數(shù)據(jù)分區(qū)的表,即每個(gè)月一個(gè)數(shù)據(jù)分區(qū)?對(duì)于任何年份,一月份的所有行都將被放到第一個(gè)數(shù)據(jù)分區(qū)中,二月份的行將被放到第二個(gè)數(shù)據(jù)分區(qū)中,依此類(lèi)推,具體如清單21所示:
- - 清單21 .創(chuàng)建示例分區(qū)表TEST6
C:\> DB2 CREATE TABLE TEST6 ( RQ date,YF int GENERATED ALWAYS AS ( month( RQ ) ) ) PARTITION BY RANGE ( YF ) (STARTING FROM 1 ENDING AT 12 EVERY 1 )
DB20000I SQL命令成功完成?
命令成功完成,這樣我們成功創(chuàng)建了示例分區(qū)表TEST6,需要注意的是對(duì)于表分區(qū)鍵中使用的生成列,不能改變或刪除其表達(dá)式?不允許對(duì)表分區(qū)鍵中使用的列添加生成列表達(dá)式?對(duì)于表分區(qū)鍵中使用的列,如果嘗試添加?刪除或改變?cè)摿械纳闪斜磉_(dá)式,就會(huì)導(dǎo)致錯(cuò)誤(SQL0270N,原因碼為52)?
1