Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29

    Unanswered: Function based indexing for trunc

    Hi,

    i have calculated columns which is done on single column against current date trunc(currtent_date-due_date).

    because of performance issue i am trying to create the function based indexing,
    becuase i have used trunc oracle is not allowing indexing on this columns.

    SQL Error: ORA-01743: only pure functions can be indexed.
    i tried to create index (current_date-due_date) same error is occurred.

    is there any alternative way to achieve this..?
    Last edited by scharan07; 08-30-10 at 06:04. Reason: updated with my R n d works on this issue.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please show us the full CREATE INDEX statement, as we cannot see your screen from here.
    (Ideally show us the full definition of the table as a CREATE TABLE as well)

    i tried to create index (current_date-due_date) same error is occurred.
    You can't index an expression that changes every day.

  3. #3
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    This is my index creation code.

    Code:
    CREATE INDEX NOOFDAYS_INDEX ON MY_TABLE((CURRENT_DATE-DUE_DATE));
    SO YOUR SAYING THAT IS NOT POSSIBLE TO CREATE AN INDEX WHICH VALUE IS CHANGE DAILY.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by scharan07 View Post
    TSO YOUR SAYING THAT IS NOT POSSIBLE TO CREATE AN INDEX WHICH VALUE IS CHANGE DAILY.
    Correct.

    It would actually change every second as CURRENT_DATE includes the time as well.

    How do you expect that to work? That Oracle automagically re-creates the index each time the expression changes? With your example, Oracle would need to re-create the entire index every second to recalculate the expression....

    And please do not write in all upper-case. That is consider rude (shouting).

  5. #5
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    shammat,
    thanks for replies and your valuable information to me.

    And please do not write in all upper-case. That is consider rude (shouting).
    thats unfortunately happened, because of i have written my index code in capitals the same continued in next line.

Posting Permissions

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