RE: Function-Based Index not working

[Seefelt, Beth]

Hi,   Try running the query with a hint that forces the index.  If it still doesn't use the index, then you missed one of the steps needed to enable function-based indexes.  If it does use the index, then you've done everything right, but the optimizer is deciding the fts is a better option.   HTH,   Beth     -----Original Message----- From: Marul Mehta [mailto:[EMAIL PROTECTED]] Sent: Saturday, August 31, 2002 4:48 AM To: Multiple recipients of list ORACLE-L Subject: Function-Based Index not working Hi,   Can you please help me out in solving this weird problem of funcation-based index not being used when I query the table. This is the comand I fired and the result it returned me.   1. SQL> create table employees  (last_name varchar2(20));     Table created.   2. SQL> CREATE INDEX upper_ix ON employees (UPPER(last_name));     Index created.   Made the autotrace on and than:-   3. SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);     no rows selected.   Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE    1    0   SORT (ORDER BY)    2    1     TABLE ACCESS (FULL) OF 'EMPLOYEES'     I fired without order by clause also but no use.   Now can any body please let tell me why this Oracle is having a full scan of the employee table.   TIA, Marul.