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_10000VALUESLESSTHAN (10000),

PARTITIONobject_id_20000VALUESLESSTHAN (20000),

PARTITIONobject_id_30000VALUESLESSTHAN (30000),

PARTITIONobject_id_40000VALUESLESSTHAN (40000),

PARTITIONobject_id_50000VALUESLESSTHAN (50000),

PARTITIONobject_id_60000VALUESLESSTHAN (60000),

PARTITIONobject_id_maxVALUESLESSTHAN (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_synonymVALUES('SYNONYM') ,

PARTITIONt_list_javaclassVALUES('JAVA CLASS'),

PARTITIONt_list_otherVALUES(DEFAULT)

)

(

PARTITIONobject_id_10000VALUESLESSTHAN (10000),

PARTITIONobject_id_20000VALUESLESSTHAN (20000),

PARTITIONobject_id_30000VALUESLESSTHAN (30000),

PARTITIONobject_id_40000VALUESLESSTHAN (40000),

PARTITIONobject_id_50000VALUESLESSTHAN (50000),

PARTITIONobject_id_60000VALUESLESSTHAN (60000),

PARTITIONobject_id_maxVALUESLESSTHAN (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_10000VALUESLESSTHAN (10000),

PARTITIONobject_id_20000VALUESLESSTHAN (20000),

PARTITIONobject_id_30000VALUESLESSTHAN (30000),

PARTITIONobject_id_40000VALUESLESSTHAN (40000),

PARTITIONobject_id_50000VALUESLESSTHAN (50000),

PARTITIONobject_id_60000VALUESLESSTHAN (60000),

PARTITIONobject_id_maxVALUESLESSTHAN (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_synonymVALUES('SYNONYM') ,

PARTITIONt_list_javaclassVALUES('JAVA CLASS'),

PARTITIONt_list_otherVALUES(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_TABLEVALUES('TABLE') ,

PARTITIONt_list_INDEXVALUES('INDEX'),

PARTITIONt_list_otherVALUES(DEFAULT)

)

 (

PARTITIONt_list_synonymVALUES('SYNONYM') ,

PARTITIONt_list_javaclassVALUES('JAVA CLASS'),

PARTITIONt_list_otherVALUES(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_10000VALUESLESSTHAN (10000),

PARTITIONobject_id_20000VALUESLESSTHAN (20000),

PARTITIONobject_id_30000VALUESLESSTHAN (30000),

PARTITIONobject_id_40000VALUESLESSTHAN (40000),

PARTITIONobject_id_50000VALUESLESSTHAN (50000),

PARTITIONobject_id_60000VALUESLESSTHAN (60000),

PARTITIONobject_id_maxVALUESLESSTHAN (MAXVALUE)

)

 (

PARTITIONt_list_synonymVALUES('SYNONYM') ,

PARTITIONt_list_javaclassVALUES('JAVA CLASS'),

PARTITIONt_list_otherVALUES(DEFAULT)

)

 

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