Path: news.easynews.com!easynews!newshub2.home.com!news.home.com!news.maxwell.syr.edu!pln-e!spln!dex!extra.newsguy.com!newsp.newsguy.com!drn From: Thomas Kyte Newsgroups: comp.databases.oracle.server Subject: Re: how to use function-based indexes? Date: 12 Dec 2001 08:15:15 -0800 Organization: Oracle Lines: 119 Message-ID: <9v7vqj02oms@drn.newsguy.com> 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> <3C1770C3.91534E05@crpa.it> NNTP-Posting-Host: p-978.newsdawg.com X-Newsreader: Direct Read News 2.91 Xref: easynews comp.databases.oracle.server:127963 X-Received-Date: Wed, 12 Dec 2001 09:36:24 MST (news.easynews.com) In article <3C1770C3.91534E05@crpa.it>, Cristian says... > >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: > I'm pretty sure its going to a function of your data or some other init.ora parameter here. Every test I do (i upped to 9i since that is obviously what you have given your optimizer goal) results in the index being used. If you run this test: drop table acquirenti; create table acquirenti ( ACQUIRENTE_RAG_SOC varchar2(80), data char(1) default 'x' ); insert into acquirenti select object_name, 'x' from all_objects where rownum < 10900; insert into acquirenti values ( 'vero1', 'x'); insert into acquirenti values ( 'vero2', 'x'); insert into acquirenti values ( 'vero3', 'x'); insert into acquirenti values ( 'vero4', 'x'); insert into acquirenti values ( 'vero5', 'x'); analyze table acquirenti compute statistics; alter session set query_REWRITE_ENABLED=TRUE; alter session set query_REWRITE_INTEGRITY=TRUSTED; alter session set optimizer_mode = first_rows_10; set autotrace on select acquiRENTE_RAG_SOC from acquirenti where upper(ACQUIRENTE_RAG_SOC) like 'VERO%'; create index xie1acquirenti on acquirenti(upper(ACQUIRENTE_RAG_SOC)); select acquiRENTE_RAG_SOC from acquirenti where upper(ACQUIRENTE_RAG_SOC) like 'VERO%'; set autotrace off what is the output? In every case -- i get an index range scan on the second query. >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) -- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp