手工生成方法為PARTITION BY子句中列示的每個范圍創建一個新數據分區?這種語法格式提高了定義范圍時的靈活性,從而增加了數據和LOB布置選項?對清單12所創建的示例分區表TEST3,如果用手工方式創建,可以在DB2CLP窗口中,連上數據庫,先發出DROP TABLE命令,刪除示例分區表TEST3,再發出CREATE TABLE命令創建示例分區表TEST3,其分區鍵RQ允許錄入的時間段為“2007-01-01”到“2007-12-31”,具體如清單13所示:
- - 清單13 .手工創建示例分區表TEST3
C:\> DB2 CONNECT TO DB2TEST1
數據庫連接信息
數據庫服務器 = DB2 / NT 9.1.0
SQL 授權標識 = RHETTE
本地數據庫別名 = 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命令成功完成?
這樣我們通過手工方式創建了示例分區表TEST3,用來對表數據進行分區的列是RQ,表數據將存儲在下列表空間中:TABLESPACE1,TABLESPACE2和TABLESPACE3?缺省情況下,索引將存儲在以下表空間中:USERSPACE1?此語句生成12個數據分區,每個數據分區包含1個鍵值:
? (RQ) >= ( ' 2007-01-01 ' ) , (RQ) < (' 2007-02-01 ' )
? (RQ) >= ( ' 2007-02-01 ' ) , (RQ) < (' 2007-03-01 ' )
? . . . . . .
? (RQ) >= ( ' 2007-12-01 ' ) , (RQ) <= (' 2007-12-31 ' )
每個數據分區通過PARTITION命令進行了命名,名字分別DATAPARTITION1,DATAPARTITION2, . . . . . . DATAPARTITION12?
接下來我們對示例分區表TEST3插入數據,在DB2CLP窗口中,發出INSERT INTO命令,具體如清單14所示:
- - 清單14 .對示例分區表TEST3插入一條記錄
C:\> DB2 INSERT INTO TEST3 VALUES ( 1 , '2007-01-10' , blob ( 'the first rows' ) )
DB20000I SQL命令成功完成?
命令成功完成,把行插入示例分區表時,根據該行的鍵值以及它所處的范圍自動將其放入正確的數據分區,這樣我們數據就插入到了數據分區DATAPARTITION1中了?如果該行處于對該表定義的所有范圍之外,插入就會失敗,并且將把以下錯誤返回給應用程序,比如我們插入一條如清單15所示的記錄,RQ的值為'2006-01-10',不在'2007-01-01'和'2007-12-31'的范圍內:
- - 清單15 .對示例分區表TEST3插入一條分區鍵范圍外的記錄
C:\> DB2 INSERT INTO TEST3 VALUES (1 , '2006-01-10' , blob ( 'the first rows' ) )
DB21034E 該命令被當作 SQL語句來處理,因為它不是有效的“命令行處理器”命令｡在 SQL 處理期間,它返回:
SQL0327N 無法將行插入表 " RHETTE.TEST3 " 中,因為它在定義的數據分區范圍之外｡
SQLSTATE = 22525
此時我們對查看示例分區表中的記錄,通過在WHERE子句中指定分區鍵的值,可以能夠避免掃描不相關的數據,直接從DATAPARTITION1數據分區中得到想要的記錄,從而提高查詢處理性能,具體如清單16所示:
- - 清單16 .查詢示例分區表TEST3
C:\> db2 select id,rq from test3 where rq='2007-01-10'
ID RQ
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1 2007-01-10
1 條記錄已選擇｡
如果想在范圍之間允許存在間隔,可以使用MINVALUE和MAXVALUE指定間隔的范圍,MINVALUE表示最小的值,就是比你插入的任何值都會小的意思,MAXVALUE表示最大的值,就是比你插入的任何值都要大的意思?對示例分區表TEST3,我們可以增量兩個數據分區:
? 數據分區OTHERS1,范圍從MINVALUE到 ” 2007-01-01” ,不包含” 2007-01-01”;所有比” 2007-01-01 ”小的值,都會放入到此分區中?
? 數據分區OTHERS2,范圍從 ” 2008-01-01” 到MAXVALUE,包含” 2008-01-01”;所有比 ” 2008-01-01” 大的值,都會放入到此分區中?
具體如清單17中藍色標記部分所示:
- - 清單17 .手工創建示例分區表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命令成功完成?
此時我們再次插入清單15中所插入的值,就可以成功了,具體如清單18所示:
- - 清單18 .對示例分區表TEST3插入一條記錄
C:\> DB2 INSERT INTO TEST3 VALUES (1,'2006-01-10' , blob ( ' the first rows ' ) )
DB20000I SQL命令成功完成?
對清單10中,把數值型的列作為表分區鍵,也可以用手工的方式進行創建,具體如清單19所示:
- - 清單19 .創建示例分區表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命令成功完成?
使用手工方式創建分區表可以將多個列用作表分區鍵,比如我們在DB2CLP窗口中創建示例分區表TEST5,具體如清單20所示:
- - 清單20 .創建示例分區表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命令成功完成?
命令成功完成,這樣我們就成功地創建了示例分區表TEST5,其包含3個數據分區,即2007年前3個季度,每個季度一個數據分區?需要注意的是,當將多個列用作表分區鍵時,將把這些列視為組合鍵(類似于索引中的組合鍵),其中,后面的列依賴于前面的列?指定的每個起始值或結束值(所有列一起)不能超出512個字符?此限制與 SYSCAT.DATAPARTITIONS目錄視圖中的LOWVALUE和HIGHVALUE 列大小對應?如果指定超出512個字符的起始值或結束值,就會導致錯誤SQL0636N,原因碼為9? 表分區是多列的,而不是多維的?在表分區中,使用的所有列都包含在單個維中?
另外,還可以將生成列用作表分區鍵?接下來我們在DB2CLP窗口中,創建示例分區表TEST6,其包含 12 個數據分區的表,即每個月一個數據分區?對于任何年份,一月份的所有行都將被放到第一個數據分區中,二月份的行將被放到第二個數據分區中,依此類推,具體如清單21所示:
- - 清單21 .創建示例分區表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命令成功完成?
命令成功完成,這樣我們成功創建了示例分區表TEST6,需要注意的是對于表分區鍵中使用的生成列,不能改變或刪除其表達式?不允許對表分區鍵中使用的列添加生成列表達式?對于表分區鍵中使用的列,如果嘗試添加?刪除或改變該列的生成列表達式,就會導致錯誤(SQL0270N,原因碼為52)?
1