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

Thread: Function Based Indexes

  1. #1
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    I get this error:
    "ORA-01743: only pure functions can be indexed"

    when I try to do this:

    create index date_index
    on mytable(to_date(date_column, 'MM/DD/RR'))
    tablespace process_index;

    See, this "date_column" is not of "date" datatype, rather of varchar, and, it's not consistent...meaning sometimes it's '8/6/02' and sometimes it's '8/06/02'...so implicit conversion doesn't work, therefore I have to use the to_date function in my queries.

    I need to index this column becasue there's over 20 million rows in this table, but when I try to create a function based index with the above statement, I get the error.

    Help...any sugestions?

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    it needs to be deterministic...that is when you execute the statement it will return the same result every time, otherwise you can't call in the fbi on this one
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Originally posted by stmontgo
    it needs to be deterministic...that is when you execute the statement it will return the same result every time
    Can you elaborate on the meaning? I don't understand how the function, to_date(column_name, 'MM-DD-RR') when used in a query, DOES NOT return same results every time.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's deja vu all over again!

    The evaluation of the 'RR' bit depends on the current value of SYSDATE, therefore it is not "pure"

  5. #5
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Thank you...now that makes sense to me why one would say this function is not "pure" or not of "deterministic" manner.

    So any suggestion how I can accomplish this? I needed to use 'RR' because the values on the column has '98' thru '02'.

    I suppose creating a user-defined function might solve my issue, but how? I'm not the best at creating functions...

    THanks

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