Home » RDBMS Server » Performance Tuning » Function Based Indexes
Function Based Indexes [message #441888] Wed, 03 February 2010 11:51 Go to next message
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 messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
navin_deep wrote on Wed, 03 February 2010 17:51
Hi
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
Michel Cadot
Messages: 63802
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

Previous Topic: Oracle Segment Tuning
Next Topic: Performance issue in Oracle 10g using Bulk Collect
Goto Forum:
  


Current Time: Sun Sep 25 12:57:57 CDT 2016

Total time taken to generate the page: 0.09123 seconds