SQL help
- From: Nirmalya Das <nirmalya@xxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Apr 2006 18:11:27 -0700
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
Other related posts:
