Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Query Optimization

    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 ?
    himridul

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Can u pls post Explain plan for the query
    Pagnint
    (No need to search web before posting new question)

  3. #3
    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
    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
    himridul

Posting Permissions

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