| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: how to use function-based indexes?
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 whereupper(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;
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 CorpReceived on Tue Dec 11 2001 - 19:00:11 CST
 |
 |