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" <tkyte_at_us.oracle.com> wrote in message
news:9v6a6r02drd_at_drn.newsguy.com...
> In article <3c165eb1$0$561$afc38c87_at_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_at_ORA817DEV.US.ORACLE.COM> alter session set
QUERY_REWRITE_ENABLED=TRUE
> 2 /
>
> Session altered.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set
> QUERY_REWRITE_INTEGRITY=TRUSTED
> 2 /
>
> Session altered.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop table emp;
>
> Table dropped.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table emp as select * from
scott.emp;
>
> Table created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> update emp set ename = initcap(ename);
>
> 14 rows updated.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> commit;
>
> Commit complete.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create index emp_upper_idx on
> emp(upper(ename));
>
> Index created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec dbms_stats.set_table_stats( user,
'EMP',
> numrows => 10000, numblks => 100 );
>
> PL/SQL procedure successfully completed.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace on explain
> ops$tkyte_at_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_at_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_at_ORA817DEV.US.ORACLE.COM> create table t ( x int not null );
>
> Table created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_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_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( -1 );
>
> 1 row created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( -1 );
>
> 1 row created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( +1 );
>
> 1 row created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( +1 );
>
> 1 row created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create index t_idx on t(foo(x));
>
> Index created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> analyze index t_idx validate structure;
>
> Index analyzed.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select LF_ROWS from index_stats;
>
> LF_ROWS
> ----------
> 2
>
> ops$tkyte_at_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" <kennedy-family_at_attbi.com> wrote in message
> >news:blqR7.32893$ER5.374350_at_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" <c.veronesi_at_crpa.it> wrote in message
> >> news:3C162538.F77972CB_at_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
>
Received on Wed Dec 12 2001 - 02:42:02 CST
