optimizer question

From: Josef Huber <josef.huber_at_ennstal.at>
Date: Fri, 28 Sep 2001 10:52:15 +0200
Message-ID: <3bb43b37@news.profinet.at>

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 Received on Fri Sep 28 2001 - 03:52:15 CDT