Function-based index help needed 1

[SJSFoxPro]

I have a SQL query that I believe is causing an implicit date conversion in my where clause. The query is:
select to_char(min(begin_date),'yyyy-MM-dd') from pay_table where TO_TIMESTAMP ('2008-07-01', 'YYYY-MM-DD HH24:MI:SS.FF') <= pay_date and TO_TIMESTAMP ('2009-6-30', 'YYYY-MM-DD HH24:MI:SS.FF') >= end_date

This is a "canned" vendor application, so the SQL cannot be changed. I have been asked to look into creating an index that will speed this up. I tried creating a function-based index on the date columns, but got the "ORA-01743: only pure functions can be indexed" error. The index I tried to create was:
CREATE INDEX PAY_TABLE_FBI ON PAY_TABLE
(TO_TIMESTAMP (PAY_DATE, 'YYYY-MM-DD HH24:MI:SS.FF'), TO_TIMESTAMP (END_DATE, 'YYYY-MM-DD HH24:MI:SS.FF'), BEGIN_DATE);

I've been reading up on only using functions that are deterministic, but it would seem that this would return the same value for the inputs. I believe I'm okay with regards to nls date parameters.

Can anyone suggest other reading I can do? Or, point out the obvious if I'm missing it ;-)

Thanks much!

 

[SantaMufasa]

SJSFoxPro,

Oracle is kinda quirky that way. But, you can workaround Oracle's exclusion of DATEs from function-based indexes by "tricking" Oracle. Following illustrates both the problem and the solution (using Oracle Education's "S_EMP" table as the source of the DATE/TIMESTAMP data):

SQL> create index temp_emp_timestamp on s_emp (to_timestamp(start_date));
create index temp_emp_timestamp on s_emp (to_timestamp(start_date))
                                          *
ERROR at line 1:
ORA-01743: only pure functions can be indexed

create or replace function idx_timestamp (date_in date) return timestamp deterministic is
begin
    return to_timestamp(date_in);
end;
/

Function created.

(Voilà...)

SQL> create index temp_emp_timestamp on s_emp (idx_timestamp(start_date));

Index created.

Let us know if this takes care of business for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[SJSFoxPro]

This did help. And, it really helped with giving that "second opinion" that to_timestamp could not be used to create the index (at least not without some tinkering). What I would like to find is some documentation providing more of a list of functions that cannot be used. Instead, I find only a brief example here and there. I guess that's what trial and error is for! I'd just feel better with something documented to back me up!

Thanks much for the help! As always, you are a wealth of knowledge. I did back it up with a purple star - because I appreciate the help!

 

[SantaMufasa]

I would hope that Oracle has documentation somewhere that fully explains both pure functions and which functions fall into the category of "impure" functions. Unfortunately, I am not aware of where to find that documentation.

All I can suggest is to Google for terms such as "ORA-01743" and/or "pure functions". Once you are satisfied with your findings, please post the highlights here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
This expanded error text may shed some light on the differnces:

ORA-01743: only pure functions can be indexed
Problem: The indexed function uses SYSDATE or the user environment.
Solution: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL expressions must not use
SYSDATE, USER, USERENV(), or anything else dependent on the session state. NLS-dependent functions are
OK.

It seems that it is the need for 'session-state' info that makes the function 'impure'.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[karluk]

For what it's worth, I did some experimentation on your issue and composed the following answer based on your passing date datatypes (rather than character) to the "TO_TIMESTAMP" function. Unfortunately, the obvious fix of explicitly converting the date inputs to character doesn't resolve the issue, so the underlying problem must be more subtle.



One issue is that you are using columns that are DATE datatypes as input to the TO_TIMESTAMP function. If you check the Oracle 10g documentation, you will find that TO_TIMESTAMP needs character type input:

TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

If your inputs are date datatypes, Oracle will need to first convert it to character before proceeding to the timestamp conversion. Unfortunately this intermediate conversion to character makes your function nondeterministic. The result depends upon a number of other factors in addition to the column values being processed. Hence the ORA-01743 error.

To take a concrete example, look at what happens when you use SYSDATE as the argument of TO_TIMESTAMP. With my database's default NLS_DATE_FORMAT of 'DD-MON-YY' I get the following results:

SQL> select to_timestamp(sysdate,'yyyy-mm-dd hh24:mi:ss.ff') from dual;

08-JUL-29 12.00.00 AM

As you can see, the intermediate step of converting sysdate to character results in the loss of hours, minutes and seconds from the output. Compare this with what happens if I run the same query with an NLS_DATE_FORMAT of 'DD-MON-YY HH24:MI:SS':

SQL> alter session set nls_date_format='dd-MON-yy hh24:mi:ss';

Session altered.

SQL> select to_timestamp(sysdate,'yyyy-mm-dd hh24:mi:ss.ff') from dual;

08-JUL-29 11.07.04 AM

 

[SJSFoxPro]

Thanks for the additional insight. My original question probably didn't make it clear, but it's the implicit conversion of pay_date and end_date (to_timestamp(to_char(column)) that I am trying to handle in a function-based index to avoid the overhead at execution time.

In a perfect world, the "canned" application would be updated to rid it of the ugly SQL. But, of course, the DBA must find a solution to get the ugly SQL to perform efficiently at the database and without changing the SQL.

Thanks for all the additional comments. Definitely a conversation worth having. I appreciate the assistance!