DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Tuning - URGENT

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Hi oracle guru's

    Please help me in tuning SQL statement. The details of indexes on table and results of Explain plan are :

    INDEX DETAILS :

    INDEX_NAME COLUMN_NAME COLUMN_POSITION
    ------------------------------ -------------------- ---------------
    IND_WLDT_APPDT APP_DATE 1
    IND_WLDT_BC BILLCYCLE 1
    IND_WLDT_COLAGE COL_AGING 1
    IND_WLDT_PRG PRGCODE 1
    IND_WLDT_SER SERVICE 1
    IND_WLDT_STATUS STATUS 1
    IND_WLDT_SVRAGE SVR_AGING 1

    WD_PK BILLCYCLE 1
    WD_PK APP_DATE 2
    WD_PK SERVICE 3
    WD_PK CUSTCODE 4


    PLAN STATEMENT :

    COLUMN plan FORMAT a70
    SELECT lpad(' ', 3*level)||operation||'('||options||')
    '||object_name||
    ' ' ||object_type plan
    FROM plan_table
    CONNECT BY PRIOR id = parent_id and statement_id='STMT1'
    START WITH id = 1 and statement_id = 'STMT1';


    PLAN RESULTS :

    PLAN
    ----------------------------------------------------------------------
    TABLE ACCESS(BY INDEX ROWID)
    WL_DETAILS

    INDEX(RANGE SCAN)
    WD_PK UNIQUE

    SORT(AGGREGATE)


    INDEX(RANGE SCAN)
    WD_PK UNIQUE

    PLAN
    ----------------------------------------------------------------------

    Please Help in tuning the above statement.

    Thanks In Advance.
    Nagesh

  2. #2
    Join Date
    Sep 2000
    Posts
    128
    and which statement would that be? :)

  3. #3
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Dear TerryD,

    Sorry I have forgotten to supply the sequel statement. This is the statement :-

    delete plan_table
    where statement_id = 'STMT1'
    /
    EXPLAIN PLAN
    SET STATEMENT_ID = 'STMT1'
    FOR
    select * from wl_details a
    where a.billcycle = '03'
    and a.app_date is not null
    and a.service is not null
    and a.custcode = '1.611832'
    and a.app_date = (select max(b.app_date)
    from wl_details b
    where billcycle = '03'
    and app_date is not null
    and service is not null
    and custcode = '1.611832')

    Thanks
    Nagesh

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The problem is that you are going into the same table twice. The following SQL should help. I, of course, do not have the tables, so you may need to debug it a little first, but it should give you the idea. Also, this only works in 8i, AFAIK. Before that, you couldn't put an ORDER BY in a sub-select. Also, since you did not provide exact field names in your select, I was unable to remove the QUERY_ROWNUM from the final result. I'll assume this was for simplicity since one would never, of course, use a * in a production SELECT statement ;)

    SELECT
    *
    FROM
    (
    SELECT
    A.* ,
    ROWNUM
    AS QUERY_ROWNUM
    FROM
    WL_DETAILS A
    WHERE
    A.BILLCYCLE = '03'
    A.APP_DATE IS NOT NULL
    A.SERVICE IS NOT NULL
    A.CUSTCODE = '1.611832'
    ORDER BY
    A.APP_DATE DESC
    )
    WHERE
    QUERY_ROWNUM = 1

    Another thought is to replace the IS NOT NULLs with > 'a' or < '1/1/80' or something to eliminate the NULL issue so any indexes on these columns could be used.

    Hope this helps,

    - Chris

  5. #5
    Join Date
    Sep 2000
    Posts
    384
    How many distinct values are there in billcycle??
    Radhakrishnan.M

Posting Permissions

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


Click Here to Expand Forum to Full Width