SQL query for 9ir2

From: James A. Williams <jwilliam_at_aglresources.com>
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

Original text of this message