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@xxxxxxx> 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) > > ------------------------------------------------------------------------------- > -- > //www.freelists.org/webpage/oracle-l > > >