SELECT
/*+index(h IDX_TK_HDB_CFDATETIME_DATE)*/
T.JP_CJRSMSSTATUS,
T.DDBH,
H.CFDATETIME_DATE,
T.CP_USERID,
H.SFGQ,
H.HBH,
H.CFCITY,
H.DDCITY,
H.ZHGQ_CFDATETIME
FROM TICKET_ALL T, TICKET_HDB H
WHERE T.TKNO = H.TKNO
AND T.ZKFX = '0'
AND T.CP_DATETIME_DATE >= :B3
AND (((H.SFGQ IS NULL OR H.SFGQ = '0') AND H.CFDATETIME_DATE >= :B2 AND
H.CFDATETIME_DATE = :B2 AND
H.ZHGQ_CFDATETIMESELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('g6mz9kzmtnfjz'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_IDg6mz9kzmtnfjz, child number 0
-------------------------------------
SELECT /*+index(h IDX_TK_HDB_CFDATETIME_DATE)*/T.JP_CJRSMSSTATUS,T.DDBH,H.CFDATE
.HBH,H.CFCITY,H.DDCITY,H.ZHGQ_CFDATETIME FROM TICKET_ALL T,TICKET_HDB H WHERE T.
AND T.CP_DATETIME_DATE>=:B3 AND (((H.SFGQ IS NULL OR H.SFGQ='0') AND H.CFDATETIM
H.CFDATETIME_DATE =:B2 AND H.ZHGQ_
F_LB_IFMOBILE(T.JP_CJRSMSSTATUS) = 1 AND T.CP_COMPID IN (SELECT * FROM TABLE(F_G
EXISTS (SELECT 1 FROM KH_KHDD K,KH_KHDD_YXZT Y WHERE K.DDBH=T.DDBH AND K.PNR_ZT=
K.DDZT='8' OR NVL(Y.PNR_TYPE,'0')='0' OR NVL(Y.PNR_TYPE,'0')='4')) AND NOT EXIST
S,SMS_HBYD_MX M WHERE S.ID=M.YDID AND S.HBH=H.HBH AND S.CFRQ=SUBSTR(H.CFDATETIME
M.CFSJ=SUBSTR(H.CFDATETIME,12,5)) GROUP BY T.JP_CJRSMSSTATUS,T.DDBH,H.CFDATETIME
,H.CFCITY,H.DDCITY,H.ZHGQ_CFDATETIME
Plan hash value: 3174844310
--------------------------------------------------------------------------------
| Id| Operation
| Name
| R
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
|
| 1 |HASH GROUP BY
|
|
| 2 | CONCATENATION
|
|
|*3 |FILTER
|
|
| 4 | NESTED LOOPS
|
|
| 5 |MERGE JOIN CARTESIAN
|
|
|*6 | TABLE ACCESS FULL
| TICKET_HDB
|
| 7 | BUFFER SORT
|
|
| 8 |COLLECTION ITERATOR PICKLER FETCH| F_GET_ZGS_XJGS
|
|*9 |TABLE ACCESS BY INDEX ROWID| TICKET_ALL
|
|* 10 | INDEX UNIQUE SCAN
| SYS_C0024631
|
|11 | NESTED LOOPS
|
|
|12 |TABLE ACCESS BY INDEX ROWID| KH_KHDD
|
|* 13 | INDEX UNIQUE SCAN
| SYS_C0021538
|
|* 14 |TABLE ACCESS BY INDEX ROWID| KH_KHDD_YXZT
|
|* 15 | INDEX UNIQUE SCAN
| PK_KH_KHDD_YXZT
|
|* 16 | TABLE ACCESS BY INDEX ROWID | SMS_HBYD_MX
|
|17 |NESTED LOOPS
|
|
|18 | TABLE ACCESS BY INDEX ROWID | SMS_HBYD
|
|* 19 |
INDEX RANGE SCAN
| SMS_HBYD_CFRQ_IDX
|
|* 20 | INDEX RANGE SCAN
| IDX_SMS_HBYD_MX
|
|* 21 |FILTER
|
|
|* 22 | HASH JOIN
|
|
|23 |NESTED LOOPS
|
|
|* 24 | TABLE ACCESS BY INDEX ROWID | TICKET_HDB
|
|* 25 |INDEX RANGE SCAN
| IDX_TK_HDB_CFDATETIME_DATE |
|* 26 | TABLE ACCESS BY INDEX ROWID | TICKET_ALL
|
|* 27 |INDEX UNIQUE SCAN
| SYS_C0024631
|
|28 |COLLECTION ITERATOR PICKLER FETCH| F_GET_ZGS_XJGS
|
|29 | NESTED LOOPS
|
|
|30 |TABLE ACCESS BY INDEX ROWID| KH_KHDD
|
|* 31 | INDEX UNIQUE SCAN
| SYS_C0021538
|
|* 32 |TABLE ACCESS BY INDEX ROWID| KH_KHDD_YXZT
|
|* 33 | INDEX UNIQUE SCAN
| PK_KH_KHDD_YXZT
|
|* 34 | TABLE ACCESS BY INDEX ROWID | SMS_HBYD_MX
|
|35 |NESTED LOOPS
|
|
|36 | TABLE ACCESS BY INDEX ROWID | SMS_HBYD
|
|* 37 |
INDEX RANGE SCAN
| SMS_HBYD_CFRQ_IDX
|
|* 38 | INDEX RANGE SCAN
| IDX_SMS_HBYD_MX
|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(( IS NULL ANDIS NULL))
6 - filter(("H"."SFGQ"='1' AND "H"."ZHGQ_CFDATETIME">=:B2 AND "H"."ZHGQ_CFDAT
9 - filter(("T"."ZKFX"='0' AND "T"."CP_DATETIME_DATE">=:B3 AND
TO_NUMBER("F_LB_IFMOBILE"("T"."JP_CJRSMSSTATUS"))=1 AND "T"."CP_CO
10 - access("T"."TKNO"="H"."TKNO")
13 - access("K"."DDBH"=:B1)
14 - filter((INTERNAL_FUNCTION("K"."DDZT") OR NVL("Y"."PNR_TYPE",'0')='0' OR N
15 - access("K"."PNR_ZT"="Y"."PNR_ZT")
16 - filter("M"."CFSJ"=SUBSTR(:B1,12,5))
19 - access("S"."HBH"=:B1 AND "S"."CFRQ"=SUBSTR(:B2,1,10))
20 - access("S"."ID"="M"."YDID")
21 - filter(( IS NULL ANDIS NULL))
22 - access("T"."CP_COMPID"=VALUE(KOKBF$))
24 - filter((("H"."SFGQ" IS NULL OR "H"."SFGQ"='0') AND (LNNVL("H"."ZHGQ_CFDAT
LNNVL("H"."ZHGQ_CFDATETIME">=:B2) OR LNNVL("H"."SFGQ"='1'))))
25 - access("H"."CFDATETIME_DATE">=:B2 AND "H"."CFDATETIME_DATE"=:B3 AND
TO_NUMBER("F_LB_IFMOBILE"("T"."JP_CJRSMSSTATUS"))=1))
27 - access("T"."TKNO"="H"."TKNO")
31 - access("K"."DDBH"=:B1)
32 - filter((INTERNAL_FUNCTION("K"."DDZT") OR NVL("Y"."PNR_TYPE",'0')='0' OR N
33 - access("K"."PNR_ZT"="Y"."PNR_ZT")
34 - filter("M"."CFSJ"=SUBSTR(:B1,12,5))
37 - access("S"."HBH"=:B1 AND "S"."CFRQ"=SUBSTR(:B2,1,10))
38 - access("S"."ID"="M"."YDID")
88 rows selected复制代码
|
