Function Based Indexes [message #441888] |
Wed, 03 February 2010 11:51 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
navin_deep
Messages: 18 Registered: November 2009
|
Junior Member |
|
|
Hi
I understood the concept of Function-Based Indexes but want to know if these are really used in Huge and Critical applications ?
Do any kind of users use function based WHERE clauses OR these are restricted completely to use?
Are such function based WHERE clauses used in front end also to pull data, generate reports etc in Applications ?
Regards
navin
|
|
|
Re: Function Based Indexes [message #441889 is a reply to message #441888] |
Wed, 03 February 2010 12:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
navin_deep wrote on Wed, 03 February 2010 17:51Hi
I understood the concept of Function-Based Indexes but want to know if these are really used in Huge and Critical applications ?
I'd like to hope so. Mine certainly does.
Quote:
Do any kind of users use function based WHERE clauses OR these are restricted completely to use?
Not sure what you mean here?
Quote:
Are such function based WHERE clauses used in front end also to pull data, generate reports etc in Applications ?
If you manage to write a large db application that doesn't make use of any functions in the where cluases of any queries I'll be very impressed (once I've confirmed it actually works properly).
You do realise that the functions used in function based indexes are generally the oracle supplied functions?
e.g. to_Date, to_char, trunc, add_months, nvl, decode etc, etc.
It's generally hard to base function based indexes on custom functions, since most of them are non-deterministic.
|
|
|
Re: Function Based Indexes [message #441890 is a reply to message #441888] |
Wed, 03 February 2010 12:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
FBI are indeed really used in actual applications.
For your second question, you must have the EXECUTE privilege on the function.
Also read Notes on Function-based Indexes in SQL Reference.
In older versions you also must have a privilege that was QUERY REWRITE I think but can't actually remember, it is no more the case in the currently supported versions.
Regards
Michel
[Updated on: Wed, 03 February 2010 12:10] Report message to a moderator
|
|
|