分区表优点
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”

PART_ID SALES_DATE

      0 01/02/2012
      1 02/02/2012
      2 03/02/2012
      3 04/02/2012

4 record(s) selected.http://www.cndba.cn/hbhe0316/article/4810

版权声明：本文为博主原创文章，未经博主允许不得转载。

Linux,oracle