SQL oracle如何创建子分区？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15131653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to create sub partition in oracle?
提问by Thiyagu ATR
Now I started to learn partition concept in oracle.some how i'll manage partition now I tried to create sub partition in Oracle. I've got this error
现在我开始在 oracle.some 中学习分区概念，现在我尝试在 Oracle 中创建子分区。我有这个错误
SQL Error: ORA-14160: this physical attribute may not be specified for a table subpartition
14160. 00000 - "this physical attribute may not be specified for a table subpartition"
*Cause: unexpected option was encountered while parsing physical
attributes of a table subpartition; TABLESPACE is the only valid
option
*Action: remove invalid option(s)
*Comment: this error could have resulted from omission of a
terminating (right) parenthesis following the list of
subpartition descriptions
respective code is:
相应的代码是：
create table sub_pat_test(emp_name varchar2(30),job_id varchar2(30),hire_date date)
partition by range(hire_date) subpartition by list(job_id)(
partition p1 values less than(to_date('01-01-2003','dd-mm-yyyy'))(
subpartition sp1 values('HR_REP','PU_MAN'),subpartition sp11 values(default)),
partition p2 values less than(to_date('01-01-2004','dd-mm-yyyy'))(
subpartition sp2 values('AC_ACCOUNT','FI_ACCOUNT')
subpartition sp22 values(default)
)
partition p3 values less than(to_date('01-01-2005','dd-mm-yyyy'))(
subpartition sp3 values('SH_CLERK','ST_CLERK')
subpartition sp33 values(default)
))
partition p4 values less than(to_date('01-01-2006','dd-mm-yyyy'))(
subpartition sp4 values('SA_MAN','PU_MAN')
subpartition sp44 values(default)
)
partition p5 values less than(maxvalues)(
subpartition sp5 values(default)
)) ;
thanks in advance!
提前致谢！
回答by Incognito
There are two errors - missing commas and you have referred to MAXVALUEas maxvalues
有两个错误-缺少逗号和你提到MAXVALUE的maxvalues
create table sub_pat_test(emp_name varchar2(30),job_id varchar2(30),hire_date date)
partition by range(hire_date)
subpartition by list(job_id)(
Partition P1 Values Less Than(To_Date('01-01-2003','dd-mm-yyyy'))
(
Subpartition Sp1 Values('HR_REP','PU_MAN'),
Subpartition Sp11 Values(Default)
),
Partition P2 Values Less Than(To_Date('01-01-2004','dd-mm-yyyy'))
(
subpartition sp2 values('AC_ACCOUNT','FI_ACCOUNT') ,
Subpartition Sp22 Values(Default)
),
Partition P3 Values Less Than(To_Date('01-01-2005','dd-mm-yyyy'))
(
subpartition sp3 values('SH_CLERK','ST_CLERK'),
subpartition sp33 values(default)
),
Partition P4 Values Less Than(To_Date('01-01-2006','dd-mm-yyyy'))(
subpartition sp4 values('SA_MAN','PU_MAN'),
subpartition sp44 values(default)
),
partition p5 values less than(maxvalue)(
subpartition sp5 values(default)
)) ;
回答by chandra sharma
Try This Working code..
试试这个工作代码..
Partition by date keeping weekly and sub partition by station name alphabetically for huge records in separate sub partition.
按日期分区保持每周和子分区按站名字母顺序为单独的子分区中的巨大记录。
PARTITION BY RANGE (CREATED_DATE) INTERVAL (numtodsinterval(7, 'DAY'))
SUBPARTITION BY RANGE(STATION)
SUBPARTITION TEMPLATE (
SUBPARTITION SUB_PART_A VALUES LESS THAN ('B%'),
SUBPARTITION SUB_PART_B VALUES LESS THAN ('C%'),
SUBPARTITION SUB_PART_C VALUES LESS THAN ('D%'),
SUBPARTITION SUB_PART_D VALUES LESS THAN ('E%'),
SUBPARTITION SUB_PART_E VALUES LESS THAN ('F%'),
SUBPARTITION SUB_PART_F VALUES LESS THAN ('G%'),
SUBPARTITION SUB_PART_G VALUES LESS THAN ('H%'),
SUBPARTITION SUB_PART_H VALUES LESS THAN ('I%'),
SUBPARTITION SUB_PART_I VALUES LESS THAN ('J%'),
SUBPARTITION SUB_PART_J VALUES LESS THAN ('K%'),
SUBPARTITION SUB_PART_K VALUES LESS THAN ('L%'),
SUBPARTITION SUB_PART_L VALUES LESS THAN ('M%'),
SUBPARTITION SUB_PART_M VALUES LESS THAN ('N%'),
SUBPARTITION SUB_PART_N VALUES LESS THAN ('O%'),
SUBPARTITION SUB_PART_O VALUES LESS THAN ('P%'),
SUBPARTITION SUB_PART_P VALUES LESS THAN ('Q%'),
SUBPARTITION SUB_PART_Q VALUES LESS THAN ('R%'),
SUBPARTITION SUB_PART_R VALUES LESS THAN ('S%'),
SUBPARTITION SUB_PART_S VALUES LESS THAN ('T%'),
SUBPARTITION SUB_PART_T VALUES LESS THAN ('U%'),
SUBPARTITION SUB_PART_U VALUES LESS THAN ('V%'),
SUBPARTITION SUB_PART_V VALUES LESS THAN ('W%'),
SUBPARTITION SUB_PART_W VALUES LESS THAN ('X%'),
SUBPARTITION SUB_PART_X VALUES LESS THAN ('Y%'),
SUBPARTITION SUB_PART_Y VALUES LESS THAN ('Z%'),
SUBPARTITION SUB_PART_Z_0_9 VALUES LESS THAN (MAXVALUE)
)
(
PARTITION OLD_PART_DATA VALUES LESS THAN (TO_DATE('01-AUG-2018', 'dd-mon-yyyy'))
);
This will automatically create partitions on basis of created date record entered into table.
这将根据输入到表中的创建日期记录自动创建分区。
回答by Egor Skriptunoff
Missed commas between subpartition/partition definitions.
子分区/分区定义之间缺少逗号。
