请教一个SQL调优

显示全部楼层 · 2014-3-4 06:00:13

sql_plan.sql(20.29 KB, 下载次数: 7)2014-9-16 17:25 上传点击文件名下载附件

sql.txt(8.82 KB, 下载次数: 7)2014-9-16 17:25 上传点击文件名下载附件

千问 · 2014-3-4 06:00:13

SELECT *
FROM (SELECT /*+ USE_HASH(T1) */
DISTINCT T1.STATUS,

T1.CREATETIME,

T1.TICKETTITLE,

T1.REGION,

T1.ISURGENTFAULT,

T1.DEALLIMITEDTIME,

T1.BIZ_LASTOPERATORNAME,

T1.CREATEDEPTFULLNAME,

T1.CREATEPERSONNAME,

T1.ALARMTYPE,

T1.ISDISPATCHPROVICE,

T1.ALARMOBJ,

T1.ALARMLOGICSUBCLASS,

T1.MESHWORKTYPE,

T1.FLOWTYPE,

T1.ALARMLEVEL,

T1.FAULTDEALCITY,

T1.TIMEOUT,

T1.ISEFFACTBIZ,

T1.ISGROUPBUSINESS,

T1.ISPUBLISHINFO,

T1.TICKETID,

T1.ALARMRELATAG,

T1.ALARMLOGICCLASS,

T1.EQUPMANUFACTURER,

T1.AREA,

T1.ISDELAYVALID,

T1.FAULTOCCURCITY,

T1.STARLEVEL,

T1.QUERYALARMELIMINATETIME,

T1.ID,

T1.BILLMAINID,

T1.PROCESSID,

T1.PROCESSINSTID,

T1.BIZ_OVERTIME,

T1.BIZ_NOTICETIME,

T1.BIZ_WORKITEMID,

T1.ALARMLEVELNAME,

T1.STARLEVELNAME,

T1.FAULTREASON,

T1.ALARMELIMINATETIME,

T1.LIMITEDTIME,

T1.STATUSNAME,

T1.PARTICIPANTORGNAME,

T1.CREATEDPT,

T1.ISDELAYEFFECT,

T1.ISGROUPBUSINESSNAME,

T1.LASTEDITTIME,

WFWORKITEM.PROCESSINSTNAME,

WFWORKITEM.ACTIVITYINSTID,

WFWORKITEM.ACTIVITYINSTNAME,

WFWORKITEM.PROCESSDEFID,

WFWORKITEM.PROCESSDEFNAME,

WFWORKITEM.PROCESSCHNAME,

WFWORKITEM.ACTIVITYDEFID,

WFWORKITEM.CURRENTSTATE,

WFWORKITEM.WORKITEMID,

WFWORKITEM.STARTTIME,

WFWORKITEM.FINALTIME,

WFWORKITEM.ISTIMEOUT,

WFWORKITEM.EXTEND2,

WFWORKITEM.PARTICIPANT

FROM V_GZCL_TICKET4DB T1, WFWORKITEM, WFWIPARTICIPANT
WHERE T1.PROCESSINSTID = WFWORKITEM.PROCESSINSTID
AND WFWORKITEM.WORKITEMID = WFWIPARTICIPANT.WORKITEMID
AND ((PROCESSID = 23) AND (1 = 1))
AND (WFWORKITEM.WORKITEMID = WFWIPARTICIPANT.WORKITEMID AND

((WFWORKITEM.CURRENTSTATE = 4 AND

WFWIPARTICIPANT.PARTIINTYPE = 'GET' AND

(((WFWIPARTICIPANT.EXTEND1 IN

('P{zhangdian}',

'O{104434}',

'O{104852}',

'R{TSCL/T1_PROCESSOR/PROV_CQWJJL1320636857215__DPT}',

'R{TSCL/T1_PROCESSOR/PROV_GZL1320636813164__DPT}',

'R{TSCL/T1_PROCESSOR/PROV_JK-TYZY1305701985343__DPT}',

'R{TSCL/T1_PROCESSOR/PROV_PTTSL1320636840921__DPT}',

'R{TSCL/T1_PROCESSOR/PROV_SJTSL1320636704846__DPT}',

'R{TSCL/T1_PROCESSOR/PROV_XZDSJL1320636741727__DPT}',

'R{TSCL/T1_PROCESSOR/PROV_ZDSJYJL1320636760774__DPT}',

'R{TSCL_PARTICIPANT/APPLY_FINISH}',

'R{emip_dutymgr_cfgmgr}',

'R{emip_gdgl_monitor}',

'R{emip_gjrw_jks_GD}',

'R{emip_gzcl_fatconfig}',

'R{emip_gzcl_ltconfig}',

'R{emip_gzcl_netconfig}',

'R{emip_gzcl_wfrole}',

'R{emip_jjgz_monitor}',

'R{emip_jsj_wfmoni}',

'R{emip_rwca_jks_GD}',

'R{emip_yjyl_administrator_gd}',

'R{emip_zdsjbz_tx_administrator_gd}',

'R{orgadmin}',

'R{regeditadmin}',

'Z{232}',

'Z{231}',

'Z{117659}',

'Z{104434{TSCL/T1_PROCESSOR/PROV_CQWJJL1320636857215__DPT}}',

'Z{104434{TSCL/T1_PROCESSOR/PROV_GZL1320636813164__DPT}}',

'Z{104434{TSCL/T1_PROCESSOR/PROV_JK-TYZY1305701985343__DPT}}',

'Z{104434{TSCL/T1_PROCESSOR/PROV_PTTSL1320636840921__DPT}}',

'Z{104434{TSCL/T1_PROCESSOR/PROV_SJTSL1320636704846__DPT}}',

'Z{104434{TSCL/T1_PROCESSOR/PROV_XZDSJL1320636741727__DPT}}',

'Z{104434{TSCL/T1_PROCESSOR/PROV_ZDSJYJL1320636760774__DPT}}',

'Z{104434{TSCL_PARTICIPANT/APPLY_FINISH}}',

'Z{104434{emip_dutymgr_cfgmgr}}',

'Z{104434{emip_gdgl_monitor}}',

'Z{104434{emip_gjrw_jks_GD}}',

'Z{104434{emip_gzcl_fatconfig}}',

'Z{104434{emip_gzcl_ltconfig}}',

'Z{104434{emip_gzcl_netconfig}}',

'Z{104434{emip_gzcl_wfrole}}',

'Z{104434{emip_jjgz_monitor}}',

'Z{104434{emip_jsj_wfmoni}}',

'Z{104434{emip_rwca_jks_GD}}',

'Z{104434{emip_yjyl_administrator_gd}}',

'Z{104434{emip_zdsjbz_tx_administrator_gd}}',

'Z{104434{orgadmin}}',

'Z{104434{regeditadmin}}',

'Z{99999{TSCL/T1_PROCESSOR/PROV_CQWJJL1320636857215__DPT}}',

'Z{99999{TSCL/T1_PROCESSOR/PROV_GZL1320636813164__DPT}}',

'Z{99999{TSCL/T1_PROCESSOR/PROV_JK-TYZY1305701985343__DPT}}',

'Z{99999{TSCL/T1_PROCESSOR/PROV_PTTSL1320636840921__DPT}}',

'Z{99999{TSCL/T1_PROCESSOR/PROV_SJTSL1320636704846__DPT}}',

'Z{99999{TSCL/T1_PROCESSOR/PROV_XZDSJL1320636741727__DPT}}',

'Z{99999{TSCL/T1_PROCESSOR/PROV_ZDSJYJL1320636760774__DPT}}',

'Z{99999{TSCL_PARTICIPANT/APPLY_FINISH}}',

'Z{99999{emip_dutymgr_cfgmgr}}',

'Z{99999{emip_gdgl_monitor}}',

'Z{99999{emip_gjrw_jks_GD}}',

'Z{99999{emip_gzcl_fatconfig}}',

'Z{99999{emip_gzcl_ltconfig}}',

'Z{99999{emip_gzcl_netconfig}}',

'Z{99999{emip_gzcl_wfrole}}',

'Z{99999{emip_jjgz_monitor}}',

'Z{99999{emip_jsj_wfmoni}}',

'Z{99999{emip_rwca_jks_GD}}',

'Z{99999{emip_yjyl_administrator_gd}}',

'Z{99999{emip_zdsjbz_tx_administrator_gd}}',

'Z{99999{orgadmin}}',

'Z{99999{regeditadmin}}',

'Z{104852{TSCL/T1_PROCESSOR/PROV_CQWJJL1320636857215__DPT}}',

'Z{104852{TSCL/T1_PROCESSOR/PROV_GZL1320636813164__DPT}}',

'Z{104852{TSCL/T1_PROCESSOR/PROV_JK-TYZY1305701985343__DPT}}',

'Z{104852{TSCL/T1_PROCESSOR/PROV_PTTSL1320636840921__DPT}}',

'Z{104852{TSCL/T1_PROCESSOR/PROV_SJTSL1320636704846__DPT}}',

'Z{104852{TSCL/T1_PROCESSOR/PROV_XZDSJL1320636741727__DPT}}',

'Z{104852{TSCL/T1_PROCESSOR/PROV_ZDSJYJL1320636760774__DPT}}',

'Z{104852{TSCL_PARTICIPANT/APPLY_FINISH}}',

'Z{104852{emip_dutymgr_cfgmgr}}',

'Z{104852{emip_gdgl_monitor}}',

'Z{104852{emip_gjrw_jks_GD}}',

'Z{104852{emip_gzcl_fatconfig}}',

'Z{104852{emip_gzcl_ltconfig}}',

'Z{104852{emip_gzcl_netconfig}}',

'Z{104852{emip_gzcl_wfrole}}',

'Z{104852{emip_jjgz_monitor}}',

'Z{104852{emip_jsj_wfmoni}}',

'Z{104852{emip_rwca_jks_GD}}',

'Z{104852{emip_yjyl_administrator_gd}}',

'Z{104852{emip_zdsjbz_tx_administrator_gd}}',

'Z{104852{orgadmin}}',

'Z{104852{regeditadmin}}')) AND

(WFWORKITEM.BIZSTATE = 0))))) OR

WFWORKITEM.WORKITEMID = WFWIPARTICIPANT.WORKITEMID AND

((CURRENTSTATE = 10 AND WFWIPARTICIPANT.PARTIINTYPE = 'EXE' AND

((WFWIPARTICIPANT.EXTEND1 = :1 AND

(WFWORKITEM.BIZSTATE = 0 OR WFWORKITEM.BIZSTATE = 7 OR

WFWORKITEM.BIZSTATE = 5 OR WFWORKITEM.BIZSTATE = 4))))))
ORDER BY T1.LASTEDITTIME DESC)
WHERE ROWNUM SYSDATE@!-365))
21 - access("COMM"."PROCESSINSTID"="WFWORKITEM"."PROCESSINSTID")
filter("COMM"."PROCESSINSTID" IS NOT NULL)
23 - filter("TT"."CREATETIME">SYSDATE@!-365)
24 - access("COMM"."TICKETID"="TT"."TICKETID")
26 - access("TT"."FAULTINFOID"="FI"."ID")
29 - access("COMM"."PROCESSINSTID"="D"."PROCESSINSTID")
31 - filter(TO_NUMBER("FA"."FAULTDEALRESULT")>0)
32 - access("COMM"."PROCESSINSTID"="FA"."PROCESSINSTID")
39 - filter(("WFWIPARTICIPANT"."PARTIINTYPE"='EXE' AND "WFWIPARTICIPANT"."PARTIINTYPE"='GET' AND

(LNNVL("WFWIPARTICIPANT"."EXTEND1"=:1) OR LNNVL("WFWIPARTICIPANT"."PARTIINTYPE"='EXE'))))
40 - access("WFWIPARTICIPANT"."EXTEND1"=:1)
filter(("WFWIPARTICIPANT"."EXTEND1"='O{104434}' OR "WFWIPARTICIPANT"."EXTEND1"='O{104852}' OR

"WFWIPARTICIPANT"."EXTEND1"='P{zhangdian}' OR "WFWIPARTICIPANT"."EXTEND1"='R{TSCL/T1_PROCESSOR/PROV_CQWJJL1320636857215__DPT}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{TSCL/T1_PROCESSOR/PROV_GZL1320636813164__DPT}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{TSCL/T1_PROCESSOR/PROV_JK-TYZY1305701985343__DPT}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{TSCL/T1_PROCESSOR/PROV_PTTSL1320636840921__DPT}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{TSCL/T1_PROCESSOR/PROV_SJTSL1320636704846__DPT}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{TSCL/T1_PROCESSOR/PROV_XZDSJL1320636741727__DPT}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{TSCL/T1_PROCESSOR/PROV_ZDSJYJL1320636760774__DPT}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{TSCL_PARTICIPANT/APPLY_FINISH}' OR "WFWIPARTICIPANT"."EXTEND1"='R{emip_dutymgr_cfgmgr}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{emip_gdgl_monitor}' OR "WFWIPARTICIPANT"."EXTEND1"='R{emip_gjrw_jks_GD}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{emip_gzcl_fatconfig}' OR "WFWIPARTICIPANT"."EXTEND1"='R{emip_gzcl_ltconfig}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{emip_gzcl_netconfig}' OR "WFWIPARTICIPANT"."EXTEND1"='R{emip_gzcl_wfrole}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{emip_jjgz_monitor}' OR "WFWIPARTICIPANT"."EXTEND1"='R{emip_jsj_wfmoni}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{emip_rwca_jks_GD}' OR "WFWIPARTICIPANT"."EXTEND1"='R{emip_yjyl_administrator_gd}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{emip_zdsjbz_tx_administrator_gd}' OR "WFWIPARTICIPANT"."EXTEND1"='R{orgadmin}' OR

"WFWIPARTICIPANT"."EXTEND1"='R{regeditadmin}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104434{TSCL/T1_PROCESSOR/PROV_CQWJJL1320636857215__DP

T}}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104434{TSCL/T1_PROCESSOR/PROV_GZL1320636813164__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{TSCL/T1_PROCESSOR/PROV_JK-TYZY1305701985343__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{TSCL/T1_PROCESSOR/PROV_PTTSL1320636840921__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{TSCL/T1_PROCESSOR/PROV_SJTSL1320636704846__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{TSCL/T1_PROCESSOR/PROV_XZDSJL1320636741727__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{TSCL/T1_PROCESSOR/PROV_ZDSJYJL1320636760774__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{TSCL_PARTICIPANT/APPLY_FINISH}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_dutymgr_cfgmgr}}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_gdgl_monitor}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_gjrw_jks_GD}}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_gzcl_fatconfig}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_gzcl_ltconfig}}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_gzcl_netconfig}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_gzcl_wfrole}}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_jjgz_monitor}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_jsj_wfmoni}}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_rwca_jks_GD}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_yjyl_administrator_gd}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{emip_zdsjbz_tx_administrator_gd}}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104434{orgadmin}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104434{regeditadmin}}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104852{TSCL/T1_PROCESSOR/PROV_CQWJJL132063685

7215__DPT}}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104852{TSCL/T1_PROCESSOR/PROV_GZL1320636813164__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104852{TSCL/T1_PROCESSOR/PROV_JK-TYZY1305701985343__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104852{TSCL/T1_PROCESSOR/PROV_PTTSL1320636840921__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104852{TSCL/T1_PROCESSOR/PROV_SJTSL1320636704846__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104852{TSCL/T1_PROCESSOR/PROV_XZDSJL1320636741727__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104852{TSCL/T1_PROCESSOR/PROV_ZDSJYJL1320636760774__DPT}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104852{TSCL_PARTICIPANT/APPLY_FINISH}}' OR

"WFWIPARTICIPANT"."EXTEND1"='Z{104852{emip_dutymgr_cfgmgr}}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104852{emip_gdgl_monitor}}' OR

"WFWIPARTICIP)
41 - filter(("CURRENTSTATE"=10 AND (("WFWORKITEM"."CURRENTSTATE"=4 AND "WFWORKITEM"."BIZSTATE"=0) OR ("CURRENTSTATE"=10 AND

INTERNAL_FUNCTION("WFWORKITEM"."BIZSTATE"))) AND INTERNAL_FUNCTION("WFWORKITEM"."BIZSTATE")))
42 - access("WFWORKITEM"."WORKITEMID"="WFWIPARTICIPANT"."WORKITEMID")
43 - filter(("COMM"."TICKETID" IS NOT NULL AND "COMM"."PROCESSID"=23 AND ("COMM"."TICKETSTATUSCONFIG" IS NULL OR

"COMM"."TICKETSTATUSCONFIG"=1) AND INTERNAL_FUNCTION("COMM"."STATUS") AND "COMM"."CREATETIME">SYSDATE@!-365))
44 - access("COMM"."PROCESSINSTID"="WFWORKITEM"."PROCESSINSTID")
filter("COMM"."PROCESSINSTID" IS NOT NULL)
46 - filter("TT"."CREATETIME">SYSDATE@!-365)
47 - access("COMM"."TICKETID"="TT"."TICKETID")
49 - access("TT"."FAULTINFOID"="FI"."ID")
52 - access("COMM"."PROCESSINSTID"="D"."PROCESSINSTID")
54 - filter(TO_NUMBER("FA"."FAULTDEALRESULT")>0)
55 - access("COMM"."PROCESSINSTID"="FA"."PROCESSINSTID")
60 - access("COMM"."PROCESSINSTID"="WFWORKITEM"."PROCESSINSTID")
63 - filter(("WFWIPARTICIPANT"."PARTIINTYPE"='GET' AND "WFWIPARTICIPANT"."PARTIINTYPE"='GET' AND

INTERNAL_FUNCTION("WFWIPARTICIPANT"."EXTEND1") AND INTERNAL_FUNCTION("WFWIPARTICIPANT"."EXTEND1") AND

(LNNVL("WFWIPARTICIPANT"."EXTEND1"=:1) OR LNNVL("WFWIPARTICIPANT"."PARTIINTYPE"='EXE'))))
64 - access("WFWORKITEM"."WORKITEMID"="WFWIPARTICIPANT"."WORKITEMID")
65 - filter(("WFWORKITEM"."CURRENTSTATE"=4 AND "WFWORKITEM"."BIZSTATE"=0 AND (("WFWORKITEM"."CURRENTSTATE"=4 AND

"WFWORKITEM"."BIZSTATE"=0) OR ("CURRENTSTATE"=10 AND INTERNAL_FUNCTION("WFWORKITEM"."BIZSTATE"))) AND (LNNVL("CURRENTSTATE"=10) OR

LNNVL("WFWIPARTICIPANT"."PARTIINTYPE"='EXE') OR LNNVL("WFWIPARTICIPANT"."EXTEND1"=:1) OR (LNNVL("WFWORKITEM"."BIZSTATE"=0) AND

LNNVL("WFWORKITEM"."BIZSTATE"=4) AND LNNVL("WFWORKITEM"."BIZSTATE"=5) AND LNNVL("WFWORKITEM"."BIZSTATE"=7)))))
66 - filter(("COMM"."PROCESSINSTID" IS NOT NULL AND "COMM"."TICKETID" IS NOT NULL AND "COMM"."PROCESSID"=23 AND

("COMM"."TICKETSTATUSCONFIG" IS NULL OR "COMM"."TICKETSTATUSCONFIG"=1) AND INTERNAL_FUNCTION("COMM"."STATUS") AND

"COMM"."CREATETIME">SYSDATE@!-365))
68 - filter(TO_NUMBER("FA"."FAULTDEALRESULT")>0)
69 - access("COMM"."PROCESSINSTID"="FA"."PROCESSINSTID")
72 - access("COMM"."PROCESSINSTID"="D"."PROCESSINSTID")
74 - filter("TT"."CREATETIME">SYSDATE@!-365)
75 - access("COMM"."TICKETID"="TT"."TICKETID")
77 - access("TT"."FAULTINFOID"="FI"."ID")

202 rows selected.

千问 · 2014-3-4 06:00:13

期待大师回复。

千问 · 2014-3-4 06:00:13

SELECT *
FROM (SELECT /*+ USE_HASH(T1) */
DISTINCT T1.STATUS,

T1.CREATETIME,

T1.TICKETTITLE,

T1.REGION,

T1.ISURGENTFAULT,

T1.DEALLIMITEDTIME,

T1.BIZ_LASTOPERATORNAME,

T1.CREATEDEPTFULLNAME,

T1.CREATEPERSONNAME,

T1.ALARMTYPE,

T1.ISDISPATCHPROVICE,

T1.ALARMOBJ,

T1.ALARMLOGICSUBCLASS,

T1.MESHWORKTYPE,

T1.FLOWTYPE,

T1.ALARMLEVEL,

T1.FAULTDEALCITY,

T1.TIMEOUT,

T1.ISEFFACTBIZ,

T1.ISGROUPBUSINESS,

T1.ISPUBLISHINFO,

T1.TICKETID,

T1.ALARMRELATAG,

T1.ALARMLOGICCLASS,

T1.EQUPMANUFACTURER,

T1.AREA,

T1.ISDELAYVALID,

T1.FAULTOCCURCITY,

T1.STARLEVEL,

T1.QUERYALARMELIMINATETIME,

T1.ID,

T1.BILLMAINID,

T1.PROCESSID,

T1.PROCESSINSTID,

T1.BIZ_OVERTIME,

T1.BIZ_NOTICETIME,

T1.BIZ_WORKITEMID,

T1.ALARMLEVELNAME,

T1.STARLEVELNAME,

T1.FAULTREASON,

T1.ALARMELIMINATETIME,

T1.LIMITEDTIME,

T1.STATUSNAME,

T1.PARTICIPANTORGNAME,

T1.CREATEDPT,

T1.ISDELAYEFFECT,

T1.ISGROUPBUSINESSNAME,

T1.LASTEDITTIME,

WFWORKITEM.PROCESSINSTNAME,

WFWORKITEM.ACTIVITYINSTID,

WFWORKITEM.ACTIVITYINSTNAME,

WFWORKITEM.PROCESSDEFID,

WFWORKITEM.PROCESSDEFNAME,

WFWORKITEM.PROCESSCHNAME,

WFWORKITEM.ACTIVITYDEFID,

WFWORKITEM.CURRENTSTATE,

WFWORKITEM.WORKITEMID,

WFWORKITEM.STARTTIME,

WFWORKITEM.FINALTIME,

WFWORKITEM.ISTIMEOUT,

WFWORKITEM.EXTEND2,

WFWORKITEM.PARTICIPANT

FROM V_GZCL_TICKET4DB T1, WFWORKITEM, WFWIPARTICIPANT
WHERE WFWORKITEM.PROCESSINSTID = T1.PROCESSINSTID
AND WFWORKITEM.WORKITEMID = WFWIPARTICIPANT.WORKITEMID
AND T1.PROCESSID = 23
AND (

(

WFWORKITEM.CURRENTSTATE = 4 AND

WFWORKITEM.BIZSTATE = 0AND

WFWIPARTICIPANT.PARTIINTYPE = 'GET' AND

WFWIPARTICIPANT.EXTEND1 IN ('P{zhangdian}','Z{104852{regeditadmin}}')

)

OR

(

WFWORKITEM.CURRENTSTATE = 10 AND

(WFWORKITEM.BIZSTATE = 0 OR WFWORKITEM.BIZSTATE = 7 ORWFWORKITEM.BIZSTATE = 5 OR WFWORKITEM.BIZSTATE = 4) AND

WFWIPARTICIPANT.PARTIINTYPE = 'EXE' AND

WFWIPARTICIPANT.EXTEND1 = :1

)

)
ORDER BY T1.LASTEDITTIME DESC)
WHERE ROWNUM SYSDATE@!-365)
21 - access("WFWORKITEM"."PROCESSINSTID"="COMM"."PROCESSINSTID")
filter("COMM"."PROCESSINSTID" IS NOT NULL)
23 - filter("TT"."CREATETIME">SYSDATE@!-365)
24 - access("COMM"."TICKETID"="TT"."TICKETID")
26 - access("TT"."FAULTINFOID"="FI"."ID")
29 - access("COMM"."PROCESSINSTID"="D"."PROCESSINSTID"(+))
31 - filter(TO_NUMBER("FA"."FAULTDEALRESULT"(+))>0)
32 - access("COMM"."PROCESSINSTID"="FA"."PROCESSINSTID"(+))
39 - filter("WFWIPARTICIPANT"."PARTIINTYPE"='EXE' AND "WFWIPARTICIPANT"."PARTIINTYPE"='GET' AND

(LNNVL("WFWIPARTICIPANT"."EXTEND1"='Z{104895{emip_jfzy_proassign}}') OR LNNVL("WFWIPARTICIPANT"."PARTIINTYPE"='EXE')))
40 - access("WFWIPARTICIPANT"."EXTEND1"='Z{104895{emip_jfzy_proassign}}')
filter("WFWIPARTICIPANT"."EXTEND1"='P{zhangdian}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104852{regeditadmin}}')
41 - filter("WFWORKITEM"."CURRENTSTATE"=10 AND ("WFWORKITEM"."CURRENTSTATE"=4 AND "WFWORKITEM"."BIZSTATE"=0 OR

"WFWORKITEM"."CURRENTSTATE"=10 AND ("WFWORKITEM"."BIZSTATE"=0 OR "WFWORKITEM"."BIZSTATE"=4 OR "WFWORKITEM"."BIZSTATE"=5 OR

"WFWORKITEM"."BIZSTATE"=7)) AND ("WFWORKITEM"."BIZSTATE"=0 OR "WFWORKITEM"."BIZSTATE"=4 OR "WFWORKITEM"."BIZSTATE"=5 OR

"WFWORKITEM"."BIZSTATE"=7))
42 - access("WFWORKITEM"."WORKITEMID"="WFWIPARTICIPANT"."WORKITEMID")
43 - filter("COMM"."PROCESSID"=23 AND "COMM"."TICKETID" IS NOT NULL AND ("COMM"."TICKETSTATUSCONFIG" IS NULL OR

"COMM"."TICKETSTATUSCONFIG"=1) AND ("COMM"."STATUS"=10 OR "COMM"."STATUS"=50 OR "COMM"."STATUS"=501 OR "COMM"."STATUS"=502 OR

"COMM"."STATUS"=505 OR "COMM"."STATUS"=506 OR "COMM"."STATUS"=507 OR "COMM"."STATUS"=509 OR "COMM"."STATUS"=510 OR

"COMM"."STATUS"=521 OR "COMM"."STATUS"=522) AND "COMM"."CREATETIME">SYSDATE@!-365)
44 - access("WFWORKITEM"."PROCESSINSTID"="COMM"."PROCESSINSTID")
filter("COMM"."PROCESSINSTID" IS NOT NULL)
46 - filter("TT"."CREATETIME">SYSDATE@!-365)
47 - access("COMM"."TICKETID"="TT"."TICKETID")
49 - access("TT"."FAULTINFOID"="FI"."ID")
52 - access("COMM"."PROCESSINSTID"="D"."PROCESSINSTID"(+))
54 - filter(TO_NUMBER("FA"."FAULTDEALRESULT"(+))>0)
55 - access("COMM"."PROCESSINSTID"="FA"."PROCESSINSTID"(+))
63 - filter("WFWIPARTICIPANT"."PARTIINTYPE"='GET' AND "WFWIPARTICIPANT"."PARTIINTYPE"='GET' AND

(LNNVL("WFWIPARTICIPANT"."EXTEND1"='Z{104895{emip_jfzy_proassign}}') OR LNNVL("WFWIPARTICIPANT"."PARTIINTYPE"='EXE')))
64 - access("WFWIPARTICIPANT"."EXTEND1"='P{zhangdian}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104852{regeditadmin}}')
filter("WFWIPARTICIPANT"."EXTEND1"='P{zhangdian}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104852{regeditadmin}}')
65 - filter("WFWORKITEM"."CURRENTSTATE"=4 AND "WFWORKITEM"."BIZSTATE"=0 AND ("WFWORKITEM"."CURRENTSTATE"=4 AND

"WFWORKITEM"."BIZSTATE"=0 OR "WFWORKITEM"."CURRENTSTATE"=10 AND ("WFWORKITEM"."BIZSTATE"=0 OR "WFWORKITEM"."BIZSTATE"=4 OR

"WFWORKITEM"."BIZSTATE"=5 OR "WFWORKITEM"."BIZSTATE"=7)) AND (LNNVL("WFWORKITEM"."CURRENTSTATE"=10) OR

LNNVL("WFWORKITEM"."BIZSTATE"=0) AND LNNVL("WFWORKITEM"."BIZSTATE"=4) AND LNNVL("WFWORKITEM"."BIZSTATE"=5) AND

LNNVL("WFWORKITEM"."BIZSTATE"=7) OR LNNVL("WFWIPARTICIPANT"."PARTIINTYPE"='EXE') OR

LNNVL("WFWIPARTICIPANT"."EXTEND1"='Z{104895{emip_jfzy_proassign}}')))
66 - access("WFWORKITEM"."WORKITEMID"="WFWIPARTICIPANT"."WORKITEMID")
67 - filter("COMM"."PROCESSID"=23 AND "COMM"."TICKETID" IS NOT NULL AND ("COMM"."TICKETSTATUSCONFIG" IS NULL OR

"COMM"."TICKETSTATUSCONFIG"=1) AND ("COMM"."STATUS"=10 OR "COMM"."STATUS"=50 OR "COMM"."STATUS"=501 OR "COMM"."STATUS"=502 OR

"COMM"."STATUS"=505 OR "COMM"."STATUS"=506 OR "COMM"."STATUS"=507 OR "COMM"."STATUS"=509 OR "COMM"."STATUS"=510 OR

"COMM"."STATUS"=521 OR "COMM"."STATUS"=522) AND "COMM"."CREATETIME">SYSDATE@!-365)
68 - access("WFWORKITEM"."PROCESSINSTID"="COMM"."PROCESSINSTID")
filter("COMM"."PROCESSINSTID" IS NOT NULL)
70 - filter("TT"."CREATETIME">SYSDATE@!-365)
71 - access("COMM"."TICKETID"="TT"."TICKETID")
73 - access("TT"."FAULTINFOID"="FI"."ID")
76 - access("COMM"."PROCESSINSTID"="D"."PROCESSINSTID"(+))
78 - filter(TO_NUMBER("FA"."FAULTDEALRESULT"(+))>0)
79 - access("COMM"."PROCESSINSTID"="FA"."PROCESSINSTID"(+))
160 rows selected.

千问 · 2014-3-4 06:00:13

tianyadeyu 发表于 2014-9-18 12:00
看清结构，楼主看看新的执行计划，把IN条件不全，咋看看呢执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3898468279
---------------------------------------------------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 | 10224 | 528 (1)| 00:00:03 | | |
| 1 |TABLE ACCESS BY INDEX ROWID
| TBL_GZCL_FAULTREASON
| 1 |14 | 1 (0)| 00:00:01 | | |
|*2 | INDEX UNIQUE SCAN
| PK_GZCL_FAULTREASON | 1 | | 0 (0)| 00:00:01 | | |
| 3 |TABLE ACCESS BY INDEX ROWID
| TBL_GZCL_FAULTREASON
| 1 |18 | 1 (0)| 00:00:01 | | |
|*4 | INDEX UNIQUE SCAN
| PK_GZCL_FAULTREASON | 1 | | 0 (0)| 00:00:01 | | |
|*5 |COUNT STOPKEY
|
| | |
|
| | |
| 6 | VIEW
|
| 1 | 10224 | 528 (1)| 00:00:03 | | |
|*7 |SORT ORDER BY STOPKEY
|
| 1 | 668 | 528 (1)| 00:00:03 | | |
| 8 | HASH UNIQUE
|
| 1 | 668 | 527 (1)| 00:00:03 | | |
| 9 |CONCATENATION
|
| | |
|
| | |
|10 | NESTED LOOPS OUTER
|
| 1 | 668 | 226 (0)| 00:00:02 | | |
|11 |NESTED LOOPS OUTER
|
| 1 | 639 | 223 (0)| 00:00:02 | | |
|12 | NESTED LOOPS
|
| 1 | 625 | 220 (0)| 00:00:02 | | |
|13 |
NESTED LOOPS
|
| 1 | 532 | 218 (0)| 00:00:02 | | |
|14 | NESTED LOOPS
|
| 1 | 332 |87 (0)| 00:00:01 | | |
|15 |
NESTED LOOPS
|
| 1 | 166 |84 (0)| 00:00:01 | | |
|* 16 |
TABLE ACCESS BY INDEX ROWID | WFWIPARTICIPANT
|18 | 486 |30 (0)| 00:00:01 | | |
|* 17 |
INDEX RANGE SCAN
| WF_IDX_PART_EXTEND1 |48 | | 3 (0)| 00:00:01 | | |
|* 18 |
TABLE ACCESS BY INDEX ROWID | WFWORKITEM
| 1 | 139 | 3 (0)| 00:00:01 | | |
|* 19 |
INDEX UNIQUE SCAN
| PK_WFWORKITEM_N
| 1 | | 2 (0)| 00:00:01 | | |
|* 20 |
TABLE ACCESS BY INDEX ROWID| TBL_COMM_COMMONTICKET | 1 | 166 | 3 (0)| 00:00:01 | | |
|* 21 |
INDEX RANGE SCAN
| INX_COMM_TICKET_PROCESSINID2| 1 | | 2 (0)| 00:00:01 | | |
|22 | PARTITION RANGE ITERATOR
|
| 1 | 200 | 131 (0)| 00:00:01 | KEY | 145 |
|* 23 |
TABLE ACCESS BY LOCAL INDEX ROWID| TBL_GZCL_TICKET
| 1 | 200 | 131 (0)| 00:00:01 | KEY | 145 |
|* 24 |
INDEX RANGE SCAN
| INX_TICKET_TICKETID | 1 | | 130 (0)| 00:00:01 | KEY | 145 |
|25 |
TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_GZCL_FAULTINFO
| 1 |93 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 26 | INDEX UNIQUE SCAN
| PK_FAULTINFO_ID
| 1 | | 1 (0)| 00:00:01 | | |
|27 | PARTITION HASH ITERATOR
|
| 1 |14 | 3 (0)| 00:00:01 | KEY | KEY |
|28 |
TABLE ACCESS BY LOCAL INDEX ROWID| TBL_GZCL_DUTYINFO
| 1 |14 | 3 (0)| 00:00:01 | KEY | KEY |
|* 29 | INDEX RANGE SCAN
| INDEX_GZCL_DUTYINFO_PCSINSTID | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|30 |PARTITION HASH ITERATOR
|
| 1 |29 | 3 (0)| 00:00:01 | KEY | KEY |
|* 31 | TABLE ACCESS BY LOCAL INDEX ROWID | TBL_GZCL_FAULTDEALRECORDS | 1 |29 | 3 (0)| 00:00:01 | KEY | KEY |
|* 32 |
INDEX RANGE SCAN
| INDEX_GZCL_FTDLRCDS_PCSINSTID | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|33 | NESTED LOOPS OUTER
|
| 1 | 668 | 149 (0)| 00:00:01 | | |
|34 |NESTED LOOPS OUTER
|
| 1 | 639 | 146 (0)| 00:00:01 | | |
|35 | NESTED LOOPS
|
| 1 | 625 | 143 (0)| 00:00:01 | | |
|36 |
NESTED LOOPS
|
| 1 | 532 | 141 (0)| 00:00:01 | | |
|37 | NESTED LOOPS
|
| 1 | 332 |10 (0)| 00:00:01 | | |
|38 |
NESTED LOOPS
|
| 1 | 166 | 7 (0)| 00:00:01 | | |
|* 39 |
TABLE ACCESS BY INDEX ROWID | WFWIPARTICIPANT
| 1 |27 | 4 (0)| 00:00:01 | | |
|* 40 |
INDEX RANGE SCAN
| WF_IDX_PART_EXTEND1 | 1 | | 3 (0)| 00:00:01 | | |
|* 41 |
TABLE ACCESS BY INDEX ROWID | WFWORKITEM
| 1 | 139 | 3 (0)| 00:00:01 | | |
|* 42 |
INDEX UNIQUE SCAN
| PK_WFWORKITEM_N
| 1 | | 2 (0)| 00:00:01 | | |
|* 43 |
TABLE ACCESS BY INDEX ROWID| TBL_COMM_COMMONTICKET | 1 | 166 | 3 (0)| 00:00:01 | | |
|* 44 |
INDEX RANGE SCAN
| INX_COMM_TICKET_PROCESSINID2| 1 | | 2 (0)| 00:00:01 | | |
|45 | PARTITION RANGE ITERATOR
|
| 1 | 200 | 131 (0)| 00:00:01 | KEY | 145 |
|* 46 |
TABLE ACCESS BY LOCAL INDEX ROWID| TBL_GZCL_TICKET
| 1 | 200 | 131 (0)| 00:00:01 | KEY | 145 |
|* 47 |
INDEX RANGE SCAN
| INX_TICKET_TICKETID | 1 | | 130 (0)| 00:00:01 | KEY | 145 |
|48 |
TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_GZCL_FAULTINFO
| 1 |93 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 49 | INDEX UNIQUE SCAN
| PK_FAULTINFO_ID
| 1 | | 1 (0)| 00:00:01 | | |
|50 | PARTITION HASH ITERATOR
|
| 1 |14 | 3 (0)| 00:00:01 | KEY | KEY |
|51 |
TABLE ACCESS BY LOCAL INDEX ROWID| TBL_GZCL_DUTYINFO
| 1 |14 | 3 (0)| 00:00:01 | KEY | KEY |
|* 52 | INDEX RANGE SCAN
| INDEX_GZCL_DUTYINFO_PCSINSTID | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|53 |PARTITION HASH ITERATOR
|
| 1 |29 | 3 (0)| 00:00:01 | KEY | KEY |
|* 54 | TABLE ACCESS BY LOCAL INDEX ROWID | TBL_GZCL_FAULTDEALRECORDS | 1 |29 | 3 (0)| 00:00:01 | KEY | KEY |
|* 55 |
INDEX RANGE SCAN
| INDEX_GZCL_FTDLRCDS_PCSINSTID | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|56 | NESTED LOOPS OUTER
|
| 1 | 668 | 150 (0)| 00:00:01 | | |
|57 |NESTED LOOPS OUTER
|
| 1 | 639 | 147 (0)| 00:00:01 | | |
|58 | NESTED LOOPS
|
| 1 | 625 | 144 (0)| 00:00:01 | | |
|59 |
NESTED LOOPS
|
| 1 | 532 | 142 (0)| 00:00:01 | | |
|60 | NESTED LOOPS
|
| 1 | 332 |11 (0)| 00:00:01 | | |
|61 |
NESTED LOOPS
|
| 1 | 166 | 8 (0)| 00:00:01 | | |
|62 |
INLIST ITERATOR
|
| | |
|
| | |
|* 63 |
TABLE ACCESS BY INDEX ROWID| WFWIPARTICIPANT
| 1 |27 | 5 (0)| 00:00:01 | | |
|* 64 |
INDEX RANGE SCAN
| WF_IDX_PART_EXTEND1 | 1 | | 4 (0)| 00:00:01 | | |
|* 65 |
TABLE ACCESS BY INDEX ROWID | WFWORKITEM
| 1 | 139 | 3 (0)| 00:00:01 | | |
|* 66 |
INDEX UNIQUE SCAN
| PK_WFWORKITEM_N
| 1 | | 2 (0)| 00:00:01 | | |
|* 67 |
TABLE ACCESS BY INDEX ROWID| TBL_COMM_COMMONTICKET | 1 | 166 | 3 (0)| 00:00:01 | | |
|* 68 |
INDEX RANGE SCAN
| INX_COMM_TICKET_PROCESSINID2| 1 | | 2 (0)| 00:00:01 | | |
|69 | PARTITION RANGE ITERATOR
|
| 1 | 200 | 131 (0)| 00:00:01 | KEY | 145 |
|* 70 |
TABLE ACCESS BY LOCAL INDEX ROWID| TBL_GZCL_TICKET
| 1 | 200 | 131 (0)| 00:00:01 | KEY | 145 |
|* 71 |
INDEX RANGE SCAN
| INX_TICKET_TICKETID | 1 | | 130 (0)| 00:00:01 | KEY | 145 |
|72 |
TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_GZCL_FAULTINFO
| 1 |93 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 73 | INDEX UNIQUE SCAN
| PK_FAULTINFO_ID
| 1 | | 1 (0)| 00:00:01 | | |
|74 | PARTITION HASH ITERATOR
|
| 1 |14 | 3 (0)| 00:00:01 | KEY | KEY |
|75 |
TABLE ACCESS BY LOCAL INDEX ROWID| TBL_GZCL_DUTYINFO
| 1 |14 | 3 (0)| 00:00:01 | KEY | KEY |
|* 76 | INDEX RANGE SCAN
| INDEX_GZCL_DUTYINFO_PCSINSTID | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|77 |PARTITION HASH ITERATOR
|
| 1 |29 | 3 (0)| 00:00:01 | KEY | KEY |
|* 78 | TABLE ACCESS BY LOCAL INDEX ROWID | TBL_GZCL_FAULTDEALRECORDS | 1 |29 | 3 (0)| 00:00:01 | KEY | KEY |
|* 79 |
INDEX RANGE SCAN
| INDEX_GZCL_FTDLRCDS_PCSINSTID | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PR"."ID"=:B1)
4 - access("T"."ID"=TO_NUMBER(:B1))
5 - filter(ROWNUMSYSDATE@!-365)
21 - access("WFWORKITEM"."PROCESSINSTID"="COMM"."PROCESSINSTID")
filter("COMM"."PROCESSINSTID" IS NOT NULL)
23 - filter("TT"."CREATETIME">SYSDATE@!-365)
24 - access("COMM"."TICKETID"="TT"."TICKETID")
26 - access("TT"."FAULTINFOID"="FI"."ID")
29 - access("COMM"."PROCESSINSTID"="D"."PROCESSINSTID"(+))
31 - filter(TO_NUMBER("FA"."FAULTDEALRESULT"(+))>0)
32 - access("COMM"."PROCESSINSTID"="FA"."PROCESSINSTID"(+))
39 - filter("WFWIPARTICIPANT"."PARTIINTYPE"='EXE' AND "WFWIPARTICIPANT"."PARTIINTYPE"='GET' AND

(LNNVL("WFWIPARTICIPANT"."EXTEND1"='Z{104895{emip_jfzy_proassign}}') OR LNNVL("WFWIPARTICIPANT"."PARTIINTYPE"='EXE')))
40 - access("WFWIPARTICIPANT"."EXTEND1"='Z{104895{emip_jfzy_proassign}}')
filter("WFWIPARTICIPANT"."EXTEND1"='P{zhangdian}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104852{regeditadmin}}')
41 - filter("WFWORKITEM"."CURRENTSTATE"=10 AND ("WFWORKITEM"."CURRENTSTATE"=4 AND "WFWORKITEM"."BIZSTATE"=0 OR

"WFWORKITEM"."CURRENTSTATE"=10 AND ("WFWORKITEM"."BIZSTATE"=0 OR "WFWORKITEM"."BIZSTATE"=4 OR "WFWORKITEM"."BIZSTATE"=5 OR

"WFWORKITEM"."BIZSTATE"=7)) AND ("WFWORKITEM"."BIZSTATE"=0 OR "WFWORKITEM"."BIZSTATE"=4 OR "WFWORKITEM"."BIZSTATE"=5 OR

"WFWORKITEM"."BIZSTATE"=7))
42 - access("WFWORKITEM"."WORKITEMID"="WFWIPARTICIPANT"."WORKITEMID")
43 - filter("COMM"."PROCESSID"=23 AND "COMM"."TICKETID" IS NOT NULL AND ("COMM"."TICKETSTATUSCONFIG" IS NULL OR

"COMM"."TICKETSTATUSCONFIG"=1) AND ("COMM"."STATUS"=10 OR "COMM"."STATUS"=50 OR "COMM"."STATUS"=501 OR "COMM"."STATUS"=502 OR

"COMM"."STATUS"=505 OR "COMM"."STATUS"=506 OR "COMM"."STATUS"=507 OR "COMM"."STATUS"=509 OR "COMM"."STATUS"=510 OR

"COMM"."STATUS"=521 OR "COMM"."STATUS"=522) AND "COMM"."CREATETIME">SYSDATE@!-365)
44 - access("WFWORKITEM"."PROCESSINSTID"="COMM"."PROCESSINSTID")
filter("COMM"."PROCESSINSTID" IS NOT NULL)
46 - filter("TT"."CREATETIME">SYSDATE@!-365)
47 - access("COMM"."TICKETID"="TT"."TICKETID")
49 - access("TT"."FAULTINFOID"="FI"."ID")
52 - access("COMM"."PROCESSINSTID"="D"."PROCESSINSTID"(+))
54 - filter(TO_NUMBER("FA"."FAULTDEALRESULT"(+))>0)
55 - access("COMM"."PROCESSINSTID"="FA"."PROCESSINSTID"(+))
63 - filter("WFWIPARTICIPANT"."PARTIINTYPE"='GET' AND "WFWIPARTICIPANT"."PARTIINTYPE"='GET' AND

(LNNVL("WFWIPARTICIPANT"."EXTEND1"='Z{104895{emip_jfzy_proassign}}') OR LNNVL("WFWIPARTICIPANT"."PARTIINTYPE"='EXE')))
64 - access("WFWIPARTICIPANT"."EXTEND1"='P{zhangdian}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104852{regeditadmin}}')
filter("WFWIPARTICIPANT"."EXTEND1"='P{zhangdian}' OR "WFWIPARTICIPANT"."EXTEND1"='Z{104852{regeditadmin}}')
65 - filter("WFWORKITEM"."CURRENTSTATE"=4 AND "WFWORKITEM"."BIZSTATE"=0 AND ("WFWORKITEM"."CURRENTSTATE"=4 AND

"WFWORKITEM"."BIZSTATE"=0 OR "WFWORKITEM"."CURRENTSTATE"=10 AND ("WFWORKITEM"."BIZSTATE"=0 OR "WFWORKITEM"."BIZSTATE"=4 OR

"WFWORKITEM"."BIZSTATE"=5 OR "WFWORKITEM"."BIZSTATE"=7)) AND (LNNVL("WFWORKITEM"."CURRENTSTATE"=10) OR

LNNVL("WFWORKITEM"."BIZSTATE"=0) AND LNNVL("WFWORKITEM"."BIZSTATE"=4) AND LNNVL("WFWORKITEM"."BIZSTATE"=5) AND

LNNVL("WFWORKITEM"."BIZSTATE"=7) OR LNNVL("WFWIPARTICIPANT"."PARTIINTYPE"='EXE') OR

LNNVL("WFWIPARTICIPANT"."EXTEND1"='Z{104895{emip_jfzy_proassign}}')))
66 - access("WFWORKITEM"."WORKITEMID"="WFWIPARTICIPANT"."WORKITEMID")
67 - filter("COMM"."PROCESSID"=23 AND "COMM"."TICKETID" IS NOT NULL AND ("COMM"."TICKETSTATUSCONFIG" IS NULL OR

"COMM"."TICKETSTATUSCONFIG"=1) AND ("COMM"."STATUS"=10 OR "COMM"."STATUS"=50 OR "COMM"."STATUS"=501 OR "COMM"."STATUS"=502 OR

"COMM"."STATUS"=505 OR "COMM"."STATUS"=506 OR "COMM"."STATUS"=507 OR "COMM"."STATUS"=509 OR "COMM"."STATUS"=510 OR

"COMM"."STATUS"=521 OR "COMM"."STATUS"=522) AND "COMM"."CREATETIME">SYSDATE@!-365)
68 - access("WFWORKITEM"."PROCESSINSTID"="COMM"."PROCESSINSTID")
filter("COMM"."PROCESSINSTID" IS NOT NULL)
70 - filter("TT"."CREATETIME">SYSDATE@!-365)
71 - access("COMM"."TICKETID"="TT"."TICKETID")
73 - access("TT"."FAULTINFOID"="FI"."ID")
76 - access("COMM"."PROCESSINSTID"="D"."PROCESSINSTID"(+))
78 - filter(TO_NUMBER("FA"."FAULTDEALRESULT"(+))>0)
79 - access("COMM"."PROCESSINSTID"="FA"."PROCESSINSTID"(+))
160 rows selected.

千问 · 2014-3-4 06:00:13

tianyadeyu 发表于 2014-9-18 12:00
看清结构，楼主看看新的执行计划，把IN条件不全，咋看看呢执行计划
把in的条件去掉后效果好了很多