|
摘录部分我的笔记的中doc，和大家一起感受Oracle 11g在分区方面的增强 Oracle 11g中Interval Partitioning分区实际上是由range分区引申而来，最终实现了range分区的自动化。 间隔分区的特点 ●由range分区派生而来 ●以定长宽度创建分区（比如年、月、具体的数字（比如100、500等）） ●分区字段必须是number或date类型 ●必须至少指定一个range分区（永久分区） ●当有记录插入时，系统根据需要自动创建新的分区和本地索引 ●已有的范围分区可被转换成间隔分区（通过ALTER TABLE SET INTERVAL选项完成） ●Interval Partitioning不支持支持索引组织表 ●在Interval Partitioning表上不能创建domain index （补充说明：域索引
域索引是程序专用(TEXT,SPATIAL)索引,作为一个索引类型通过例程管理和访问,之所以叫做域索引是因为它的索引数据在程序专用区域。
只有单行索引被域索引支持
可以创建单行域索引在纯量,对象,或者LOB数据类型字段上
全文索引、空间索引就是域索引的一种。
简单的说,是Oracle支持的一种可以有用户自己定制规则的索引.其中全文索引和空间索引等是Oracle已经定义好的域索引 ）
说明 1）对于采用date类型的Interval Partitioning可以支持按year、month、day、hour、minute、second六种类型的定长宽度分区，分别通过如下函数转换 numtoyminterval ( n, { 'YEAR'|'MONTH'})
numtodsinterval ( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'}) 具体的用法可以参见下面的例子。 2）对于采用number类型的Interval Partitioning必须按照固定的宽度分区 下面用几个具体的例子来感受一下Interval Partitioning 示例一：DATE类型按YEAR Interval Partitioning Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as study
SQL> create table interval_test_by_year
2 ( region number(4),
3 recdate date not null,
4 recoid number
5 )
6 partition by range(recdate)
7 interval(numtoyminterval(1,'year'))
8 (
9 partition p1 values less than(to_date('20050101','yyyymmdd'))
10 );
Table created
SQL> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name='INTERVAL_TEST_BY_YEAR'
4 order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------------- --------------- --------------------------------------------------------------------------------
INTERVAL_TEST_BY_YEAR P1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select table_name,partitioning_type,status,interval from user_part_tables
2 where table_name='INTERVAL_TEST_BY_YEAR';
TABLE_NAME PARTITIONING_TYPE STATUS INTERVAL
----------------------- ----------------- -------- --------------------------
INTERVAL_TEST_BY_YEAR RANGE VALID NUMTOYMINTERVAL(1,'YEAR')
SQL> select * from interval_test_by_year;
REGION RECDATE RECOID
------ ----------- ----------
SQL> insert into interval_test_by_year values(531,to_date('20040101','yyyymmdd'),1);
1 row inserted
SQL> insert into interval_test_by_year values(531,to_date('20050101','yyyymmdd'),2);
1 row inserted
SQL> insert into interval_test_by_year values(531,to_date('20060101','yyyymmdd'),3);
1 row inserted
SQL> commit;
Commit complete
SQL> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name='INTERVAL_TEST_BY_YEAR'
4 order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------------- ---------------- --------------------------------------------------------------------------------
INTERVAL_TEST_BY_YEAR P1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_YEAR SYS_P61 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_YEAR SYS_P62 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select * from interval_test_by_year;
REGION RECDATE RECOID
------ ----------- ----------
531 2004-1-1 1
531 2005-1-1 2
531 2006-1-1 3
SQL> select * from interval_test_by_year partition (p1);
REGION RECDATE RECOID
------ ----------- ----------
531 2004-1-1 1
SQL> select * from interval_test_by_year partition (sys_p61);
REGION RECDATE RECOID
------ ----------- ----------
531 2005-1-1 2
SQL> select * from interval_test_by_year partition (sys_p62);
REGION RECDATE RECOID
------ ----------- ----------
531 2006-1-1 3
SQL>
示例二：DATE类型按monh Interval Partitioning Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as study
SQL> create table interval_test_by_month
2 ( region number(4),
3 recdate date not null,
4 recoid number
5 )
6 partition by range(recdate)
7 interval(numtoyminterval(1,'month'))
8 (
9 partition p1 values less than(to_date('20071201','yyyymmdd'))
10 );
Table created
SQL> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name='INTERVAL_TEST_BY_MONTH'
4 order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
----------------------- ---------------- --------------------------------------------------------------------------------
INTERVAL_TEST_BY_MONTH P1 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select table_name,partitioning_type,status,interval from user_part_tables
2 where table_name='INTERVAL_TEST_BY_MONTH';
TABLE_NAME PARTITIONING_TYPE STATUS INTERVAL
----------------------- ----------------- -------- --------------------------
INTERVAL_TEST_BY_MONTH RANGE VALID NUMTOYMINTERVAL(1,'MONTH')
SQL> insert into interval_test_by_month values(531,to_date('20071212','yyyymmdd'),1);
1 row inserted
SQL> insert into interval_test_by_month values(531,to_date('20080110','yyyymmdd'),2);
1 row inserted
SQL> insert into interval_test_by_month values(531,to_date('20090101','yyyymmdd'),3);
1 row inserted
SQL> commit;
Commit complete
SQL> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name='INTERVAL_TEST_BY_MONTH'
4 order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------ ---------------- --------------------------------------------------------------------------------
INTERVAL_TEST_BY_MONTH P1 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_MONTH SYS_P63 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_MONTH SYS_P64 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_MONTH SYS_P65 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select * from interval_test_by_month partition (p1);
REGION RECDATE RECOID
------ ----------- ----------
SQL> select * from interval_test_by_month partition (sys_p63);
REGION RECDATE RECOID
------ ----------- ----------
531 2007-12-12 1
SQL> select * from interval_test_by_month partition (sys_p64);
REGION RECDATE RECOID
------ ----------- ----------
531 2008-1-10 2
SQL> select * from interval_test_by_month partition (sys_p65);
REGION RECDATE RECOID
------ ----------- ----------
531 2009-1-1 3
SQL>
示例三：DATE类型按day Interval Partitioning Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as study
SQL> create table interval_test_by_day
2 ( region number(4),
3 recdate date not null,
4 recoid number
5 )
6 partition by range(recdate)
7 interval(numtodsinterval(1,'day'))
8 (
9 partition p1 values less than(to_date('20071201','yyyymmdd'))
10 );
Table created
SQL> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name='INTERVAL_TEST_BY_DAY'
4 order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------------- ---------------- --------------------------------------------------------------------------------
INTERVAL_TEST_BY_DAY P1 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select table_name,partitioning_type,status,interval from user_part_tables
2 where table_name='INTERVAL_TEST_BY_DAY';
TABLE_NAME PARTITIONING_TYPE STATUS INTERVAL
---------------------- ----------------- -------- ------------------------
INTERVAL_TEST_BY_DAY RANGE VALID NUMTODSINTERVAL(1,'DAY')
SQL> insert into interval_test_by_day values(531,to_date('20071202','yyyymmdd'),1);
1 row inserted
SQL> insert into interval_test_by_day values(531,to_date('20071203','yyyymmdd'),2);
1 row inserted
SQL> insert into interval_test_by_day values(531,to_date('20081205','yyyymmdd'),3);
1 row inserted
SQL> commit;
Commit complete
SQL> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name='INTERVAL_TEST_BY_DAY'
4 order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------------- ---------------- --------------------------------------------------------------------------------
INTERVAL_TEST_BY_DAY P1 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_DAY SYS_P66 TO_DATE(' 2007-12-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_DAY SYS_P67 TO_DATE(' 2007-12-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_DAY SYS_P68 TO_DATE(' 2008-12-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> select table_name,partitioning_type,status,interval from user_part_tables
2 where table_name='INTERVAL_TEST_BY_DAY';
TABLE_NAME PARTITIONING_TYPE STATUS INTERVAL
---------------------- ----------------- -------- ------------------------
INTERVAL_TEST_BY_DAY RANGE VALID NUMTODSINTERVAL(1,'DAY')
SQL> insert into interval_test_by_day values(531,to_date('20081204','yyyymmdd'),4);
1 row inserted
SQL> commit;
Commit complete
SQL> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name='INTERVAL_TEST_BY_DAY'
4 order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------------- ---------------- --------------------------------------------------------------------------------
INTERVAL_TEST_BY_DAY P1 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_DAY SYS_P66 TO_DATE(' 2007-12-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_DAY SYS_P67 TO_DATE(' 2007-12-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_DAY SYS_P69 TO_DATE(' 2008-12-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST_BY_DAY SYS_P68 TO_DATE(' 2008-12-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL>
说明：按照hour,minute,second的和上面的做法类似，本案例不再示例中不再介绍. 示例四：NUMBER类型按定长间隔分区 假设我们的系统中有一张存放员工薪水的表，由于公司员工很多，为了便于管理，我们按照员工的薪水将这张表分区，每2000一个档次，以前的做法是： create table interval_test_by_number
( empno number(6),
name varchar2(20),
sal number(6)
)
partition by range(salary)
(
partition p_2k values less than (2001),
partition p_4k values less than (4001),
partition p_6k values less than (6001),
partition p_8k values less than (8001)
partition p_10k values less than (10001)
partition p_max values less than (maxvalue)
); 设置5个档次，分别从2K到10K，预留一个maxvalue分区，考虑将来随着物价的上涨，员工的薪水可能会超过10K，到时候通过split动作来新扩展 分区.
那么到了11g中，我们就不需要这么麻烦了，完全可以提交给Oracle，利用Interval Partitioning技术，让系统自动完成。对DBA来说，只需要创建一个基本salary薪水的分区即可。以后谁着公司的薪酬体系的变化，系统会自动扩展需要的分区。比如： Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as study
SQL> create table interval_test_by_number
2 (empno number(6),
3 name varchar2(20),
4 sal number(6)
5 )
6 partition by range(sal)
7 Interval (2000)
8 (
9 partition p_2k values less than (2001)
10 );
Table created
SQL> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name='INTERVAL_TEST_BY_NUMBER'
4 order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
-------------------------- ----------------- ------------
INTERVAL_TEST_BY_NUMBER P_2K 2001
SQL> select table_name,partitioning_type,status,interval from user_part_tables
2 where table_name='INTERVAL_TEST_BY_NUMBER';
TABLE_NAME PARTITIONING_TYPE STATUS INTERVAL
------------------------- ----------------- -------- ---------
INTERVAL_TEST_BY_NUMBER RANGE VALID 2000
SQL> insert into interval_test_by_number values (1001,'thomas zhang',1800);
1 row inserted
SQL> insert into interval_test_by_number values (1002,'zhangrunping',2500);
1 row inserted
SQL> insert into interval_test_by_number values (1003,'zrp',3000);
1 row inserted
SQL> insert into interval_test_by_number values (1005,'CEO',9999);
1 row inserted
SQL> commit;
Commit complete
SQL> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name='INTERVAL_TEST_BY_NUMBER'
4 order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------- ----------------- ------------
INTERVAL_TEST_BY_NUMBER P_2K 2001
INTERVAL_TEST_BY_NUMBER SYS_P73 4001
INTERVAL_TEST_BY_NUMBER SYS_P74 10001
SQL>
|
