select *
from mk_task_info_10032 b,
(SELECT s.task_id,
s.chn_type,
s.contact_flag,
s.accept_flag,
s.fail_reason,
s.service_name,
s.oper_date,
s.note
FROM (SELECT task_id,
chn_type,
contact_flag,
accept_flag,
fail_reason,
service_name,
oper_date,
note,
ROW_NUMBER() OVER(PARTITION BY task_id ORDER BY exec_id DESC) rm
FROM mk_taskexec_info_10032
WHERE 1 = 1
and task_class in ('88')) s
WHERE s.rm = 1) c,
mk_act_info a,
mk_contactfail_dict d,
(SELECT s.task_id, s.oper_no, s.oper_name
FROM (SELECT task_id,
oper_no,
oper_name,
ROW_NUMBER() OVER(PARTITION BY task_id ORDER BY assign_id DESC) rm
FROM mk_TaskAssign_trac) s
WHERE s.rm = 1) e,
mk_taskstatus_dict f,
mk_regioncode_dict g,
mk_chntype_dict h,
mk_acceptflag_dict i,
mk_userinfo j,
dvpncustmsg o,
dcusthigh k,
dalertrulemsg m
where b.act_id = a.act_id(+)
and b.task_id = c.task_id(+)
and b.task_id = e.task_id(+)
and c.fail_reason = d.fail_code(+)
and (b.vpn_group_flag is null or b.vpn_group_flag = '1')
and b.status_code = f.status_code(+)
and b.region_code = g.region_code(+)
and c.chn_type = h.chn_type(+)
and b.id_no = j.user_id(+)
and c.accept_flag = i.accept_flag(+)
and b.id_no = k.id_no(+)
and k.unit_id = o.unit_id(+)
and b.task_class = m.rule_id(+)
and b.region_code = '10032'
and b.mngr_service_no in ('zqhx531')
and b.status_code in ('04')
and b.task_class in ('88')
and to_char(c.oper_date, 'yyyymmdd') >= '20140501'
and to_char(c.oper_date, 'yyyymmdd') <= '20140506'
and to_char(b.start_date, 'yyyymmdd') >= '20140501'
and to_char(b.start_date, 'yyyymmdd') <= '20140605'
and to_char(b.end_date, 'yyyymmdd') >= '20140501'
and to_char(b.end_date, 'yyyymmdd') <= '20140603'
and c.accept_flag in ('0')
and m.sort_id = '2'
and b.phone_no like ltrim(rtrim('13333442043')) || '%'
and o.unit_name like '%' || trim('榆社县华能电厂') || '%'
and o.scale_flag = '3'
and o.unit_code = '3'
and case when o.unit_code in ('1', '2', '3')
and o.scale_flag in ('1', '2', '3', '7', '8') then '是' when o.unit_code in ('0') then '否' when o.scale_flag in ('4', '5', '6') then '否' end = '是' and
o.photo_code = '1' and k.photo_code_zq = '1'
Plan hash value: 1487381673
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2782 | 53 (4)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY LOCAL INDEX ROWID | DVPNCUSTMSG | 1 | 48 | 21 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 2782 | 53 (4)| 00:00:01 | | |
| 3 | NESTED LOOPS OUTER | | 1 | 2734 | 32 (7)| 00:00:01 | | |
| 4 | NESTED LOOPS OUTER | | 1 | 2671 | 27 (4)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 1 | 2651 | 23 (5)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 2637 | 20 (5)| 00:00:01 | | |
| 7 | NESTED LOOPS OUTER | | 1 | 2631 | 19 (6)| 00:00:01 | | |
| 8 | MERGE JOIN OUTER | | 1 | 2593 | 18 (6)| 00:00:01 | | |
| 9 | MERGE JOIN OUTER | | 1 | 2582 | 17 (6)| 00:00:01 | | |
| 10 | NESTED LOOPS OUTER | | 1 | 2571 | 16 (7)| 00:00:01 | | |
| 11 | NESTED LOOPS OUTER | | 1 | 2557 | 15 (7)| 00:00:01 | | |
| 12 | MERGE JOIN OUTER | | 1 | 2542 | 14 (8)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 1 | 2530 | 13 (8)| 00:00:01 | | |
|* 14 | TABLE ACCESS BY GLOBAL INDEX ROWID | MK_TASK_INFO_10032 | 1 | 173 | 6 (0)| 00:00:01 | ROWID | ROWID |
|* 15 | INDEX RANGE SCAN | IDX_TASK_PHONE_NO_10032 | 3 | | 3 (0)| 00:00:01 | | |
|* 16 | VIEW PUSHED PREDICATE | | 1 | 2357 | 7 (15)| 00:00:01 | | |
|* 17 | WINDOW SORT PUSHED RANK | | 1 | 125 | 7 (15)| 00:00:01 | | |
|* 18 | TABLE ACCESS BY GLOBAL INDEX ROWID| MK_TASKEXEC_INFO_10032 | 1 | 125 | 6 (0)| 00:00:01 | ROWID | ROWID |
|* 19 | INDEX RANGE SCAN | IDX_TASKEXEC_TASK_ID_10032 | 1 | | 4 (0)| 00:00:01 | | |
| 20 | BUFFER SORT | | 1 | 12 | 7 (0)| 00:00:01 | | |
| 21 | TABLE ACCESS BY INDEX ROWID | MK_ACCEPTFLAG_DICT | 1 | 12 | 1 (0)| 00:00:01 | | |
|* 22 | INDEX UNIQUE SCAN | PK_MK_ACCEPTFLAG_DICT | 1 | | 0 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS BY INDEX ROWID | MK_CHNTYPE_DICT | 1 | 15 | 1 (0)| 00:00:01 | | |
|* 24 | INDEX UNIQUE SCAN | PK_MK_CHNTYPE_DICT | 1 | | 0 (0)| 00:00:01 | | |
| 25 | TABLE ACCESS BY INDEX ROWID | MK_CONTACTFAIL_DICT | 1 | 14 | 1 (0)| 00:00:01 | | |
|* 26 | INDEX UNIQUE SCAN | PK_MK_CONTACTFAIL_DICT | 1 | | 0 (0)| 00:00:01 | | |
| 27 | BUFFER SORT | | 1 | 11 | 16 (7)| 00:00:01 | | |
| 28 | TABLE ACCESS BY INDEX ROWID | MK_REGIONCODE_DICT | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 29 | INDEX UNIQUE SCAN | PK_MK_REGIONCODE_DICT | 1 | | 0 (0)| 00:00:01 | | |
| 30 | BUFFER SORT | | 1 | 11 | 17 (6)| 00:00:01 | | |
| 31 | TABLE ACCESS BY INDEX ROWID | MK_TASKSTATUS_DICT | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 32 | INDEX UNIQUE SCAN | PK_MK_TASKSTATUS_DICT | 1 | | 0 (0)| 00:00:01 | | |
| 33 | TABLE ACCESS BY INDEX ROWID | MK_ACT_INFO | 1 | 38 | 1 (0)| 00:00:01 | | |
|* 34 | INDEX UNIQUE SCAN | PK_MK_ACT_INFO | 1 | | 0 (0)| 00:00:01 | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | DALERTRULEMSG | 1 | 6 | 1 (0)| 00:00:01 | | |
|* 36 | INDEX RANGE SCAN | IDX_RULEID | 1 | | 0 (0)| 00:00:01 | | |
|* 37 | TABLE ACCESS BY GLOBAL INDEX ROWID | DCUSTHIGH | 1 | 14 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 38 | INDEX RANGE SCAN | PK_DCUSTHIGH_ID | 1 | | 2 (0)| 00:00:01 | | |
| 39 | TABLE ACCESS BY GLOBAL INDEX ROWID | MK_USERINFO | 1 | 20 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 40 | INDEX RANGE SCAN | IDX_MK_USERINFO_USER_ID | 1 | | 2 (0)| 00:00:01 | | |
|* 41 | VIEW PUSHED PREDICATE | | 1 | 63 | 5 (20)| 00:00:01 | | |
|* 42 | WINDOW SORT PUSHED RANK | | 1 | 45 | 5 (20)| 00:00:01 | | |
| 43 | TABLE ACCESS BY INDEX ROWID | MK_TASKASSIGN_TRAC | 1 | 45 | 4 (0)| 00:00:01 | | |
|* 44 | INDEX RANGE SCAN | INDEX_TASK_ID_TRAC | 1 | | 3 (0)| 00:00:01 | | |
| 45 | PARTITION LIST ALL | | 7 | | 14 (0)| 00:00:01 | 1 | 14 |
|* 46 | INDEX RANGE SCAN | IDX_UNITID_DVPNCUSTMSG | 7 | | 14 (0)| 00:00:01 | 1 | 14 |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("O"."SCALE_FLAG"='3' AND "O"."UNIT_CODE"='3' AND "O"."PHOTO_CODE"='1' AND "O"."UNIT_NAME" LIKE '%榆社县华能电厂%' AND CASE WHEN
(("O"."UNIT_CODE"='1' OR "O"."UNIT_CODE"='2' OR "O"."UNIT_CODE"='3') AND ("O"."SCALE_FLAG"='1' OR "O"."SCALE_FLAG"='2' OR
"O"."SCALE_FLAG"='3' OR "O"."SCALE_FLAG"='7' OR "O"."SCALE_FLAG"='8')) THEN '是' WHEN "O"."UNIT_CODE"='0' THEN '否' WHEN
("O"."SCALE_FLAG"='4' OR "O"."SCALE_FLAG"='5' OR "O"."SCALE_FLAG"='6') THEN '否' END ='是')
14 - filter("B"."MNGR_SERVICE_NO"='zqhx531' AND "B"."TASK_CLASS"='88' AND "B"."STATUS_CODE"='04' AND TO_NUMBER("B"."TASK_CLASS")=88
AND TO_CHAR(INTERNAL_FUNCTION("B"."START_DATE"),'yyyymmdd')>='20140501' AND
TO_CHAR(INTERNAL_FUNCTION("B"."START_DATE"),'yyyymmdd')<='20140605' AND
TO_CHAR(INTERNAL_FUNCTION("B"."END_DATE"),'yyyymmdd')>='20140501' AND TO_CHAR(INTERNAL_FUNCTION("B"."END_DATE"),'yyyymmdd')<='20140603'
AND ("B"."VPN_GROUP_FLAG" IS NULL OR "B"."VPN_GROUP_FLAG"='1') AND "B"."REGION_CODE"='10032')
15 - access("B"."PHONE_NO" LIKE '13333442043%')
filter("B"."PHONE_NO" LIKE '13333442043%')
16 - filter(TO_CHAR(INTERNAL_FUNCTION("S"."OPER_DATE"),'yyyymmdd')>='20140501' AND
TO_CHAR(INTERNAL_FUNCTION("S"."OPER_DATE"),'yyyymmdd')<='20140506' AND "S"."ACCEPT_FLAG"='0' AND "S"."RM"=1)
17 - filter(ROW_NUMBER() OVER ( PARTITION BY "TASK_ID" ORDER BY INTERNAL_FUNCTION("EXEC_ID") DESC )<=1)
18 - filter("TASK_CLASS"='88')
19 - access("TASK_ID"="B"."TASK_ID")
22 - access("I"."ACCEPT_FLAG"(+)='0')
24 - access("S"."CHN_TYPE"="H"."CHN_TYPE"(+))
26 - access("S"."FAIL_REASON"="D"."FAIL_CODE"(+))
29 - access("G"."REGION_CODE"(+)='10032')
32 - access("F"."STATUS_CODE"(+)='04')
34 - access("B"."ACT_ID"="A"."ACT_ID"(+))
35 - filter("M"."SORT_ID"=2)
36 - access("M"."RULE_ID"=88)
filter("M"."RULE_ID"=TO_NUMBER("B"."TASK_CLASS"))
37 - filter("K"."PHOTO_CODE_ZQ"='1')
38 - access("K"."ID_NO"=TO_NUMBER("B"."ID_NO"))
40 - access("B"."ID_NO"="J"."USER_ID"(+))
41 - filter("S"."RM"(+)=1)
42 - filter(ROW_NUMBER() OVER ( PARTITION BY "TASK_ID" ORDER BY INTERNAL_FUNCTION("ASSIGN_ID") DESC )<=1)
44 - access("TASK_ID"="B"."TASK_ID")
46 - access("K"."UNIT_ID"="O"."UNIT_ID")
这里
| 13 | NESTED LOOPS | | 1 | 2530 | 13 (8)| 00:00:01 | | |
|* 14 | TABLE ACCESS BY GLOBAL INDEX ROWID | MK_TASK_INFO_10032 | 1 | 173 | 6 (0)| 00:00:01 | ROWID | ROWID |
|* 15 | INDEX RANGE SCAN | IDX_TASK_PHONE_NO_10032 | 3 | | 3 (0)| 00:00:01 | | |
|* 16 | VIEW PUSHED PREDICATE | | 1 | 2357 | 7 (15)| 00:00:01 | | |
ID=16
16 - filter(TO_CHAR(INTERNAL_FUNCTION("S"."OPER_DATE"),'yyyymmdd')>='20140501' AND
TO_CHAR(INTERNAL_FUNCTION("S"."OPER_DATE"),'yyyymmdd')<='20140506' AND "S"."ACCEPT_FLAG"='0' AND "S"."RM"=1)
来自
(SELECT task_id,
chn_type,
contact_flag,
accept_flag,
fail_reason,
service_name,
oper_date,
note,
ROW_NUMBER() OVER(PARTITION BY task_id ORDER BY exec_id DESC) rm
FROM mk_taskexec_info_10032
WHERE 1 = 1
and task_class in ('88')) s
WHERE s.rm = 1) c
and to_char(c.oper_date, 'yyyymmdd') >= '20140501'
and to_char(c.oper_date, 'yyyymmdd') <= '20140506'
and c.accept_flag in ('0')
视图来自C 表,那么这里谓词推入作为了NL的被驱动表,直接use_hash(b c)
