oracle 组合分区

CREATETABLE t_range_hash

(object_id NUMBER(5),

object_name VARCHAR2(50),

create_date DATE,

OBJECT_TYPE VARCHAR2(20))

PARTITIONBYRANGE(object_id) subpartitionbyhash(OBJECT_TYPE)

SUBPARTITIONS4

(

PARTITIONobject_id_10000 VALUESLESSTHAN (10000),

PARTITIONobject_id_20000 VALUESLESSTHAN (20000),

PARTITIONobject_id_30000 VALUESLESSTHAN (30000),

PARTITIONobject_id_40000 VALUESLESSTHAN (40000),

PARTITIONobject_id_50000 VALUESLESSTHAN (50000),

PARTITIONobject_id_60000 VALUESLESSTHAN (60000),

PARTITIONobject_id_max VALUESLESSTHAN (MAXVALUE)

)

现实中的应用：对时间字段按范围进行分区后每个分区的记录数仍然很多，可以按很少重复的列进行hash分区，这个可以使子分区的记录数目比较平衡

 

 

CREATETABLE t_range_list

(object_id NUMBER(5),

object_name VARCHAR2(50),

create_date DATE,

OBJECT_TYPE VARCHAR2(20))

PARTITIONBYRANGE(object_id) subpartitionbylist(OBJECT_TYPE)

SUBPARTITIONSTEMPLATE(

PARTITIONt_list_synonym VALUES('SYNONYM') ,

PARTITIONt_list_javaclass VALUES('JAVA CLASS'),

PARTITIONt_list_other VALUES(DEFAULT)

)

(

PARTITIONobject_id_10000 VALUESLESSTHAN (10000),

PARTITIONobject_id_20000 VALUESLESSTHAN (20000),

PARTITIONobject_id_30000 VALUESLESSTHAN (30000),

PARTITIONobject_id_40000 VALUESLESSTHAN (40000),

PARTITIONobject_id_50000 VALUESLESSTHAN (50000),

PARTITIONobject_id_60000 VALUESLESSTHAN (60000),

PARTITIONobject_id_max VALUESLESSTHAN (MAXVALUE)

)

)

现实中的应用：对时间字段按范围进行分区后每个分区的记录数仍然很多，可以按照某个重复性比较高的类型进行list，比如员工的部门，这样可以使子分区的记录数变少并且有明显的业务含义，方面查询

 

CREATETABLE t_range_range

(object_id NUMBER(5),

object_name VARCHAR2(50),

create_date DATE,

OBJECT_TYPE VARCHAR2(20))

PARTITIONBYRANGE(object_id) subpartitionbyhash(OBJECT_TYPE)

SUBPARTITIONS4

(

PARTITIONobject_id_10000 VALUESLESSTHAN (10000),

PARTITIONobject_id_20000 VALUESLESSTHAN (20000),

PARTITIONobject_id_30000 VALUESLESSTHAN (30000),

PARTITIONobject_id_40000 VALUESLESSTHAN (40000),

PARTITIONobject_id_50000 VALUESLESSTHAN (50000),

PARTITIONobject_id_60000 VALUESLESSTHAN (60000),

PARTITIONobject_id_max VALUESLESSTHAN (MAXVALUE)

)

现实中的应用：对时间字段按年进行分区后每个分区的记录数仍然很多，可以按照月再进行range分区

 

 

CREATETABLE t_list_hash

(object_id NUMBER(5),

object_name VARCHAR2(50),

create_date DATE,

OBJECT_TYPE VARCHAR2(20))

PARTITIONBYLIST(OBJECT_TYPE) subpartitionbyHASH(OBJECT_id)

SUBPARTITIONS4

(

PARTITIONt_list_synonym VALUES('SYNONYM') ,

PARTITIONt_list_javaclass VALUES('JAVA CLASS'),

PARTITIONt_list_other VALUES(DEFAULT)

)

现实中的应用：如已经按部门进行分区了，可是子分区的记录仍然很多，为了均匀分布数据，可以用员工的id或者其他区别性比较强的字段进行hash分区

 

CREATETABLE t_list_list

(object_id NUMBER(5),

object_name VARCHAR2(50),

create_date DATE,

OBJECT_TYPE VARCHAR2(20))

PARTITIONBYLIST(OBJECT_TYPE) subpartitionbylist(OBJECT_NAME)

SUBPARTITIONSTEMPLATE(

PARTITIONt_list_TABLE VALUES('TABLE') ,

PARTITIONt_list_INDEX VALUES('INDEX'),

PARTITIONt_list_other VALUES(DEFAULT)

)

 (

PARTITIONt_list_synonym VALUES('SYNONYM') ,

PARTITIONt_list_javaclass VALUES('JAVA CLASS'),

PARTITIONt_list_other VALUES(DEFAULT)

)

现实中的应用：如已经按部门进行分区了，可是子分区的记录仍然很多，业务上经常查询部门下每个项目组的信息，就可以按项目组进行list分区

 

CREATETABLE t_list_range

(object_id NUMBER(5),

object_name VARCHAR2(50),

create_date DATE,

OBJECT_TYPE VARCHAR2(20))

PARTITIONBYLIST(OBJECT_TYPE) subpartitionbylist(OBJECT_id)

SUBPARTITIONSTEMPLATE(

PARTITIONobject_id_10000 VALUESLESSTHAN (10000),

PARTITIONobject_id_20000 VALUESLESSTHAN (20000),

PARTITIONobject_id_30000 VALUESLESSTHAN (30000),

PARTITIONobject_id_40000 VALUESLESSTHAN (40000),

PARTITIONobject_id_50000 VALUESLESSTHAN (50000),

PARTITIONobject_id_60000 VALUESLESSTHAN (60000),

PARTITIONobject_id_max VALUESLESSTHAN (MAXVALUE)

)

 (

PARTITIONt_list_synonym VALUES('SYNONYM') ,

PARTITIONt_list_javaclass VALUES('JAVA CLASS'),

PARTITIONt_list_other VALUES(DEFAULT)

)

 

现实中的应用：这个应用场景和range-list比较像，要看具体的业务，如已经按部门进行分区了，可是子分区的记录仍然很多，业务上经常查询某个时间段的信息就可以按时间range分区。