Re: SQL help

How did you get this output? I mean, what statistics level did you set in the session?
what version of the db?
rgds
On 4/20/06, Nirmalya Das <nirmalya_at_hln.com> 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')
>
> 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
>
>
>

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