Xref: alice comp.databases.oracle.server:73946 Path: alice!news-feed.fnsi.net!hammer.uoregon.edu!logbridge.uoregon.edu!su-news-hub1.bbnplanet.com!paloalto-snf1.gtei.net!news.gtei.net!inet16.us.oracle.com!not-for-mail From: Thomas Kyte Newsgroups: comp.databases.oracle.server Subject: Re: Function Based Index Date: Sun, 14 Nov 1999 21:05:24 -0500 Organization: Oracle Service Industries Lines: 135 Message-ID: <02kvOE0HgqJIyQQKbzuu1Q0o6Qul@4ax.com> References: <382F45A9.9EAD31F8@0800-einwahl.de> Reply-To: tkyte@us.oracle.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: inet16.us.oracle.com 942631443 4639 130.35.30.53 (15 Nov 1999 02:04:03 GMT) X-Complaints-To: usenet@inet16.us.oracle.com NNTP-Posting-Date: 15 Nov 1999 02:04:03 GMT X-Newsreader: Forte Agent 1.6/32.525 A copy of this was sent to Martin Haltmayer (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@ORACLE> create table test_tab (tststr varchar2 (30)); Table created. tkyte@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@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@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@ORACLE> create index test_tab_test_func_1b 2 on test_tab (substr (test_func (tststr), 1, 30)); Index created. and it works. > > >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 > -- 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@us.oracle.com Oracle Service Industries Reston, VA USA Opinions are mine and do not necessarily reflect those of Oracle Corporation