Path: news.easynews.com!easynews!news-out.visi.com!hermes.visi.com!news1.optus.net.au!optus!spool01.syd.optusnet.com.au!spool.optusnet.com.au!210.49.20.118.MISMATCH!not-for-mail From: "Howard J. Rogers" Newsgroups: comp.databases.oracle.server 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> Subject: Re: how to use function-based indexes? Date: Wed, 12 Dec 2001 19:42:02 +1100 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Lines: 275 Message-ID: <3c17180c$0$559$afc38c87@news.optusnet.com.au> NNTP-Posting-Host: 211.28.25.198 X-Trace: 1008146453 559 211.28.25.198 Xref: easynews comp.databases.oracle.server:127919 X-Received-Date: Wed, 12 Dec 2001 02:03:26 MST (news.easynews.com) I didn't say that function-based indexes had to be on nullable columns (or vice versa), nor that a function-based index can only reference all rows in a table. Merely that the optimizer's inability to determine whether a function was able to return a null *or not* drastically affected the likelihood of it choosing to use them, in versions priot to 9i, and that problem has been (allegedly) resolved in 9i. HJR -- ---------------------------------------------- Resources for Oracle: http://www.hjrdba.com =============================== "Thomas Kyte" wrote in message news:9v6a6r02drd@drn.newsguy.com... > In article <3c165eb1$0$561$afc38c87@news.optusnet.com.au>, "Howard says... > > > >No the functions don't have to match in case. This is function-based > >indexes were dealing with here, not stored outlines. An index built on > >emp(sum(sal)+avg(sal)+32) can even be used by the optimizer if the eventual > >select is for 32+sum(sal)+avg(sal)>53. It's aware of commutative > >mathematical operations, therefore. > > > >I suspect the problem here is in your first line of response. Since we are > >dealing with the optimizer, it's up to the optimizer to determine whether > >the use of the function-based index is of merit. That requires statistics > >on the table (and on the index, I'd suggest). > > also to use function based indexes -- we HAVE to use CBO. RBO doesn't "see" > them. I'd suspect here they are using rbo. > > > It helps if the column being > >searched for is declared NOT NULL, too -since it has always been tricky > >persuading the optimizer that a function-based index on a nullable column > >references all rows in the table, but they got that right in 9i. > > clarify -- i've NEVER had an issue -- the optimizer never cared AFAIK about the > nullality of the underlying column. do you have a provable test case > > > see > http://osi.oracle.com/~tkyte/article1/index.html > > it creates an index on the upper(ename) on the emp table, and Ename is nullable. > It uses the index straight away (my optimizer goal was first rows so I skipped > the analyze but you get similar results with choose). that was in 815, in 817 i > just tested (with choose) > > ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set QUERY_REWRITE_ENABLED=TRUE > 2 / > > Session altered. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set > QUERY_REWRITE_INTEGRITY=TRUSTED > 2 / > > Session altered. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table emp; > > Table dropped. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp as select * from scott.emp; > > Table created. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> update emp set ename = initcap(ename); > > 14 rows updated. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> commit; > > Commit complete. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> create index emp_upper_idx on > emp(upper(ename)); > > Index created. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'EMP', > numrows => 10000, numblks => 100 ); > > PL/SQL procedure successfully completed. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on explain > ops$tkyte@ORA817DEV.US.ORACLE.COM> select ename, empno, sal from emp where > upper(ename) = 'KING'; > > ENAME EMPNO SAL > ---------- ---------- ---------- > King 7839 5000 > > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=3300) > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=100 Bytes=3300) > 2 1 INDEX (RANGE SCAN) OF 'EMP_UPPER_IDX' (NON-UNIQUE) (Cost=1 Card=100 > > > ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off > > > > Also it is really wrong to say that a fbi index on ANY (nullable or otherwise) > column references all rows in the table!! I've used this to great advantage as > well. If your function returns null -- it'll not make it into the b*tree just > like anything else. You can use this to do selective indexing (eg: if the value > is less then zero in the table, it is -1 -- all positive values are to be > indexed, how can we index just positive values -- an FBI)... > > ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int not null ); > > Table created. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function foo( l_x in int ) > return int > 2 deterministic > 3 as > 4 begin > 5 if ( l_x < 0 ) then > 6 return null; > 7 else > 8 return l_x; > 9 end if; > 10 end; > 11 / > > Function created. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( -1 ); > > 1 row created. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( -1 ); > > 1 row created. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( +1 ); > > 1 row created. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( +1 ); > > 1 row created. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(foo(x)); > > Index created. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze index t_idx validate structure; > > Index analyzed. > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > ops$tkyte@ORA817DEV.US.ORACLE.COM> select LF_ROWS from index_stats; > > LF_ROWS > ---------- > 2 > > ops$tkyte@ORA817DEV.US.ORACLE.COM> > > > > > And if the > >thing being selected for represents more than about 5% of the rows in the > >table, the optimizer is unlikely to use an index at all, of whatever sort. > >And if there are only (say) 50 rows in the table to begin with, we're in for > >an efficient full table scan come what may. > > > >Regards > >HJR > >-- > >---------------------------------------------- > >Resources for Oracle: http://www.hjrdba.com > >=============================== > > > > > >"Jim Kennedy" wrote in message > >news:blqR7.32893$ER5.374350@rwcrnsc52... > >> How many rows are in the acquirenti table? (a full scan might be > >efficient.) > >> > >> Also when you defined the function based index you did: > >> ... ( UPPER(... )) > >> when you issed the query you did > >> ... upper(...) > >> I know this sounds silly but try in your select statement > >> ...UPPER(...) > >> I believe that the function and the select have to match even in case. > >> Again sounds silly, but that might be it. > >> Jim > >> "Cristian Veronesi" wrote in message > >> news:3C162538.F77972CB@crpa.it... > >> Niall Litchfield wrote: > >> > >> > Function based > >> > indexes are for cases where you are issuing queries like > >> > > >> > select * from acquirenti > >> > where upper(acquirente_rag_soc) like 'VERO%'; > >> > >> Sorry, in my second post I forgot to include the UPPER function in the > >> query. But the problem still remains: > >> > >> SQL> explain plan for > >> 2 select * from acquirenti > >> 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 > >> > >> Best regards, Cristian > >> > >> -- > >> Cristian Veronesi ><((((º> 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 >