A friend of mine named Jane just called me and pointed out that you could create a function-based index on the column using this syntax, but you can't use a bind variable to define it.
If you create an index with this form nvl(column_name, '_null_') and then issue a query with the form where nvl(column_name, '_null_') = :bind then the function-based index is a candidate to be used. However, since you are using bind variables, oracle is having to use a default data estimate to decide on execution plan, so you may still have to hint the index to get it to be used. As always, test and then test some more. hth, Jack --- Jack Silvey <[EMAIL PROTECTED]> wrote: > Imma, > > First thought is no - oracle implements > function-based > indexes by using a hidden psuedocolumn to hold the > resultant data of the function. Since you would be > using a bind variable, it would not be able to > create > this column. > > Since you are passing a variable, Oracle cannot use > the table stats (high/low values, histograms) to > determine access path easily, so is choosing a FTS. > > If you are sure that you want to use an index, you > might consider forcing an index lookup via a hint. A > word of caution - when using the index hint, > consider > not putting the name of the index in the hint, since > if you do, and the index name ever changes, the hint > will be ignored and the FTS behavior will return. > > hth, > > Jack > > > > --- "Imma C. Rocco" <[EMAIL PROTECTED]> wrote: > > Hi, > > I have read that on Oracle 8.1.7 it is possible to > > create a function based index like the one: > > Create index ind1 on <table> > (substr(<column_name>)) > > tabelspace ....etc > > And if Oracle optimizer is in CBO mode and > > query_rewrite_enabled = true > > query_rewrite_integrity = trusted > > compatible = 8.1.0 or greater > > A statement which has a where clause that involve > a > > substr(<colum_name>) should use the index. > > > > > > (*)I would like to know if it is also possible to > > create a function based index on a NVL function > that > > involve a bind variable, because a have a > statement > > like the one that follow: > > select 'x' > > from <table> > > where <col_name> = nvl(:p_aa, <col_name>) > > > > This statement perform a full table scan on > <table> > > > > ps: if (*) is possible could you please give an > > sintax example? > > > > Thanks > > Imma > > > > > > > _____________________________________________________ > > Supercharge your e-mail with a 25MB Inbox, POP3 > > Access, No Ads > > and NoTaglines --> LYCOS MAIL PLUS. > > > http://www.mail.lycos.com/brandPage.shtml?pageId=plus > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Imma C. Rocco > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- (858) 538-5051 > FAX: > > (858) 538-5051 > > San Diego, California -- Public Internet > > access / Mailing Lists > > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be > removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > > __________________________________________________ > Do You Yahoo!? > Sign up for SBC Yahoo! Dial - First Month Free > http://sbc.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jack Silvey > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
