CODEWITHSUNDEEP
sqloracle-databaseindexing
Thomas
Thomas

Reputation: 366

Oracle 11gR2 Function based index error

I want to create a simple function based index on a simple table but i get error. So, first of all I created a function 

CREATE OR REPLACE FUNCTION promo_function(p_promo_category VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
  RETURN UPPER(p_promo_category);
END promo_function;

Then I would execute this, but fails

CREATE INDEX promotions_fbi
          ON SH.PROMOTIONS (promo_function (promo_category));

Why? The error is ORA-00904:"PROMO_FUNCTION": Invalid identifier But the function works well in a query:

SELECT * 
  FROM sh.sales s,
       sh.promotions p,
       sh.times t
 WHERE s.promo_id = p.promo_id
   AND s.time_id = t.time_id
   AND t.time_id BETWEEN DATE '2000-01-01' AND DATE '2000-03-31'
   AND promo_function(p.promo_category) = 'AD NEWS';

Many thanks!

Upvotes: 1

Views: 793

Answers (2)

APC
APC

Reputation: 146239

There is nothing inherently wrong with your code. I can create a function-based index like this:

SQL> create table promotions (promo_category varchar2(10))
  2  /

Table created.

SQL> CREATE OR REPLACE FUNCTION promo_function
  2      (p_promo_category in VARCHAR2)
  3      RETURN VARCHAR2 DETERMINISTIC 
  4  IS
  5  BEGIN
  6      RETURN UPPER(p_promo_category);
  7  END promo_function;
  8  /

Function created.

SQL> CREATE INDEX promotions_fbi
  2        ON PROMOTIONS (promo_function (promo_category));

Index created.

SQL>

The only difference between my code and yours is that I don't prefix the table in the CREATE INDEX statement. Everything is in the same schema, so I don't need to.

So, can I re-create your scenario? Here's one way. I drop the index and function, then give another uses all privileges on teh table...

SQL> drop index promotions_fbi;

Index dropped.

SQL> drop function PROMO_FUNCTION;

Function dropped.

SQL> grant all on promotions to B;

Grant succeeded.

SQL>

As that user I can create a normal index ...

SQL> conn b/b
Connected.
SQL> select * from apc.promotions;

no rows selected

SQL> CREATE INDEX promotions_i
  2      ON APC.PROMOTIONS (promo_category);

Index created.

SQL>

However, if I create a function I cannot create a function-based index using it....

SQL> conn b/b
Connected.

SQL> CREATE INDEX promotions_fbi
  2        ON APC.PROMOTIONS (promo_function (promo_category));
      ON APC.PROMOTIONS (promo_function (promo_category))
                         *
ERROR at line 2:
ORA-00904: : invalid identifier

SQL>

The invalid identifier fingers the function name. Why? Because although schema B would own the index schema APC owns the table, and needs to be able to execute the function too. The solution is to grant execute rights on the function to the table owner:

SQL> conn b/b
Connected.
SQL> grant execute on promo_function to APC;

Grant succeeded.

SQL> CREATE INDEX promotions_fbi
   2   ON APC.PROMOTIONS (B.promo_function (promo_category));  
Index created.

SQL>

Note that we must explicitly reference the function owner as well as the table owner in this statement. It's a bit nasty, and that's why it's generally a bad idea to spread privileges across two schemas in this fashion.

Not sure how @zaratustra gets their findings, as I can definitely create function-based in indexes using the word FUNCTION in the name...

SQL> r
  1  select i.table_owner, i.owner as index_owner, i.index_name
  2         , i.index_type, e.column_expression
  3  from all_indexes i
  4       left join all_ind_expressions e
  5           on i.owner = e.index_owner
  6             and i.index_name = e.index_name
  7* where i.table_name = 'PROMOTIONS'

TABLE_OWNER                    INDEX_OWNER
------------------------------ ------------------------------
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
COLUMN_EXPRESSION
--------------------------------------------------------------------------------
APC                            APC
PROMOTIONS_FBI                 FUNCTION-BASED NORMAL
"APC"."PROMO_FUNCTION"("PROMO_CATEGORY")

APC                            A
PROMO_B_I                      FUNCTION-BASED NORMAL
"A"."B_FUNCTION"("PROMO_CATEGORY")

APC                            APC
PROMOTIONS_I                   NORMAL



SQL>

Although I am on a different point release so that might explain it

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

SQL>

Upvotes: 3

neshkeev
neshkeev

Reputation: 6476

During my experiments I realized that Oracle can't let you create function-based index with the function word in the name of the procedure. Looks like a bug for me:

CREATE OR REPLACE FUNCTION func_function(p_promo_category IN VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
  RETURN UPPER(p_promo_category);
END func_function;

create table t1 (
  promo_category varchar2(4000)
);

Table created

CREATE INDEX promotions_fbi ON t1 (func_function (promo_category));

ORA-00911: invalid character

Let's create a function without the function word in the name:

CREATE OR REPLACE FUNCTION func_functio(p_promo_category IN VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
  RETURN UPPER(p_promo_category);
END func_functio;

CREATE INDEX promotions_fbi ON t1 (func_functio (promo_category));

Index created

select index_name, index_type 
  from user_indexes 
 where lower(index_name) = 'promotions_fbi'

INDEX_NAME      INDEX_TYPE
-------------------------------------
PROMOTIONS_FBI  FUNCTION-BASED NORMAL

My Oracle version:

select banner from v$version

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Upvotes: 0

Related Questions