DB2 V9表分割槽(四)

rheet1978發表於2008-07-21
使用手工方式建立示例分割槽表

        手工生成方法為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個鍵值:

l         (RQ) >= ( ' 2007-01-01 ' ) , (RQ) < (' 2007-02-01 ' )

l         (RQ) >= ( ' 2007-02-01 ' ) , (RQ) < (' 2007-03-01 ' )

l         . . . . . .

l         (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,我們可以增量兩個資料分割槽:

l         資料分割槽OTHERS1,範圍從MINVALUE到 ” 2007-01-01” ,不包含” 2007-01-01”;所有比” 2007-01-01 ”小的值,都會放入到此分割槽中。

l         資料分割槽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)。

 

 

 

參考資料

學習


獲得產品和技術


討論

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9524210/viewspace-405457/,如需轉載,請註明出處,否則將追究法律責任。

相關文章