Oracle之分区表效率与普通表比较
--范围分区示例
drop table range_part_tab purge;
--注意，此分区为范围分区
create table range_part_tab (id number,deal_date date,area_code number,contents varchar2(4000))
partition by range (deal_date)
(
partition p1 values less than (TO_DATE(2017-02-01, YYYY-MM-DD)),
partition p2 values less than (TO_DATE(2017-03-01, YYYY-MM-DD)),
partition p3 values less than (TO_DATE(2017-04-01, YYYY-MM-DD)),
partition p4 values less than (TO_DATE(2017-05-01, YYYY-MM-DD)),
partition p5 values less than (TO_DATE(2017-06-01, YYYY-MM-DD)),
partition p6 values less than (TO_DATE(2017-07-01, YYYY-MM-DD)),
partition p7 values less than (TO_DATE(2017-08-01, YYYY-MM-DD)),
partition p8 values less than (TO_DATE(2017-09-01, YYYY-MM-DD)),
partition p9 values less than (TO_DATE(2017-10-01, YYYY-MM-DD)),
partition p10 values less than (TO_DATE(2017-11-01, YYYY-MM-DD)),
partition p11 values less than (TO_DATE(2017-12-01, YYYY-MM-DD)),
partition p12 values less than (TO_DATE(2018-01-01, YYYY-MM-DD)),
partition p_max values less than (maxvalue)
)
;
--以下是插入2017年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_tab (id,deal_date,area_code,contents)
select rownum,
to_date( to_char(sysdate-365,J)+TRUNC(DBMS_RANDOM.VALUE(0,365)),J),
ceil(dbms_random.value(590,599)),
rpad(*,400,*)
from dual
connect by rownum <= 100000;
commit;
--分区原理分析之普通表插入
drop table norm_tab purge;
create table norm_tab (id number,deal_date date,area_code number,contents varchar2(4000));
insert into norm_tab(id,deal_date,area_code,contents)
select rownum,
to_date( to_char(sysdate-365,J)+TRUNC(DBMS_RANDOM.VALUE(0,365)),J),
ceil(dbms_random.value(590,599)),
rpad(*,400,*)
from dual
connect by rownum <= 100000;
commit;
set linesize 1000
set autotrace on
drop table range_part_tab purge;
--注意，此分区为范围分区
create table range_part_tab (id number,deal_date date,area_code number,contents varchar2(4000))
partition by range (deal_date)
(
partition p1 values less than (TO_DATE(2017-02-01, YYYY-MM-DD)),
partition p2 values less than (TO_DATE(2017-03-01, YYYY-MM-DD)),
partition p3 values less than (TO_DATE(2017-04-01, YYYY-MM-DD)),
partition p4 values less than (TO_DATE(2017-05-01, YYYY-MM-DD)),
partition p5 values less than (TO_DATE(2017-06-01, YYYY-MM-DD)),
partition p6 values less than (TO_DATE(2017-07-01, YYYY-MM-DD)),
partition p7 values less than (TO_DATE(2017-08-01, YYYY-MM-DD)),
partition p8 values less than (TO_DATE(2017-09-01, YYYY-MM-DD)),
partition p9 values less than (TO_DATE(2017-10-01, YYYY-MM-DD)),
partition p10 values less than (TO_DATE(2017-11-01, YYYY-MM-DD)),
partition p11 values less than (TO_DATE(2017-12-01, YYYY-MM-DD)),
partition p12 values less than (TO_DATE(2018-01-01, YYYY-MM-DD)),
partition p_max values less than (maxvalue)
)
;
--以下是插入2017年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_tab (id,deal_date,area_code,contents)
select rownum,
to_date( to_char(sysdate-365,J)+TRUNC(DBMS_RANDOM.VALUE(0,365)),J),
ceil(dbms_random.value(590,599)),
rpad(*,400,*)
from dual
connect by rownum <= 100000;
commit;
--分区原理分析之普通表插入
drop table norm_tab purge;
create table norm_tab (id number,deal_date date,area_code number,contents varchar2(4000));
insert into norm_tab(id,deal_date,area_code,contents)
select rownum,
to_date( to_char(sysdate-365,J)+TRUNC(DBMS_RANDOM.VALUE(0,365)),J),
ceil(dbms_random.value(590,599)),
rpad(*,400,*)
from dual
connect by rownum <= 100000;
commit;
set linesize 1000
set autotrace on
select count(*) from norm_tab where deal_date>=TO_DATE(2017-09-01, YYYY-MM-DD)
and deal_date <= TO_DATE(2017-09-30, YYYY-MM-DD);
select count(*) from range_part_tab where deal_date>=TO_DATE(2017-09-01, YYYY-MM-DD)
and deal_date <= TO_DATE(2017-10-30, YYYY-MM-DD);