关于oracle分区技术--初了解，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.
三、 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
安装ORACLE 很简单
1、安装ORACLE软件需要的空间大小：5G
2、/TMP分区 400M
3、交换分区 内存的2倍
4、创建数据库的时候你可以把余下的空间全都分给数据库用
一般就需要2G就够了（如果是做实验），其实就是把所有45G的空间都分在/目录下也没问题。
当然如果你用RAW来存数据文件那么就要为每个文件专分个区出来（比较麻烦不推荐使用，虽然性能有所提高，但是做实验没这个必要）
说明：用于装ORACLE软件的地方是自己在/下创建的
如：/u01/app/oracle
另外推荐你看篇文章，里面教你怎么在LINUX下装ORACLE
www.oracle-base.com/...S4.php
祝你好运
1. 组合分区表的创建方式（"范围-哈稀"），见附1
2. 楼主的需求，即"范围-范围分区"，在ORACLE 9i, 10g经过测试都是不能实现的
在附1的基础上修改为"范围-范围"组合分区，创建时报错：ORA-14151：无效的表分区方法
3. 关于sxdtgsh兄的回答，我测了
3.1 没有maxvalue上限分区设置，在插入超出分区的数据时会报错ORA-14400: 插入的分区关键字未映射到任何分区
3.2 按回答的语句创建分区表没有问题，但数据无法按照楼主的需求分布
＝＝＝＝附1
附录：创建"范围-哈稀"组合分区表
CREATE TABLE TAB11 (ID NUMBER,DT DATE)
PARTITION BY RANGE (DT)
SUBPARTITION BY HASH (ID) SUBPARTITIONS 2 -- 自分区个数，可以不写，由系统判断
(
PARTITION Y2012 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD'))
(
SUBPARTITION Y2012_H1
,SUBPARTITION Y2012_H2
)
,PARTITION Y2013 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD'))
(
SUBPARTITION Y2013_H1
,SUBPARTITION Y2013_H2
)
,PARTITION YMAX VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION YMAX_H1
,SUBPARTITION YMAX_H2
)
)
＝＝＝＝附2，请楼主检查最后查询的数据分布
create table T_TEST
(
ID NUMBER(20) NOT NULL,
TIME DATE NOT NULL
)
partition by range(TIME, ID) -- 按时间、ID范围分区 这个例子是按年的
(
partition P_2012_10 values less than (to_date('2013-01-01','yyyy-MM-dd'), 10),
partition P_2012_20 values less than (to_date('2013-01-01','yyyy-MM-dd'), 20),
partition P_2012_MAX values less than (to_date('2013-01-01','yyyy-MM-dd'), MAXVALUE),
partition P_2013_10 values less than (to_date('2014-01-01','yyyy-MM-dd'), 10),
partition P_2013_20 values less than (to_date('2014-01-01',......余下全文>>