Function Based Index

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Mon, 15 Nov 1999 01:28:42 +0200
Message-ID: <382F45A9.9EAD31F8@0800-einwahl.de>

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 Received on Sun Nov 14 1999 - 17:28:42 CST