1. Registered User
Join Date
Jan 2004
Posts
66

Hi ,

Following is my query , which is giving high cost :

SELECT
custacc.aolindividualid
,custacc.accountid
,custacc.serviceid
,custacc_tmp.registrationdate
FROM
customeraccount custacc
,(
SELECT /*+ PARALLEL(customeraccount,16)*/
aolindividualid
,max(registrationdate) registrationdate
FROM
customeraccount
GROUP BY
aolindividualid
) custacc_tmp
WHERE
custacc.aolindividualid = custacc_tmp.aolindividualid
AND NVL(custacc.registrationdate,TO_DATE('12312099','m mddyyyy')) =
NVL(custacc_tmp.registrationdate,TO_DATE('12312099 ','mmddyyyy'))

Actually I need the row which has maximum registrationdate for a particular aolindividualid .

Is there any better solution to reduce the cost ?

2. Registered User
Join Date
Jan 2004
Location
India
Posts
191
Can u pls post Explain plan for the query

3. Registered User
Join Date
Jan 2004
Posts
66
When no. of rows =3 in the table customeraccount table ,
this is the execution plan

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=42)
1 0 HASH JOIN (Cost=5 Card=1 Bytes=42)
2 1 VIEW (Cost=2 Card=3 Bytes=66)
3 2 SORT (GROUP BY) (Cost=2 Card=3 Bytes=27)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERACCOUNT
' (Cost=2 Card=5 Bytes=45)

5 4 INDEX (FULL SCAN) OF 'PKCUSTOMERACCOUNT' (UNIQUE)
(Cost=1 Card=5)

6 1 TABLE ACCESS (FULL) OF 'CUSTOMERACCOUNT' (Cost=2 Ca
rd=5 Bytes=100)

Statistics
----------------------------------------------------------
392 recursive calls
0 db block gets
94 consistent gets
0 redo size
399 bytes sent via SQL*Net to client
344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
3 rows processed

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•