SQL help

From: Nirmalya Das <nirmalya_at_hln.com>
Date: Wed, 19 Apr 2006 18:11:27 -0700
Message-ID: <20060419181127.pho4s844ggk4g44s@www.hln.com>

The logical read for this query is way too high for the rows returned.

Anything stands out..............

Also why I am not getting the row count in the explain plan....

---

SELECT wa_ho.work_assignment_pk,
       wa_ho.channel_override_cd,
       wa_ho.can_supplement_flg,
       wa_ho.work_assignment_status AS status,
       (CASE
           WHEN wa_ho.work_assignment_status = 'NT'
              THEN 'Manual'
           WHEN wa_ho.work_assignment_status = 'NS'
              THEN 'Staff Appraiser'
           WHEN wa_ho.work_assignment_status = 'NE'
              THEN 'Manual'
        END
       ) destination,
       CAST (CAST(wa_ho.assignment_date AS TIMESTAMP WITH TIME ZONE) AT TIME

ZONE 'EST' AS DAT
E) AS assign_date,

       CAST (CAST(wa_ho.create_datetime AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'EST' AS DAT
E) AS create_datetime,

       c.claim_nbr,
       c.policy_nbr,
       dv.description AS object_desc,
       (CASE
           WHEN cp.last_name IS NULL
              THEN cp.first_name
           WHEN cp.first_name IS NULL
              THEN cp.last_name
           WHEN cp.last_name IS NOT NULL AND cp.last_name IS NOT NULL
              THEN cp.last_name || ', ' || cp.first_name
           ELSE ''
        END
       ) objowner
  FROM cgw30.damaged_vehicle dv,
       cgw30.claim_party cp,
       cgw30.claim c,
       cgw30.work_assignment wa_ho,
       cgw30.work_assignment_entity wae

 WHERE cp.CLAIM_PARTY_ROLE IN ( 'AG','OY')    AND UPPER(cp.last_name) LIKE 'S%'
   AND cp.claim_pk = c.claim_pk
   AND c.claim_pk=dv.claim_pk
   AND dv.damaged_object_pk=wa_ho.damaged_object_pk    AND wa_ho.work_assignment_type IN ('HO', 'EST')    AND wa_ho.assign_to_pk = wae.work_assignment_entity_pk    AND wae.ldap_identity IN ('404.CTD', '404.WAD')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------

• ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.02 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 117 1.95 1.87 0 149104 0 1728
• ------ -------- ---------- ---------- ---------- ---------- ---------- total 119 1.98 1.89 0 149104 0 1728

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 141 (NDAS)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   TABLE ACCESS   GOAL: ANALYZED (BY GLOBAL INDEX ROWID) OF
              'CLAIM_PARTY' PARTITION:ROW LOCATION
      0    NESTED LOOPS
      0     NESTED LOOPS
      0      NESTED LOOPS
      0       NESTED LOOPS
      0        INLIST ITERATOR
      0         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'WORK_ASSIGNMENT_ENTITY_IE6' (NON-UNIQUE)
      0        TABLE ACCESS   GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
                   OF 'WORK_ASSIGNMENT' PARTITION:ROW LOCATION
      0         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'WORK_ASSIGNMENT_IE1' (NON-UNIQUE)
      0       INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                  'DAMAGE_VEHICLE_PK_I' (NON-UNIQUE)
      0      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'CLAIM_PK_I'
                 (NON-UNIQUE)
      0     INLIST ITERATOR
      0      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'CP_UK_I'
                 (NON-UNIQUE)
-------------------------------------------------------------------------------

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 19 2006 - 20:11:27 CDT