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 <jack_silvey_at_yahoo.com> 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" <imma_at_lycos.com> 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: imma_at_lycos.com
> >
> > 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: ListGuru_at_fatcity.com (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: jack_silvey_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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: jack_silvey_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Tue Jul 09 2002 - 16:39:25 CDT
