Path: news.easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!enews.sgi.com!newsfeed.nettuno.it!not-for-mail From: Cristian Veronesi Newsgroups: comp.databases.oracle.server Subject: Re: how to use function-based indexes? Date: Wed, 12 Dec 2001 15:59:15 +0100 Organization: C.R.P.A. S.p.A. (All standard disclaimers applied) Lines: 60 Message-ID: <3C1770C3.91534E05@crpa.it> References: <3C161340.D3125449@crpa.it> <3C161C88.D90F4A75@crpa.it> <3c162283$0$230$ed9e5944@reading.news.pipex.net> <3C162538.F77972CB@crpa.it> <3c165eb1$0$561$afc38c87@news.optusnet.com.au> <9v6a6r02drd@drn.newsguy.com> <3C17174E.922A7642@crpa.it> <3c17230b$0$8512$ed9e5944@reading.news.pipex.net> <3C172BD9.547FF1C2@crpa.it> <9v7n2s022d8@drn.newsguy.com> NNTP-Posting-Host: eraclito.crpa.it Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: pinco.nettuno.it 1008169145 25159 212.22.134.33 (12 Dec 2001 14:59:05 GMT) X-Complaints-To: news@nettuno.it NNTP-Posting-Date: 12 Dec 2001 14:59:05 GMT X-Mailer: Mozilla 4.7 [en] (Win95; I) X-Accept-Language: en,pl,ja Xref: easynews comp.databases.oracle.server:127954 X-Received-Date: Wed, 12 Dec 2001 07:59:08 MST (news.easynews.com) Thomas Kyte wrote: > read the posting at: > = > http://osi.oracle.com/~tkyte/article1/index.html > = > it'll help you get your example going. Thank you for all your hints. Now I succeeded to force the query to use the index by using the correct INDEX hint syntax, but without hints the query performs a full-table scan, even if I set optimizer to FIRST_ROWS_10: SQL> alter session set QUERY_REWRITE_ENABLED=3DTRUE; Session altered. SQL> alter session set QUERY_REWRITE_INTEGRITY=3DTRUSTED; Session altered. SQL> alter session set optimizer_mode =3D first_rows_10; Session altered. SQL> select acquirente_rag_soc from acquirenti 2 where UPPER(ACQUIRENTE_RAG_SOC) like 'VERO%'; ACQUIRENTE_RAG_SOC -------------------------------------------------------------------------= ------- VERONI SILVANO VERONI ANGELO E P. PAOLO VERONA ROBERTO EGIDIO E MUSSINI CRISTINA VERONESI DANTE VERONESI SILVANO Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DFIRST_ROWS (Cost=3D2 Card=3D10 Byt= es=3D 200) 1 0 TABLE ACCESS (FULL) OF 'ACQUIRENTI' (Cost=3D2 Card=3D10 Bytes= =3D 200) Thanks for your help. Best regards, Cristian -- = Cristian Veronesi ><((((=BA> http://www.crpa.it There are no good wars, with the following exceptions: The American = Revolution, World War II, and the Star Wars Trilogy. (Bart Simpson)