oracle 分区检索快吗,【性能优化】分区表查询，怎样更快呢？-优快云博客

careate table tmp(pk_id number(38),dta_date date)

partition by range (DTA_DATE)

(

partition PAR_201301 values less than (TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201302 values less than (TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201303 values less than (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201304 values less than (TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201305 values less than (TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201306 values less than (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201307 values less than (TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201308 values less than (TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201309 values less than (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_COMMON_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201310 values less than (TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_COMMON_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201311 values less than (TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201312 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201401 values less than (TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201402 values less than (TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201403 values less than (TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201404 values less than (TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201405 values less than (TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201406 values less than (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201407 values less than (TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201408 values less than (TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201409 values less than (TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_COMMON_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201410 values less than (TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_COMMON_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201411 values less than (TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201412 values less than (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 8M

next 1M

minextents 1

maxextents unlimited

partition PAR_201501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255,

partition PAR_201502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

tablespace PRD_OSOR_DAT01

pctfree 10

initrans 1

maxtrans 255

);