一、 分区类型
1. 范围分区(Range Partitioning)
适用于 连续/按时间排序的数据
2. 散列分区(Hash Partitioning)
适用于 不连续/数据记录固定的数据
3. 组合分区 Range-Hash
Range-List
4. 列表分区 List Partitioning
适用于对不连续域的数据分区
更准确的控制数据的分区存储
适用于 位置类数据
二、 分区表的维护
准备工作
SYS@ORA11G>create tablespace sales_ts01
Tablespace created.
SYS@ORA11G>create tablespace sales_ts02
2 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts02_01.dbf' size 10m;
Tablespace created.
SYS@ORA11G>create tablespace sales_ts03
2 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts03_01.dbf' size 10m;
Tablespace created.
SYS@ORA11G>create tablespace sales_ts04
2 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts04_01.dbf' size 10m;
Tablespace created.
SYS@ORA11G>create tablespace sales_ts05
2 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts05_01.dbf' size 10m;
Tablespace created.
SYS@ORA11G>create tablespace sales_ts06
2 datafile'/u01/app/oracle/oradata/ORA11G/sales_ts06_01.dbf' size 10m;
Tablespace created.
SYS@ORA11G>conn tyger/tyger
Connected.
TYGER@ORA11G>CREATE TABLE SALES
(PROD_ID NUMBER,
CUST_ID NUMBER,
TIME_ID DATE,
CHANNEL_ID NUMBER,
PROMO_ID NUMBER,
QUANTITY_SOLD NUMBER(10,2),
AMOUNT_SOLD NUMBER(10,2)
)
PARTITION BY RANGE(TIME_ID)
(PARTITION sales01 values less than ('01-Feb-2004')TABLESPACE SALES_TS01,
PARTITION sales02 values less than ('01-Mar-2004')TABLESPACE SALES_TS02,
PARTITION sales03 values less than ('01-Apr-2004')TABLESPACE SALES_TS03,
PARTITION sales04 values less than ('01-May-2004')TABLESPACE SALES_TS04,
PARTITION sales05 values less than ('01-Jun-2004')TABLESPACE SALES_TS05,
PARTITION sales06 values less than ('01-Jul-2004')TABLESPACE SALES_TS06
); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Table created.
TYGER@ORA11G>selectTABLE_NAME,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,STATUS
2 from user_part_tables;
TABLE_NAME PARTITION SUBPARTIT STATUS
------------------------------ --------- -----------------
SALES RANGE NONE VALID
TYGER@ORA11G>col table_name for a20
TYGER@ORA11G>col tablespace_name for a20
1 selectTABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
2* fromuser_tab_partitions
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------------------
SALES SALES01 SALES_TS01
SALES SALES02 SALES_TS02
SALES SALES03 SALES_TS03
SALES SALES04 SALES_TS04
SALES SALES05 SALES_TS05
SALES SALES06 SALES_TS06
6 rows selected.
语法：
ALTERTABLE ALTER INDEX
ADD PARTITION -DROP PARTITION
COALESCE PARTITION - MODIFY PARTITION
DROP PARTITION - MODIFY DEFAULT ATTRIBUTES
EXCHANGE PARTITION - MODIFY PARTITION COALESCE
MERGE PARTITIONS - REBUILD PARTITION
MODIFY PARTITION - RENAME PARTITION
MODIFY DEFAULT -SPLIT PARTITION
ATTRIBUTES - UNUSABLE
MOVE PARTITION
RENAME PARTITION
SPLIT PARTITION
2.1 删除表分区
Alter table sales droppartition sales01;
TYGER@ORA11G>alter table sales drop partitionsales01;
Table altered.
TYGER@ORA11G>selectTABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
2 from user_tab_partitions;
TAB