oracle range 分区访问

发布时间：2020-12-12 13:54:46 所属栏目：百科来源：网络整理

导读：explain plan for select * from esb2_trans_log t where t.trans_date = to_date('2018-06-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and t.trans_date = to_date('2018-06-07 23:59:59','yyyy-mm-dd hh24:mi:ss') ; select * from table(dbms_xplan.display(

explain plan for select * from esb2_trans_log t where t.trans_date >= to_date('2018-06-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and t.trans_date <= to_date('2018-06-07 23:59:59','yyyy-mm-dd hh24:mi:ss') ; select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT 1 Plan hash value: 1868862569 2 3 --------------------------------------------------------------------------------------------------------- 4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | 5 --------------------------------------------------------------------------------------------------------- 6 | 0 | SELECT STATEMENT | | 17M| 3526M| 150K (1)| 00:30:06 | | | 7 | 1 | PARTITION RANGE SINGLE| | 17M| 3526M| 150K (1)| 00:30:06 | 6 | 6 | 8 |* 2 | TABLE ACCESS FULL | ESB2_TRANS_LOG | 17M| 3526M| 150K (1)| 00:30:06 | 6 | 6 | 9 --------------------------------------------------------------------------------------------------------- 10 11 Predicate Information (identified by operation id): 12 --------------------------------------------------- 13 14 2 - filter("T"."TRANS_DATE"<=TO_DATE(' 2018-06-07 23:59:59','syyyy-mm-dd hh24:mi:ss')) explain plan for select * from esb2_trans_log t where t.trans_date=date'2018-06-07'; select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT 1 Plan hash value: 1868862569 2 3 --------------------------------------------------------------------------------------------------------- 4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | 5 --------------------------------------------------------------------------------------------------------- 6 | 0 | SELECT STATEMENT | | 222 | 46620 | 150K (1)| 00:30:05 | | | 7 | 1 | PARTITION RANGE SINGLE| | 222 | 46620 | 150K (1)| 00:30:05 | 6 | 6 | 8 |* 2 | TABLE ACCESS FULL | ESB2_TRANS_LOG | 222 | 46620 | 150K (1)| 00:30:05 | 6 | 6 | 9 --------------------------------------------------------------------------------------------------------- 10 11 Predicate Information (identified by operation id): 12 --------------------------------------------------- 13 14 2 - filter("T"."TRANS_DATE"=TO_DATE(' 2018-06-07 00:00:00','syyyy-mm-dd hh24:mi:ss')) select /*+parallel(a 8)*/ a.trans_date from esb2_trans_log a where a.esbflowno in ( select t.esbflowno from esb2_trans_log t where t.trans_date=date'2018-06-07' ) 2018/6/6 23:59:59 2018/6/6 23:59:59 2018/6/7 2018/6/7 0:00:01 2018/6/7 0:00:01 2018/6/7 0:00:01 2018/6/7 0:00:03 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 0:00:01 2018/6/7 0:00:01 2018/6/7 0:00:01 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 2018/6/7 0:00:01 ESBFLOWNO FLOWSTEPID ESBSERVICEFLOWNO ESBSERVSEQU REQFLOWNO RESPFLOWNO SERVICETYPE TRANSTAMP TRANSTAMP1 TRANSTAMP2 TRANSTAMP3 TRANSTAMP4 LOCATIONID CHANNELID SERVICEID RESPSTATUS RESPCODE RESPMSG OPERSTAMP PREFLOWNO POSTFLOWNO LOGICCHANNEL REALCHANNEL SERVICEFLOW LOGICSYSTEM REALSYSTEM TRANS_DATE LOOP 1 esbapp1-esb_in-20180607000000-999114 1 2018-06-07 00:00:00.602 2018-06-07 00:00:00.602 esb_in FDS 0300300002402 1 07-6月 -18 12.00.00.917 上午 FDS 2018/6/7 esbapp1 2 esbapp1-esb_in-20180607000000-999114 2 1 2018-06-07 00:00:00.960 2018-06-07 00:00:00.960 esb_out FDS 0300300002402 1 07-6月 -18 12.00.01.917 上午 FDS ELINK 2018/6/7 0:00:01 3 esbapp1-esb_in-20180607000000-999114 3 1 2018-06-07 00:00:00.984 2018-06-07 00:00:00.960 2018-06-07 00:00:00.984 esb_out FDS 0300300002402 1 07-6月 -18 12.00.01.918 上午 FDS ELINK 2018/6/7 0:00:01 4 esbapp1-esb_in-20180607000000-999114 4 21503020180606010023839301 50010120180607010000876368 21503020180606010023839301 2018-06-07 00:00:00.990 2018-06-07 00:00:00.602 2018-06-07 00:00:00.960 2018-06-07 00:00:00.984 2018-06-07 00:00:00.990 esb_in FDS 0300300002402 1 9999 前置无记录[100] 07-6月 -18 12.00.01.918 上午 FDS ELINK 2018/6/7 0:00:01 esbapp1 -- Create table create table ESB2_TRANS_LOG ( esbflowno VARCHAR2(256) not null,flowstepid VARCHAR2(3) not null,esbserviceflowno VARCHAR2(52),esbservsequ VARCHAR2(52),reqflowno VARCHAR2(52),respflowno VARCHAR2(52),servicetype CHAR(1),transtamp VARCHAR2(30) not null,transtamp1 VARCHAR2(30),transtamp2 VARCHAR2(30),transtamp3 VARCHAR2(30),transtamp4 VARCHAR2(30),locationid VARCHAR2(20) not null,channelid VARCHAR2(40),serviceid VARCHAR2(40) default 'NULL',respstatus CHAR(1),respcode VARCHAR2(64),respmsg VARCHAR2(4000),operstamp TIMESTAMP(3) default systimestamp not null,preflowno VARCHAR2(52),postflowno VARCHAR2(52),logicchannel VARCHAR2(40),realchannel VARCHAR2(40),serviceflow VARCHAR2(40),logicsystem VARCHAR2(40),realsystem VARCHAR2(40),trans_date DATE default sysdate not null,loop VARCHAR2(20) ) partition by range (TRANS_DATE) ( partition ESB2_TRANS_LOG_180602 values less than (TO_DATE(' 2018-06-03 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace ESBTRANS03_DATA_TBS_03 pctfree 10 initrans 1 maxtrans 255 storage ( initial 8M next 1M minextents 1 maxextents unlimited ),

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!