PLAN_TABLE_OUTPUT
Plan hash value: 2272901224
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 708 | 30444 | 1521 (2)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | VIEW | | 14159 | 594K| 1519 (2)| 00:00:01 |
| 3 | SORT UNIQUE | | 14159 | 746K| 1519 (2)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| BTOP_TELLER_TELLERLOGIN | 14159 | 746K| 1517 (1)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | BTOP_TELLER_TELLERLOGIN | 2 | 74 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "BTOP_TELLER_TELLERLOGIN" "A" WHERE
TO_DATE("A"."LOGONTIME",'yyyy-mm-dd HH24:MI:SS')>TO_DATE(' 2017-12-08 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND LNNVL(REPLACE("A"."CLIENTIP",SUBSTR("A"."CLIENTIP",INSTR("
A"."CLIENTIP",':')))<>:B1)))
4 - filter(TO_DATE("A"."LOGONTIME",'yyyy-mm-dd HH24:MI:SS')<TO_DATE(' 2017-12-08
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
5 - filter(TO_DATE("A"."LOGONTIME",'yyyy-mm-dd HH24:MI:SS')>TO_DATE(' 2017-12-08
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND LNNVL(REPLACE("A"."CLIENTIP",SUBSTR("A"."CLIENT
IP",INSTR("A"."CLIENTIP",':')))<>:B1))
explain plan for select *
from (select distinct (replace(a.clientip,
substr(a.clientip, instr(a.clientip, ':')))) as ip,
a.orgid,
a.tellerid
from btop_teller_tellerlogin a
where to_date(a.logontime, 'yyyy-mm-dd HH24:MI:SS') <
to_date('2017-12-08', 'yyyy-mm-dd')) c
where c.ip not in
(select distinct (replace(a.clientip,
substr(a.clientip, instr(a.clientip, ':'))))
from btop_teller_tellerlogin a
where to_date(a.logontime, 'yyyy-mm-dd HH24:MI:SS') >
to_date('2017-12-08', 'yyyy-mm-dd'));
select count(*) from (select distinct (replace(a.clientip,
substr(a.clientip, instr(a.clientip, ':')))) as ip,
a.orgid,
a.tellerid
from btop_teller_tellerlogin a
where to_date(a.logontime, 'yyyy-mm-dd HH24:MI:SS') <
to_date('2017-12-08', 'yyyy-mm-dd'))
--9088
create index btop_teller_tellerlogin_idx1 on btop_teller_tellerlogin(to_date(logontime, 'yyyy-mm-dd HH24:MI:SS')) online;
Plan hash value: 2733609535
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2700 |00:00:08.65 | 2415K| | | |
|* 1 | FILTER | | 1 | | 2700 |00:00:08.65 | 2415K| | | |
| 2 | VIEW | | 1 | 14159 | 9088 |00:00:01.52 | 255K| | | |
| 3 | SORT UNIQUE | | 1 | 14159 | 9088 |00:00:01.52 | 255K| 761K| 761K| 676K (0)|
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| BTOP_TELLER_TELLERLOGIN | 1 | 14159 | 296K|00:00:00.72 | 255K| | | |
|* 5 | INDEX RANGE SCAN | BTOP_TELLER_TELLERLOGIN_IDX1 | 1 | 2692 | 296K|00:00:00.11 | 789 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED | BTOP_TELLER_TELLERLOGIN | 1636 | 2 | 1011 |00:00:07.12 | 2160K| | | |
|* 7 | INDEX RANGE SCAN | BTOP_TELLER_TELLERLOGIN_IDX1 | 1636 | | 2798K|00:00:00.83 | 8150 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
5 - access("A"."SYS_NC00010$"<TO_DATE(' 2017-12-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
6 - filter(LNNVL(REPLACE("A"."CLIENTIP",SUBSTR("A"."CLIENTIP",INSTR("A"."CLIENTIP",':')))<>:B1))
7 - access("A"."SYS_NC00010$">TO_DATE(' 2017-12-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
37 rows selected.
SQL> select segment_name,bytes/1024/1024 from dba_segments a where a.segment_name=upper('BTOP_TELLER_TELLERLOGIN');
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
BTOP_TELLER_TELLERLOGIN
52
本来需要访问的数据为 1636*52M=83G