| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Function based index not being used
It looks like my post got a little messed up. That'll teach me to
preview first. Here is another attempt, since I don't see an option to
edit or delete. If this doesn't come out right, I'll just leave it at
that and hope you can decipher it.
Bruce,
I just noticed that your function is returning a raw datatype. I ran into this problem once before. I forgot that, when you are creating a function-based index on a function that returns a raw datatype, you must constrain the return value using substr, in order for the index to be used. Please see the revised demonstration below, in which I used a function that returns a raw datatype and two indexes, one without substr and one with substr. The one without substr does not use the index, but the one with substr in both the index creation and query does use the index.
scott_at_ORA92> CREATE USER otherUser IDENTIFIED BY otherUserPassword 2 /
User created.
scott_at_ORA92> GRANT CONNECT, RESOURCE TO otherUser 2 /
Grant succeeded.
scott_at_ORA92> CONNECT otherUser/otherUserPassword
Connected.
scott_at_ORA92> @ LOGIN
scott_at_ORA92> SET ECHO OFF
GLOBAL_NAME
otheruser_at_ORA92> create or replace function myFunction( p_str in
varchar2 )
2 return raw deterministic
3 as
4 l_data varchar2(255);
5 begin
6 l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
7 return dbms_obfuscation_toolkit.DESEncrypt
8 ( input => utl_raw.cast_to_raw(l_data),
9 key => utl_raw.cast_to_raw('MagicKey') );
10 end;
Function created.
otheruser_at_ORA92> SHOW ERRORS
No errors.
otheruser_at_ORA92> CONNECT scott/tiger
Connected.
otheruser_at_ORA92> @ LOGIN
otheruser_at_ORA92> SET ECHO OFF
GLOBAL_NAME
scott_at_ORA92> create table indxtest as select * from all_objects where
rownum <= 1000
2 /
Table created.
scott_at_ORA92> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE 2 /
Session altered.
scott_at_ORA92> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED 2 /
Session altered.
scott_at_ORA92> create index indxtest_func_idx1 2 on indxtest (otherUser.myFunction (object_name)) 3 /
Index created.
scott_at_ORA92> create index indxtest_func_idx2 2 on indxtest (substr (otherUser.myFunction (object_name), 1, 2000)) 3 /
Index created.
scott_at_ORA92> EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'INDXTEST') PL/SQL procedure successfully completed.
scott_at_ORA92> SET AUTOTRACE ON EXPLAIN
scott_at_ORA92> select *
2 from indxtest
3 where otherUser.myFunction (object_name) = 'aa'
4 /
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=10 Bytes=1280) 1 0 TABLE ACCESS (FULL) OF 'INDXTEST' (Cost=3 Card=10 Bytes=1280)
scott_at_ORA92> select *
2 from indxtest
3 where substr (otherUser.myFunction (object_name), 1, 2000) = 'aa'
4 /
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=128) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'INDXTEST' (Cost=2 Card=1 Bytes=128)
2 1 INDEX (RANGE SCAN) OF 'INDXTEST_FUNC_IDX2' (NON-UNIQUE) (Cost=1 Card=1)
scott_at_ORA92> Received on Fri Jul 08 2005 - 20:46:36 CDT
 |
 |