Path: news.easynews.com!easynews!crtntx1-snh1.gtei.net!cpk-news-hub1.bbnplanet.com!news.gtei.net!newscon02.news.prodigy.com!prodigy.com!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 05:46:04 -0800 Organization: Oracle Lines: 188 Message-ID: <9v7n2s022d8@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> NNTP-Posting-Host: p-436.newsdawg.com X-Newsreader: Direct Read News 2.91 Xref: easynews comp.databases.oracle.server:127950 X-Received-Date: Wed, 12 Dec 2001 07:06:46 MST (news.easynews.com) In article <3C172BD9.547FF1C2@crpa.it>, Cristian says... > >Niall Litchfield wrote: > >> how many rows does your query return? > >5 rows from 10904 > >> how many blocks in the table? > >64 > >> have you analyzed the table (sorry can't find if you've posted this inf= >o or >> not). > >Yes, I did: > >SQL> analyze table acquirenti compute statistics ; > >Table analyzed. > >> if this is performance critical - I read your posts as just investigati= >on - >> you could of course hint the statement. > >I tried, with the same result: > >SQL> explain plan for > 2 select * from acquirenti /*+ INDEX(acquirenti xie1acquirenti) */ > 3 where UPPER(ACQUIRENTE_RAG_SOC) like 'VERO%'; > >Explained. > >SQL> select * from plan_view ; > >OPERATION OPTIONS OBJECT_NAME POSITION >-------------------- -------------------- -------------------- -------- >SELECT STATEMENT 10 > TABLE ACCESS FULL ACQUIRENTI 1 > read the posting at: http://osi.oracle.com/~tkyte/article1/index.html it'll help you get your example going. your index hint isn't a hint, its in the wrong place. select /*+ INDEX(acquirenti xie1acquirenti) */ * from acquirenti where UPPER(ACQUIRENTE_RAG_SOC) like 'VERO%'; using ALL_ROWS will cause the CBO to skip indexes EASILY. ALL_ROWS will favor full scans over index accesses. Here is a full example showing this in action (with no hints, nothing). I would urge you to use autotrace (that link above shows how to set it up). Its much easier and less error prone then doing the explain plan yourself. ops$tkyte@ORA817DEV.US.ORACLE.COM> create table acquirenti ( ACQUIRENTE_RAG_SOC varchar2(30), 2 data char(500) default 'x' ); Table created. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into acquirenti 2 select object_name, 'x' from all_objects where rownum < 10900; 10899 rows created. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into acquirenti values ( 'vero1', 'x' ); 1 row created. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into acquirenti values ( 'vero2', 'x' ); 1 row created. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into acquirenti values ( 'vero3', 'x' ); 1 row created. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into acquirenti values ( 'vero4', 'x' ); 1 row created. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into acquirenti values ( 'vero5', 'x' ); 1 row created. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> create index xie1acquirenti on acquirenti(upper(ACQUIRENTE_RAG_SOC)); Index created. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table acquirenti compute statistics; Table analyzed. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set QUERY_REWRITE_ENABLED=TRUE; Session altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED; Session altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> select ACQUIRENTE_RAG_SOC, substr(data,1,20) from acquirenti 2 where upper(ACQUIRENTE_RAG_SOC) like 'VERO%'; ACQUIRENTE_RAG_SOC SUBSTR(DATA,1,20) ------------------------------ -------------------- vero1 x vero2 x vero3 x vero4 x vero5 x Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=1048) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ACQUIRENTI' (Cost=3 Card=2 Bytes=1048) 2 1 INDEX (RANGE SCAN) OF 'XIE1ACQUIRENTI' (NON-UNIQUE) (Cost=2 Card=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 634 bytes sent via SQL*Net to client 430 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 5 rows processed >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