Re: optimizer function based indexes

Hi,
Have you checked OPTIMIZER_FEATURES_ENABLE?

oli

oracle-db_at_bretterbauer.at wrote:
> hello i've update my database from 8.1.7 release 3 to 9i
> and now following statement don't use my optimizer hint in 9i (in 8i it
> works)
>
> upper_artikel_kurztext_idx is an function based index
> select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel
> where upper(kurztext) LIKE '%WANN%'
>
> when i change the statement it works but i need above statement
> select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel
> where upper(kurztext) LIKE 'WANN%' or
> select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel
> where upper(kurztext) = 'WANN'
>
> the following parameters are set in init.ora
> QUERY_REWRITE_ENABLED=TRUE
> QUERY_REWRITE_INTEGRITY=TRUSTED
> OPTIMIZER_MODE = CHOOSE
>
> database is analyzed !
>
> any ideas
>
> thanx joe
>
>

Content-Type: text/html; charset="iso-8859-1"; name="Anhang: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 
----------------------------------------

-- 
Oliver Artelt
Oracle Certified DBA

cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
email: info@cubeoffice.de # web: http://www.cubeoffice.de
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Oliver Artelt
  INET: oli_at_md.transnet.de

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