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