Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Bucharest, Romania
    Posts
    14

    Unanswered: special filtering condition with ADD_MONTHS

    Hello,

    Using Oracle 8i, I try to select some data from the last two months, including current month (total 3 months). I have only MONTH_ID and YEAR_ID as columns that I could link to compute the condition.

    I have two solutions: I don't know why the second is most badly than first one. First SQL statement it takes about 1 sec, and the second one about 21 sec. Could you help me, please, to understand why when I am using the ADD_MONTHS function I have obtain badly performance than I don't use it? Thank you very much.

    1. select distinct AMRB_MONTH_ID, AMRB_YEAR_ID from X
    where
    (X.AMRB_MONTH_ID <= to_number(to_char(sysdate,'MM'))
    X.AMRB_MONTH_ID > to_number(to_char(sysdate,'MM'))-3
    X.AMRB_YEAR_ID = to_number(to_char(sysdate,'YYYY')) )
    OR
    (X.AMRB_MONTH_ID >= 10+to_number(to_char(sysdate,'MM'))
    AND X.AMRB_YEAR_ID = to_number(to_char(sysdate,'YYYY')) -1);

    2. select distinct AMRB_MONTH_ID, AMRB_YEAR_ID from X
    where to_date(X.AMRB_YEAR_ID || X.AMRB_MONTH_ID, 'YYYYMM') >
    ADD_MONTHS(trunc(sysdate,'MONTH'),-3);


    Regards,
    Adrian

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: special filtering condition with ADD_MONTHS

    If there is an index on AMRB_YEAR_ID and AMRB_MONTH_ID then the first query can use it but the second cannot due to the modifications being done to the values by concatenation and to_date function.

    If you created a function-based index on to_date(X.AMRB_YEAR_ID || X.AMRB_MONTH_ID, 'YYYYMM') then that might be fast too.

  3. #3
    Join Date
    Feb 2004
    Location
    Bucharest, Romania
    Posts
    14
    Hello,

    Tony: You are right. I am using an index on AMRB_YEAR_ID and AMRB_MONTH_ID columns.

    But, I want to change the implementation using the ADD_MONTHS function.

    How can I create a function-based index on to_date(X.AMRB_YEAR_ID || X.AMRB_MONTH_ID, 'YYYYMM') ?

    Thank you and best regards,
    Adrian

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Simply:

    create index i on t (to_date(AMRB_YEAR_ID || AMRB_MONTH_ID, 'YYYYMM'));

    Oracle can only use the index if QUERY_REWRITE_ENABLED=TRUE and QUERY_REWRITE_INTEGRITY=TRUSTED. Take a look at "Create Index" in the SQL Reference manual for more info.

Posting Permissions

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