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).
