Re: function based index and DB - startupparameters

It certainly used to be true that you had to set query_rewrite_enabled to true to
use a function based index at all, and then query_rewrite_integrity = trusted to use a home-grown pl/sql function. You also
had to have the query rewrite privilege
to create the index in the first place.

It's the sort of area where Oracle makes little changes with newer releases, though, sometimes simply to change the default
value init.ora parameters; so it's worth having a little test case built that can be re-run on each point release.

(e.g. I think at some point from 8 to 9, the query_rewrite_enabled default changed from false to true - so a statement like "you don't need to set ..." is suddenly true, because you don't set it, it is set by default.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Steve Howard" <stephen.howard_at_us.pwcglobal.com> wrote in message
news:6d8b7216.0310011252.309a1000_at_posting.google.com...


>

> I thought this was only for programmer defined functions, to ensure

> that the function always returned the same value given the same

> inputs.  With Oracle's internally written functions like UPPER, I

> thought this was not the case.

>

> My memory has been known to fail me, and I don't have a test case

> handy, but I remember trying this and not setting the TRUSTED

> parameter, but the FBI based on something like UPPER was still used.

>

> Any insight?

>

> Regards,

>

> Steve