关于Oracle分区技术初步认识
一、 分区类型
1. 范围分区(Range Partitioning)
适用于 连续/按时间排序的数据
2. 散列分区(Hash Partitioning)
适用于 不连续/数据记录固定的数据
3. 组合分区 Range-Hash
Range-List
4. 列表分区 List Partitioning
适用于对不连续域的数据分区
更准确的控制数据的分区存储
适用于 位置类数据
二、 分区表的维护
准备工作
SYS@ORA11G>create tablespace sales_ts01
2 datafile'/u01/app/Oracle/oradata/ORA11G/sales_ts01_01.dbf' size 10m;
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>
SYS@ORA11G>
SYS@ORA11G>
SYS@ORA11G>conn tyger/tyger
Connected.
TYGER@ORA11G>
TYGER@ORA11G>
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
TYGER@ORA11G>l
1 selectTABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
2* fromuser_tab_partitions
TYGER@ORA11G>/
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;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------------------
SALES SALES02 SALES_TS02
SALES SALES03 SALES_TS03
SALES SALES04 SALES_TS04
SALES SALES05 SALES_TS05
SALES SALES06 SALES_TS06
2.2 增加表分区
增加分区的分区范围必须比当前分区的最后一个分区更高
TYGER@ORA11G>alter table sales add partitionsales01 values less than ('01-Feb-2004') tablespace sales_ts01;
alter table sales add partition sales01 values lessthan ('01-Feb-2004') tablespace sales_ts01
*
ERROR at line 1:
ORA-14074: partition bound mustcollate higher than that of the last partition
TYGER@ORA11G>alter table sales add partitionsales01
2 values less than ('01-Aug-2004') tablespacesales_ts01;
Table altered.
TYGER@ORA11G>selecttable_name,partition_name,tablespace_name
2 from user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------------------
SALES SALES02 SALES_TS02
SALES SALES03 SALES_TS03
SALES SALES04 SALES_TS04
SALES SALES05 SALES_TS05
SALES SALES06 SALES_TS06
SALES SALES01 SALES_TS01
2.3 合并分区
· 必须是相邻的范围分区
· 继承最大的范围边界
TYGER@ORA11G>alter table sales
2 merge partitions sales06,sales01 intopartition sales07;
Table altered.
TYGER@ORA11G>select table_name,partition_name,tablespace_name
2 from user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------------------
SALES SALES07 USERS
SALES SALES02 SALES_TS02
SALES SALES03 SALES_TS03
SALES SALES04 SALES_TS04
SALES SALES05 SALES_TS05
2.4 移动表分区
· 移动分区数据到另一个表空间
· 重新整理数据减少碎片
· 改变物理属性
TYGER@ORA11G>alter table sales move partitionsales01
2 tablespace sales_ts_move;
2.5 拆分表分区
拆分表分区———一个分区变的太大，导致备份，恢复和分区性能操作花费时间太长，重新分配I/O负载。
TYGER@ORA11G>alter table sales split partition sales07
2 at ('01-Jul-2004') // 按哪个时间点拆分
3 into (partition sales01 tablespacesales_ts01,
4 partition sales06 tablespacesales_ts06);
Table altered.
TYGER@ORA11G>selecttable_name,partition_name,tablespace_name
2 from user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------------------
SALES SALES02 SALES_TS02
SALES SALES03 SALES_TS03
SALES SALES04 SALES_TS04
SALES SALES05 SALES_TS05
SALES SALES06 SALES_TS06
SALES SALES01 SALES_TS01
6 rows selected.