SQL help

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

Other related posts: