分区表优点
1.有效的转入和转出
2.更容易管理大型表
3.灵活的索引位置
4.提供了商业智能样式查询性能
1.创建分区表
db2 "CREATE TABLE TEST1.SALES
(ID INT NOT NULL,
SALES_PERSON VARCHAR(50),
REGION VARCHAR(50),
SALES_DATE DATE)
PARTITION BY RANGE(SALES_DATE)
(PART PJAN STARTING '1/1/2012' ENDING '1/31/2012',
PART PFEB STARTING '2/1/2012' ENDING '2/29/2012',
PART PMAR STARTING '3/1/2012' ENDING '3/31/2012',
PART PAPR STARTING '4/1/2012' ENDING '4/30/2012')"
2.describe查看分区表
[db2inst1@db03 stack]$ db2 "describe DATA PARTITIONS for table test1.sales show detail"
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
----------- ------------------------------- ----------- ----------- ------------ ----------- - ------
0 PJAN 2 4 2 2 F
1 PFEB 2 5 2 2 F
2 PMAR 2 6 2 2 F
3 PAPR 2 7 2 2 F
4 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
----------- - ------------------------------- - -------------------------------
0 Y '2012-01-01' Y '2012-01-31'
1 Y '2012-02-01' Y '2012-02-29'
2 Y '2012-03-01' Y '2012-03-31'
3 Y '2012-04-01' Y '2012-04-30'
4 record(s) selected.
3.插入数据
[db2inst1@db03 stack]$ db2 "insert into TEST1.SALES values (1, 'zhangsan', 'China', '2012-1-2-10.00.00')"
DB20000I The SQL command completed successfully.
[db2inst1@db03 stack]$ db2 "insert into TEST1.SALES values (2, 'lisi', 'US', '2012-2-2-10.00.00')"
DB20000I The SQL command completed successfully.
[db2inst1@db03 stack]$ db2 "insert into TEST1.SALES values (3, 'James', 'US', '2012-3-2-10.00.00')"
DB20000I The SQL command completed successfully.
[db2inst1@db03 stack]$ db2 "insert into TEST1.SALES values (4, 'jacky', 'US', '2012-4-2-10.00.00')"
DB20000I The SQL command completed successfully.
4.查看数据
[db2inst1@db03 stack]$ db2 “select datapartitionnum(SALEs_DATE) as part_id, SALES_DATE from test1.sales”
0 01/02/2012
1 02/02/2012
2 03/02/2012
3 04/02/2012
4 record(s) selected.
版权声明：本文为博主原创文章，未经博主允许不得转载。
Linux,oracle
