Re: Function-Based Index not working

[Steve Perry]

analyze table employees  compute statistics   you may need to enable query rewrite too.   ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE; ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; steve ----- Original Message ----- From: Marul Mehta To: Multiple recipients of list ORACLE-L Sent: Saturday, August 31, 2002 3:48 AM 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.