oracle组合分区原理,oracle 组合分区

CREATETABLEt_range_hash

(object_idNUMBER(5),

object_nameVARCHAR2(50),

create_dateDATE,

OBJECT_TYPEVARCHAR2(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分区，这个可以使子分区的记录数目比较平衡

CREATETABLEt_range_list

(object_idNUMBER(5),

object_nameVARCHAR2(50),

create_dateDATE,

OBJECT_TYPEVARCHAR2(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，比如员工的部门，这样可以使子分区的记录数变少并且有明显的业务含义，方面查询

CREATETABLEt_range_range

(object_idNUMBER(5),

object_nameVARCHAR2(50),

create_dateDATE,

OBJECT_TYPEVARCHAR2(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分区

CREATETABLEt_list_hash

(object_idNUMBER(5),

object_nameVARCHAR2(50),

create_dateDATE,

OBJECT_TYPEVARCHAR2(20))

PARTITIONBYLIST(OBJECT_TYPE)subpartitionbyHASH(OBJECT_id)

SUBPARTITIONS4

(

PARTITIONt_list_synonymVALUES('SYNONYM') ,

PARTITIONt_list_javaclassVALUES('JAVA CLASS'),

PARTITIONt_list_otherVALUES(DEFAULT)

)

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

CREATETABLEt_list_list

(object_idNUMBER(5),

object_nameVARCHAR2(50),

create_dateDATE,

OBJECT_TYPEVARCHAR2(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分区

CREATETABLEt_list_range

(object_idNUMBER(5),

object_nameVARCHAR2(50),

create_dateDATE,

OBJECT_TYPEVARCHAR2(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分区。