DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Need Query tuning

  1. #1
    Join Date
    Apr 2003
    Posts
    19

    Need Query tuning

    I have got the below query which is taking 2 min to execute this query,the table contains 3.8 M records.I have used Index hint in this query,still it is taking the same time ,No change before and after using index hint.

    select txn_no,seq_no,purchase_amount,units_allotted,pop
    from purchase_log pl
    where pl.fund_option = 006
    and account_number = 89863956310200
    and purchase_allotted_date <= '30-sep-2003'
    and txn_status<>'R';

    There are indexes as follows.

    SYS_C4026(unique) txn_no,seq_no.
    WEB#PURCHASE_LOG2 accout_number,purchase_allotted_date
    WEB#PURCHASE_LOG3 fund_option,Batch_run_date.


    Kindly look into this query and give me the solution
    Thank u all.

    sk_nadu
    sknaidu

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What datatype is the column ACCOUNT_NUMBER - char/varchar or number? What datatype is the column PURCAHSE_ALLOTTED_DAY- char/varchar or date?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Why don't You post EXPLAIN PLAN for this statement?
    Nagesh

  4. #4
    Join Date
    Apr 2003
    Posts
    19
    Hi Jurij Modic

    Account_number is varchar2(13) and PURCHASE_ALLOTTED_DATE is date type.
    thanks
    sk_naidu
    sknaidu

  5. #5
    Join Date
    Apr 2003
    Posts
    19
    hi gnagesh
    here it is

    1 explain plan
    2 set statement_id = 'bad1' for
    3 select txn_no,seq_no,purchase_amount,units_allotted,pop
    4 from purchase_log pl
    5 where account_number = 0069900464457
    6 and purchase_allotted_date <= '30-SEP-2003'
    7 and pl.fund_option = 006
    8 and txn_no<>'P'
    9 and seq_NO<>0
    10* and txn_status<>'R'
    SQL> /

    Explained.

    SQL> SELECT cardinality "Rows",
    2 lpad(' ',level-1)||operation||' '||
    3 options||' '||object_name "Plan"
    4 FROM PLAN_TABLE
    5 CONNECT BY prior id = parent_id
    6 AND prior statement_id = statement_id
    7 START WITH id = 0
    8 AND statement_id = 'bad1'
    9 ORDER BY id;

    Rows
    ----------
    Plan
    ---------------------------------------------------------------
    1
    SELECT STATEMENT

    1
    TABLE ACCESS FULL PURCHASE_LOG




    thanks
    sk_naidu
    sknaidu

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Did you analyze table?

    Try this:
    select txn_no,seq_no,purchase_amount,units_allotted,pop
    from purchase_log pl
    where pl.fund_option = 006
    and account_number = '89863956310200'
    and purchase_allotted_date <= to_date('30-sep-2003','dd-mon-yyyy')
    and txn_status<>'R';

    Tamil

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by SKNaidu
    Hi Jurij Modic

    Account_number is varchar2(13) and PURCHASE_ALLOTTED_DATE is date type.
    thanks
    sk_naidu
    Wel, this expalins it. Since account_number is VARCHAR2 column, you must change the following line from your query
    Code:
    and account_number = 89863956310200
    into this
    Code:
    and account_number = '89863956310200'
    to enable Oracle to use your index WEB#PURCHASE_LOG2.

    Also, change the following line
    Code:
    purchase_allotted_date <= '30-sep-2003'
    into this
    Code:
    purchase_allotted_date <= to_date('30-sep-2003','dd-mon-yyyy')
    P.S.
    Tamiselvan has allready pointed this out, however I'm not sure you noticed the differences in the complete query....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Oct 2003
    Posts
    5
    Hi, If you are comparing characters to number
    Give as ... and to_number(account_number) = 89863956310200. This should work.
    Thanks

  9. #9
    Join Date
    Apr 2003
    Posts
    19
    Hi friends

    This is actually my query and i had hard coded the values.
    now pl tell me what to do to run this qury fast enough.
    here account_no is varchar2
    fund_option is varchar2.


    select /*+ index */ txn_no,seq_no,purchase_amount,units_allotted,pop
    from purchase_log pl
    where pl.fund_option = trim(fund_option)
    and account_number = trim(account_no)
    and purchase_allotted_date <=asondate
    and txn_status<>'R';


    pl. help in this issue.
    sk_naidu
    sknaidu

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Suman Thummala
    Hi, If you are comparing characters to number
    Give as ... and to_number(account_number) = 89863956310200. This should work.
    Thanks

    that's worse, no index can be used if you use a function in your predicate unless you create an function based index

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