If CBO think - cost of FTS will be less than index scan then it will not use index.
HTH. -----Original Message----- From: Marul Mehta [mailto:[EMAIL PROTECTED]] Sent: Sat 8/31/2002 11:48 To: Multiple recipients of list ORACLE-L Cc: 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.
<<winmail.dat>>
