Oracle Database Discussions

I cannot seem to enable function-based indexes, anyone can help here ?

Oracle 8.1.7 (latest patch)

I have a table 63K rows big.

trying to index on either lower() or upper() of a column named 'name', varchar2(50).

no matter what I do, the search by the function is slow and the explain plan shows a full scan.

the user has a DBA role, and I even added the system privilege QUERY_REWRITE to no avail.

here are the statements I used:

CREATE INDEX PRODUCT3_IDX on product(UPPER(name));

select * from product where UPPER(name) = 'ABC.COM';
(NO ROWS, SLOW RESPONSE)

explain plan set statement_id = 'JK' for
select * from product where UPPER(name) = 'ABC.COM';
PLAN
------------------------------------------------------------------------------------
1354 SELECT STATEMENT . CHOOSE Cost = 1354 Rows Expected = 640
1 TABLE ACCESS FULL CUSTOMER.PRODUCT ANALYZED Cost = 1354 Rows Expected = 640