Re: SQL help

Nirmalya,

It's hard to diagnose anything without an idea about the respective sizes of tables (or partitions in the case of work_assignment), a description of indexes and some ideas about the selectivity of the various indexes that are available.
through joins from claim_party to claim, damaged_vehicle The only thing that strikes me on casual inspection is that unless you have a function based index your condition on LAST_NAME that *might* be selective will count for nothing. As I see it, you have a list of tables that goes, through joins, from claim_party to claim, damaged_vehicle, work_assignment and work_assignment_entity with filtering conditions that apply at both ends of the chain (that is, claim_party and work_assignment + work_assignment_entity). Your execution plan shows from which end Oracle retrieves the data. Could it be possibly more efficient coming from the other end? If it converges faster to the final result set that's what you should aim for.
If you have an index on (claim_party_role, last_name), something easy to try might be to replace upper(cp.last_name) like 'S%' by (cp.last_name like 'S%' or cp.last_name like 's%').

HTH

S Faroult

Nirmalya Das wrote:

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')





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


Other related posts: