Query Optimization

[himridul]

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','mmddyyyy')) =
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 ?

 

[himridul]

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
7 physical reads
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

 

[sbix]

From what I see you're calcultaing the max registration date for each customer in table customer_account which is listed also in custacc_tmp.
If custacc_tmp contains only one row an indexed access is a good choice, but if custacc_tmp contains thousands of rows a full table scan is better.
I would calculate the two TO_DATE for the NVL outside the SQL statement (by the moment they are 2 constant) by assigning them to a variable to use into the SQL statement