Re: Oracle 8.1.7 function based indexes

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Tue, 09 Jul 2002 12:20:04 -0800
Message-ID: <F001.00492CF8.20020709122004@fatcity.com>

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

Received on Tue Jul 09 2002 - 15:20:04 CDT