Re: Function Based Index

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 14 Nov 1999 21:05:24 -0500
Message-ID: <02kvOE0HgqJIyQQKbzuu1Q0o6Qul@4ax.com>

A copy of this was sent to Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de> (if that email address didn't require changing) On Mon, 15 Nov 1999 01:28:42 +0200, you wrote:

please try to post in text- not html. most "real" newsreaders don't run in a browser.

The issue is a recursive dependency between the function test_func and the table you are indexing... the following does not work because the function is dependent on the table.....

tkyte_at_ORACLE> create table test_tab (tststr varchar2 (30)); Table created.

tkyte_at_ORACLE> create or replace function test_func (   2 s in test_tab.tststr%type
  3 ) return test_tab.tststr%type deterministic is   4 begin
  5 return upper (trim (s));
  6 end test_func;
  7 /
Function created.

And then this index on that table would be dependent on the function...

tkyte_at_ORACLE> create index test_tab_test_func_1b 2 on test_tab (substr (test_func (tststr), 1, 30)); on test_tab (substr (test_func (tststr), 1, 30))

ERROR at line 2:
ORA-04020: deadlock detected while trying to lock object TKYTE.TEST_TAB

we break the dependency:

tkyte_at_ORACLE> create or replace function test_func ( s in varchar2) return varchar2
  2 deterministic
  3 is
  4 begin
  5 return upper (trim (s));
  6 end test_func;
  7 /

Function created.

tkyte_at_ORACLE> create index test_tab_test_func_1b 2 on test_tab (substr (test_func (tststr), 1, 30));

Index created.

and it works.

><!doctype html public "-//w3c//dtd html 4.0 transitional//en">
><html>
>Hi all,
>I have a strange behaviour with function based indexes in Oracle 8i
>on NT 4.0. I want to use a function-based index on a user function. It
>works nicely with internal functions. Here is what I get:
>SQL>
> SQL> drop table test_tab;
>Table dropped.
>SQL> create table test_tab (tststr varchar2 (30));
>Table created.
>SQL>
> SQL> create or replace function test_func (
>   2        s in test_tab.tststr%type
>   3  ) return test_tab.tststr%type deterministic is
>   4  begin
>   5        return upper (trim
>(s));
>   6  end test_func;
>   7  /
>Function created.
>SQL> show errors
> No errors.
> SQL>
> SQL> describe test_func
> FUNCTION test_func RETURNS VARCHAR2(30)
>  Argument Name                 
>Type                   
>In/Out Default?
>  ------------------------------ ----------------------- ------
>--------
>  S                             
>VARCHAR2(30)           
>IN
>SQL>
> SQL> -- We know that test_tab_test_func_1a is not working according
>to the docs, but we try anyhow. But we get ORA-01450.
> SQL>
> SQL> create index test_tab_test_func_1a
>   2  on test_tab (test_func (tststr));
> on test_tab (test_func (tststr))
>                        
>*
> ERROR at line 2:
> ORA-01450: maximum key length (3218) exceeded
>  
>SQL>
> SQL> -- According to the Oracle8i examples test_tab_test_func_1b should
>work. But we get ORA-04020.
> SQL>
> SQL> create index test_tab_test_func_1b
>   2  on test_tab (substr (test_func (tststr), 1, 30));
> on test_tab (substr (test_func (tststr), 1, 30))
>                                            
>*
> ERROR at line 2:
> ORA-04020: deadlock detected while trying to lock object
> TEST_MATCH.TEST_TAB
>  
>SQL>
> SQL> -- Surprise, surprise - test_tab_test_func_2 works although the
>function is merely the same.
> SQL>
> SQL> create index test_tab_test_func_2
>   2  on test_tab (upper (trim (tststr)));
>Index created.
>SQL>
> SQL> spool off
>Can somebody please shed some light on this? I appreciate any help.
>Martin</html>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Nov 14 1999 - 20:05:24 CST