Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!rip!news.webusenet.com!peer01.cox.net!cox.net!kibo.news.demon.net!news.demon.co.uk!demon!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: function based index and DB - startupparameters Date: Fri, 10 Oct 2003 08:33:43 +0100 Lines: 70 Message-ID: References: <3f7abcf4$0$13656$afc38c87@news.optusnet.com.au> <6d8b7216.0310011252.309a1000@posting.google.com> NNTP-Posting-Host: jlcomp.demon.co.uk X-Trace: news.demon.co.uk 1065771428 15739 158.152.75.41 (10 Oct 2003 07:37:08 GMT) X-Complaints-To: abuse@demon.net NNTP-Posting-Date: Fri, 10 Oct 2003 07:37:08 +0000 (UTC) X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Priority: 3 X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MSMail-Priority: Normal Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:244972 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" wrote in message news:6d8b7216.0310011252.309a1000@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