范围分区 range(A,B)的分区法则,范围分区都是 values less than(A,B)的,通常情况下以A为准,如果小于A的不用考虑B,直接插进去,如果等于A那么考虑B,要是满足B的话也插进去。
(一)创建分区表
create table tmp_11
(
COMPANY_ID NUMBER(8) not null,
SO_NO CHAR(12) not null,
N_YEAR number(4),
N_MONTH number(2),
N_DAY number(2)
)
partition by range(N_YEAR,N_MONTH,N_DAY)
(
partition p1 values less than (2011,1,1),
partition p2 values less than (2012,1,1),
partition p3 values less than (2013,1,1),
partition p4 values less than (2014,1,1)
)
(2)创建主键索引
SQL> alter table tmp_11 add constraint PK_TMP_11 primary key (COMPANY_ID,SO_NO) using index local tablespace dms_index;
alter table tmp_11 add constraint PK_TMP_11 primary key (COMPANY_ID,SO_NO) using index local tablespace dms_index
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
alter table tmp_11
add constraint PK_TMP_11 primary key (COMPANY_ID,SO_NO)
注:创建本地索引的条件,在主键和唯一约束中,分区列必须在主键中。
SQL> alter table tmp_11 add constraint PK_TMP_11 primary key (COMPANY_ID,SO_NO) ;
Table altered
(3)插入数据,查看执行计划
select * from tmp_11 where n_year=2009
select * from tmp_11 where n_month=9
select * from tmp_11 where n_day=9
select * from tmp_11 where n_year=2009 and n_day=9
分区执行计划
Oracle分区执行计划
PARTITION RANGE (MULTI-COLUMN)
访问以组合字段为分区键的范围分区(RANGE PARTITION)表中所有与多个分区字段过滤条件相匹配的分区。多列范围分区一般使用于一些特定的条件中