一、 分区类型
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.
三、 11g新特性
分区增强功能
·间隔分区
·基于虚拟列的分区
·引用分区
·组合分区增强功能
·分区顾问
3.1间隔分区
· 间隔分区是范围分区的一种扩展
·当插入的数据超过了所有范围分区时，将自动创建指定间隔的分区。
·必须至少创建一个范围分区
·间隔分区可以自动创建范围分区
CREATE TABLE sales (order_date DATE, ...)
PARTITON BY RANGE (order_date)
INTERVAL(NUMTOYMINTERVAL(1,‘month‘)
(PARTITION p_first VALUES LESS THAN(‘01-JAN-2006‘);
numtoyminterval函数——数字转换函数
语法：NUMTOYMINTERVAL ( n , ‘char_expr‘ )
char_expr：日期描述，可以是YEAR和MONTH；
作用：可以将数字转换成相应的日期单位时间
比如：NUMTOYMINTERVAL ( 1, ‘MONTH‘ ) 表示一个月
NUMTOYMINTERVAL ( 1, ‘YEAR‘ ) 表示一年
3.2基于虚拟列的分区
· 虚拟列值是通过计算函数或表达式得到的。
· 可以在 create 或 alter 表操作中定义虚拟列。
· 虚拟列值实际上并未存储在磁盘上的表行中，而是根据需要进行计算
· 像其他表列类型一样，可以对虚拟列进行索引，可以在查询、DML 和 DDL 语句中使用它们。
· 可在虚拟列上对表和索引进行分区，甚至可以收集它们的统计信息。
CREATE TABLE accounts
(acc_no number(10) not null,
acc_name varchar2(50) not null, ...
acc_branch number(2) generated always as
(to_number(substr(to_char(acc_no),1,2)))
partition by list (acc_branch) ...
3.3引用分区
通过采用引用分区技术，首先我们不用在子表专门设计分区时间字段，直接根据外键关系，就可以对子表进行与主表相同的分区。而且，主表和子表在分区管理上也是一体的。主表增加一个分区，子表自动增加一个分区，删除主表的一个分区，子表也自动删除一个分区。
? 现在，可以根据表的引用约束条件中引用的此表的分区方法对
表进行分区。
? 分区键是通过现有的父/子关系解析的。
? 分区键是由活动的主键和外键约束条件强制实施的。
? 包含父/子关系的表可以通过从父表继承分区键进行均匀分区，
而无需复制键列。
? 分区是自动维护的。
3.4新的复合分区
Range-range
List-list
List-hash
List-range