SQL Tuning question

[bang_dba]

hi,
Using inbuilt functions on a column in a query like (NVL,TO_CHAR....etc).... does it impact the performance of the query.

I think using these kind of functions result in FTS even though u have a indexes on those columns. Is it advisable to have fuction indexes ????

[abhaysk]

Originally posted by bang_dba
hi,
Is it advisable to have fuction indexes ????

Depends, whether the Cols are always assosicated with such Fn in where clause, then yes.

Abhay.

[DaPi]

That's right:
e.g. WHERE TO_CHAR(primary_key) = . . .
can not use the index on primary_key.

[slimdave]

... and one side-effect of using function-based indexes can be that they break the DBMS_STATS.GATHER_TABLE_STATS() procedure due to this bug. Check whether this would be an issue for you before considering them.