db2 partition table test
db2 => CREATE TABLE carsales(sale_date DATE, VIN char(30)) PARTITION BY RANGE(sale_date) (part q113 STARTING MINVALUE ENDING '3/31/2013' EXCLUSIVE, part q213 STARTING '3/31/2013' ENDING '6/30/2013' EXCLUSIVE, part q313 STARTING '6/30/2013' ENDING '9/30/2013' EXCLUSIVE, part q413 STARTING '9/30/2013' ENDING '12/31/2013')
DB20000I The SQL command completed successfully.
db2 => describe data partitions for table carsales show detail
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
----------- ------------------------------- ----------- ----------- ------------ ----------- - ------
0 Q113 2 523 2 2 F
1 Q213 2 524 2 2 F
2 Q313 2 525 2 2 F
3 Q413 2 526 2 2 F
4 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
----------- - ------------------------------- - -------------------------------
0 Y MINVALUE N '2013-03-31'
1 Y '2013-03-31' N '2013-06-30'
2 Y '2013-06-30' N '2013-09-30'
3 Y '2013-09-30' Y '2013-12-31'
4 record(s) selected.
db2 => alter table carsales detach partition q113 into tblq113
DB20000I The SQL command completed successfully.
db2 => describe data partitions for table carsales show detail
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
----------- ------------------------------- ----------- ----------- ------------ ----------- - ------
1 Q213 2 524 2 2 F
2 Q313 2 525 2 2 F
3 Q413 2 526 2 2 F
3 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
----------- - ------------------------------- - -------------------------------
1 Y '2013-03-31' N '2013-06-30'
2 Y '2013-06-30' N '2013-09-30'
3 Y '2013-09-30' Y '2013-12-31'
3 record(s) selected.
db2 => insert into tblq113 values('2013-01-01','Jan')
db2 => ALTER TABLE carsales ATTACH PARTITION STARTING MINVALUE ENDING ('3/31/2013') EXCLUSIVE FROM tblq113
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586
db2 => set integrity for carsales immediate checked
DB20000I The SQL command completed successfully.
db2 => select * from carsales
SALE_DATE VIN
---------- ------------------------------
01/01/2013 Jan
1 record(s) selected.
db2 =>
DB20000I The SQL command completed successfully.
db2 => describe data partitions for table carsales show detail
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
----------- ------------------------------- ----------- ----------- ------------ ----------- - ------
0 Q113 2 523 2 2 F
1 Q213 2 524 2 2 F
2 Q313 2 525 2 2 F
3 Q413 2 526 2 2 F
4 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
----------- - ------------------------------- - -------------------------------
0 Y MINVALUE N '2013-03-31'
1 Y '2013-03-31' N '2013-06-30'
2 Y '2013-06-30' N '2013-09-30'
3 Y '2013-09-30' Y '2013-12-31'
4 record(s) selected.
db2 => alter table carsales detach partition q113 into tblq113
DB20000I The SQL command completed successfully.
db2 => describe data partitions for table carsales show detail
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
----------- ------------------------------- ----------- ----------- ------------ ----------- - ------
1 Q213 2 524 2 2 F
2 Q313 2 525 2 2 F
3 Q413 2 526 2 2 F
3 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
----------- - ------------------------------- - -------------------------------
1 Y '2013-03-31' N '2013-06-30'
2 Y '2013-06-30' N '2013-09-30'
3 Y '2013-09-30' Y '2013-12-31'
3 record(s) selected.
db2 => insert into tblq113 values('2013-01-01','Jan')
db2 => ALTER TABLE carsales ATTACH PARTITION STARTING MINVALUE ENDING ('3/31/2013') EXCLUSIVE FROM tblq113
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586
db2 => set integrity for carsales immediate checked
DB20000I The SQL command completed successfully.
db2 => select * from carsales
SALE_DATE VIN
---------- ------------------------------
01/01/2013 Jan
1 record(s) selected.
db2 =>
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/725820/viewspace-2125516/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- partition table test
- partition table and partition indexIndex
- 拆分Table 為Partition Table
- partition table(1)
- partition table(2)
- When to Partition a Table
- [Oracle] Partition table exchange Heap tableOracle
- Oracle 普通table 轉換為partition tableOracle
- create a partition table using a exsit table
- 分割槽表PARTITION table
- partition table update partition-key result in changing tablespace
- Interval Partition table 11G
- Oracle分割槽表(Partition Table)Oracle
- How to partition a non-partitioned table
- 分割槽表PARTITION table(轉)
- partition table中truncate應用
- mysql partition table use to_days bugMySql
- 關於partition table import的問題Import
- doesn't contain a valid partition tableAI
- add hash partition , default tablespace for patitioned table
- 【DB】Direct Path EXP Corrupts The Dump If An Empty Table Partition Exists
- Applying “Incremental Statistic” for Oracle Big Partition TableAPPREMOracle
- 壓縮table and index 對 space 的影響testIndex
- Data Warehouse Guide閱讀筆記(七):partition tableGUIIDE筆記
- ext4 lvreduce報錯superblock or the partition table is likely to be corruptVRBloC
- TiDB 原始碼閱讀系列文章(二十)Table PartitionTiDB原始碼
- 今天測試了一下update partition table的part key
- DB2 ADMIN_MOVE_TABLE 表移動DB2
- best practice of rebuild your partition table local index online by using: "update indexes"RebuildIndex
- PARTITION partition01
- PARTITION partition02
- PARTITION partition04
- ERROR 1114 (HY000): The table 'test1' is full 的解決Error
- Pruning、Reference Partition、Exchange Partition
- partition timestamp(0) not use partition
- 教你解決ghost win10開機出現invalid partition table的方法Win10
- [20191203]enq: ZA - add std audit table partition.txtENQ
- test