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
),