满意答案
OPENyourhe
2013.06.01
采纳率:54% 等级:12
已帮助:10558人
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','yyyy-MM-dd'), 20),
partition P_2013_MAX values less than (to_date('2014-01-01','yyyy-MM-dd'), MAXVALUE),
partition P_MAX values less than (MAXVALUE,MAXVALUE)
);
INSERT INTO T_TEST VALUES (1,TO_DATE('20121204 00:00:00','YYYYMMDD HH24:MI:SS'));
INSERT INTO T_TEST VALUES (12,TO_DATE('20121204 00:00:00','YYYYMMDD HH24:MI:SS'));
INSERT INTO T_TEST VALUES (32,TO_DATE('20121204 00:00:00','YYYYMMDD HH24:MI:SS'));
INSERT INTO T_TEST VALUES (2,TO_DATE('20131204 00:00:00','YYYYMMDD HH24:MI:SS'));
INSERT INTO T_TEST VALUES (12,TO_DATE('20131204 00:00:00','YYYYMMDD HH24:MI:SS'));
INSERT INTO T_TEST VALUES (33,TO_DATE('20131204 00:00:00','YYYYMMDD HH24:MI:SS'));
INSERT INTO T_TEST VALUES (3,TO_DATE('20141204 00:00:00','YYYYMMDD HH24:MI:SS'));
INSERT INTO T_TEST VALUES (23,TO_DATE('20141204 00:00:00','YYYYMMDD HH24:MI:SS'));
INSERT INTO T_TEST VALUES (43,TO_DATE('20151204 00:00:00','YYYYMMDD HH24:MI:SS'));
SELECT * FROM T_TEST;
SELECT * FROM T_TEST PARTITION(P_2012_10);
SELECT * FROM T_TEST PARTITION(P_2012_20);
SELECT * FROM T_TEST PARTITION(P_2012_MAX);
SELECT * FROM T_TEST PARTITION(P_2013_10);
SELECT * FROM T_TEST PARTITION(P_2013_20);
SELECT * FROM T_TEST PARTITION(P_2013_MAX);
SELECT * FROM T_TEST PARTITION(P_MAX);
00分享举报