SQL query for 9ir2
Date: 30 Mar 2004 13:58:29 -0800
Message-ID: <5003a2b9.0403301358.15177d9e_at_posting.google.com>
A user sent me the below query. Since I can't change to business logic I am looking maybe at an FBI. Any thoughts.
SELECT (SFLD.source_order_nbr) order_number,
to_char(SO.DT_SO_PEND,'mm/dd/yy') appointment_date_c,
to_char(to_date(SO.TM_APPT,'hh24mi'),'hh:mi am') appointment_time_c,
to_char(SO.DT_SO_CMPLT,'mm/dd/yy') completion_date_c,
' ' completion_time_c,
so.cd_mup_ord_type,
decode(SO.CD_SO_STAT, 54, 'CMPL', 57, 'VOID') order_status,
SO.NM_CUST_1, (P.AD_SERV_STR_NO ||' '|| SN.AD_SERV_CDL_DIR ||' '|| SN.AD_SERV_STR_NM ||' '|| SN.AD_SERV_STR_SFIX||' '|| SN.AD_SERV_SFIX ||', '|| P.AD_SERV_STRUC ) premise_address,
SN.AD_SERV_CITY, to_char(SN.AD_SERV_ZIP),
'C' data_source,
' ',
' ',
' ',
' '
FROM SERV_ORD SO,PREMISE P, STREET_NAME SN, SO_FLD_ORD SFLD WHERE (( SO.CD_SPEC_DT = 'BYPS' OR SO.CD_SPEC_DT = 'PROR' OR (SO.CD_SPEC_DT = 'DLAY' AND (TM_APPT is null OR TM_APPT = ''))) or CD_SO_STAT = 57) and
(SO.DT_SO_CMPLT > to_date(:ra_end_date,'yyyymmdd') or SO.DT_SO_CMPLT is null) and
( SO.KY_SO_NO = SFLD.KY_SO_NO ) AND
to_char( SO.DT_SO_PEND,'mm/dd/yyyy') =
to_char(SFLD.DT_SO_PEND,'mm/dd/yyyy') AND
( SO.KY_SO_NO = P.KY_SO_NO(+) AND SO.DT_SO_PEND = P.DT_SO_PEND(+) AND SO.KY_PREM_NO = P.KY_PREM_NO(+) ) AND (P.KY_SO_NO = SN.KY_SO_NO(+) AND P.DT_SO_PEND = SN.DT_SO_PEND(+) AND P.KY_STR_NM = SN.KY_STR_NM(+)) and so.ad_compressed like '953 TARA BND%'
Below is the execution plan for the above on Solaris 2.8 9.2.0.4
OPS$ORACLE>_at_explainer2
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows |Bytes | Cost |
| 0 | SELECT STATEMENT | | 9 | 1791 | 38847 | | 1 | NESTED LOOPS OUTER | | 9 | 1791 | 38847 | | 2 | NESTED LOOPS OUTER | | 9 | 1395 | 38829 | |* 3 | HASH JOIN | | 9 | 1116 | 38811 | |* 4 | TABLE ACCESS FULL | SERV_ORD | 794 | 73842 | 35610 | | 5 | TABLE ACCESS FULL | SO_FLD_ORD | 3814K| 112M| 2979 | | 6 | TABLE ACCESS BY INDEX ROWID| PREMISE | 1 | 31 | 2 |
PLAN_TABLE_OUTPUT
|* 7 | INDEX UNIQUE SCAN | PREMISE_PK | 1 | | 1 | | 8 | TABLE ACCESS BY INDEX ROWID | STREET_NAME | 1 | 44 | 2 | |* 9 | INDEX UNIQUE SCAN | STREET_NAME_PK | 1 | | 1 | ---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
3 - access("SO"."KY_SO_NO"="SFLD"."KY_SO_NO" AND
TO_CHAR("SO"."DT_SO_PEND",'mm/dd/yyyy')=TO_CHAR("SFLD"."DT_SO_PEND",'mm/dd/yyyy') )
PLAN_TABLE_OUTPUT
4 - filter(("SO"."CD_SPEC_DT"='BYPS' OR "SO"."CD_SPEC_DT"='PROR' OR
"SO"."CD_SPEC_DT"='DLAY' AND ("SO"."TM_APPT" IS NULL OR
"SO"."TM_APPT"='') OR
"SO"."CD_SO_STAT"=57) AND
("SO"."DT_SO_CMPLT">TO_DATE('2004-03-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss') OR "SO"."DT_SO_CMPLT" IS NULL)
AND "SO"."AD_COMPRESSED"
LIKE '953 TARA BND%')
7 - access("SO"."KY_PREM_NO"="P"."KY_PREM_NO"(+) AND
"SO"."KY_SO_NO"="P"."KY_SO_NO"(+) AND "SO"."DT_SO_PEND"="P"."DT_SO_PEND"(+)) 9 - access("P"."KY_STR_NM"="SN"."KY_STR_NM"(+) AND "P"."KY_SO_NO"="SN"."KY_SO_NO"(+) AND"P"."DT_SO_PEND"="SN"."DT_SO_PEND"(+)) Note: cpu costing is off
33 rows selected.
OPS$ORACLE> Received on Tue Mar 30 2004 - 23:58:29 CEST