范围分区不同的建表方式
CREATE TABLE LINEITEM
( l_orderkey DECIMAL(10,0) NOT NULL,
l_partkey INTEGER,
l_suppkey INTEGER,
l_linenumber INTEGER,
l_quantity DECIMAL(12,2),
l_extendedprice DECIMAL(12,2),
l_discount DECIMAL(12,2),
l_tax DECIMAL(12,2),
l_returnflag CHAR(1),
l_linestatus CHAR(1),
l_shipdate DATE,
l_commitdate DATE,
l_receiptdate DATE,
l_shipinstruct CHAR(25),
l_shipmode CHAR(10),
l_comment VARCHAR(44))
PARTITION BY RANGE(l_shipdate)
( STARTING ‘1/1/1992′ ENDING ’30/06/1992’,
STARTING ‘1/7/1992′ ENDING ’31/12/1992’,
STARTING ‘1/1/1993′ ENDING ’30/6/1993’,
STARTING ‘1/7/1993′ ENDING ’31/12/1993’) –不指定边界
PARTITION BY RANGE(l_shipdate)
( STARTING MINVALUE,
STARTING ‘1/1/1992′ ENDING ’30/06/1992’,
STARTING ‘1/7/1992′ ENDING ’31/12/1992’,
STARTING ‘1/1/1993′ ENDING ’30/6/1993’,
STARTING ‘1/7/1993′ ENDING ’31/12/1993’,
ENDING MAXVALUE) –指定边界
PARTITION BY RANGE(l_shipdate)
(STARTING MINVALUE,
STARTING ‘1/1/1992′ ENDING ’31/12/1998’
EVERY 1 MONTH,
ENDING MAXVALUE); –默认连续创建分区
PARTITION BY RANGE(l_shipdate)
( PART JAN1992 STARTING ‘1/1/1992′ ENDING ’30/6/1992’ IN DMS_D1,
PART JULY1992 STARTING ‘1/7/1992′ ENDING ’31/12/1992’ IN DMS_D2,
PART JAN 1993 STARTING ‘1/1/1993′ ENDING ’30/6/1993’ IN DMS_D3,
PART JULY1993 STARTING ‘1/7/1993′ ENDING ’31/12/1993’ IN DMS_D4); –指定表空间，并对各分区命名
db2 describe data partitions for table LINEITEM –查看结构
db2 describe data partitions for table LINEITEM show detail –查看详细结构
db2 “select datapartitionnum(l_shipdate) as PartitionId, l_shipdate from lineitem
where l_shipdate between ’01/06/1992′ and ’31/07/1992′
order by l_shipdate” –查看数据分布
db2 “create index I_LINEITEM on LINEITEM(L_SHIPDATE) in DMS_I1” –建立索引
参考文档 http://www.ibm.com/developerworks/cn/education/data/dm0612read/section4.html#ibm-pcon