数据库表分区
场景: 现实业务中有两个表关系比较紧密,而且数据量比较大的时候,需要对两个表都进行分区,并能很好的发挥分表作用
创建分区数据库表
注意: 数据库表最好是在创建的时候就进行分区,不能对已经创建的普通表(堆表)再进行分区变为分区表,否则转换起来比较麻烦。
create table OCS_COLLECT_PEOPLE
(
COLLECT_ID VARCHAR2(32) not null,
PEOPLE_ID VARCHAR2(20) not null,
PEOPLE_VERSION NUMBER(2) not null,
ACCOUNT_VERSION NUMBER(2),
PROJECT_ID VARCHAR2(20) not null,
REGION_ID VARCHAR2(12) not null,
BATCH_ID VARCHAR2(32) not null,
MONEY NUMBER(12,3) not null,
OPERATOR_ID VARCHAR2(16) not null,
OPERATOR_TIME DATE not null,
ASSIGN_ID VARCHAR2(30),
AUDIT_ID VARCHAR2(25),
PAY_ID VARCHAR2(30),
STATUS NUMBER(2) not null
) PARTITION BY RANGE(OPERATOR_TIME)
(
PARTITION CP_2012 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')) tablespace XT_TEST_P1,
PARTITION CP_2013 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD')) tablespace XT_TEST_P2
);
create table OCS_COLLECT_PEOPLE_PROP
(
COLLECT_ID VARCHAR2(32) not null,
FLOAT_VALUE NUMBER(12,3),
INT_VALUE INTEGER,
STR_VALUE VARCHAR2(100),
DATE_VALUE DATE,
PROP_NAME VARCHAR2(20) not null,
OPERATOR_TIME DATE not null
) PARTITION BY RANGE(OPERATOR_TIME)
(
PARTITION CPP_2012 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')) tablespace XT_TEST_P1,
PARTITION CPP_2013 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD')) tablespace XT_TEST_P2
);
使用比较
结论: 如果想使用两个表的分区功能,必须把两个表的分区字段使用上,才能达到最好效果,如果只使用一个表的分区字段,那么有一个表会使用分区优势,如下:
SELECT * FROM OCS_COLLECT_PEOPLE P,OCS_COLLECT_PEOPLE_PROP PP
WHERE PP.OPERATOR_TIME=P.OPERATOR_TIME AND P.COLLECT_ID=PP.COLLECT_ID
AND P.OPERATOR_TIME>TO_DATE('2013-01-01','YYYY-MM-DD');--比下面的SQL好,从执行计划上看cardinality值和bytes值都小了许多SELECT * FROM OCS_COLLECT_PEOPLE P,OCS_COLLECT_PEOPLE_PROP PP
WHERE PP.OPERATOR_TIME>TO_DATE('2013-01-01','YYYY-MM-DD') AND P.COLLECT_ID=PP.COLLECT_ID
AND P.OPERATOR_TIME>TO_DATE('2013-01-01','YYYY-MM-DD')
数据库表分区
场景: 现实业务中有两个表关系比较紧密,而且数据量比较大的时候,需要对两个表都进行分区,并能很好的发挥分表作用
创建分区数据库表
注意: 数据库表最好是在创建的时候就进行分区,不能对已经创建的普通表(堆表)再进行分区变为分区表,否则转换起来比较麻烦。
create table OCS_COLLECT_PEOPLE
(
COLLECT_ID VARCHAR2(32) not null,
PEOPLE_ID VARCHAR2(20) not null,
PEOPLE_VERSION NUMBER(2) not null,
ACCOUNT_VERSION NUMBER(2),
PROJECT_ID VARCHAR2(20) not null,
REGION_ID VARCHAR2(12) not null,
BATCH_ID VARCHAR2(32) not null,
MONEY NUMBER(12,3) not null,
OPERATOR_ID VARCHAR2(16) not null,
OPERATOR_TIME DATE not null,
ASSIGN_ID VARCHAR2(30),
AUDIT_ID VARCHAR2(25),
PAY_ID VARCHAR2(30),
STATUS NUMBER(2) not null
) PARTITION BY RANGE(OPERATOR_TIME)
(
PARTITION CP_2012 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')) tablespace XT_TEST_P1,
PARTITION CP_2013 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD')) tablespace XT_TEST_P2
);
create table OCS_COLLECT_PEOPLE_PROP
(
COLLECT_ID VARCHAR2(32) not null,
FLOAT_VALUE NUMBER(12,3),
INT_VALUE INTEGER,
STR_VALUE VARCHAR2(100),
DATE_VALUE DATE,
PROP_NAME VARCHAR2(20) not null,
OPERATOR_TIME DATE not null
) PARTITION BY RANGE(OPERATOR_TIME)
(
PARTITION CPP_2012 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')) tablespace XT_TEST_P1,
PARTITION CPP_2013 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD')) tablespace XT_TEST_P2
);
使用比较
结论: 如果想使用两个表的分区功能,必须把两个表的分区字段使用上,才能达到最好效果,如果只使用一个表的分区字段,那么有一个表会使用分区优势,如下:
SELECT * FROM OCS_COLLECT_PEOPLE P,OCS_COLLECT_PEOPLE_PROP PP
WHERE PP.OPERATOR_TIME=P.OPERATOR_TIME AND P.COLLECT_ID=PP.COLLECT_ID
AND P.OPERATOR_TIME>TO_DATE('2013-01-01','YYYY-MM-DD');--比下面的SQL好,从执行计划上看cardinality值和bytes值都小了许多SELECT * FROM OCS_COLLECT_PEOPLE P,OCS_COLLECT_PEOPLE_PROP PP
WHERE PP.OPERATOR_TIME>TO_DATE('2013-01-01','YYYY-MM-DD') AND P.COLLECT_ID=PP.COLLECT_ID
AND P.OPERATOR_TIME>TO_DATE('2013-01-01','YYYY-MM-DD')