Oracle-L: Function-Based Index not working

From: Marul Mehta <marul_at_zycus.com>
Date: Sat, 31 Aug 2002 00:48:18 -0800
Message-ID: <F001.004C4E08.20020831004818@fatcity.com>

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.

SQL> create table employees (last_name varchar2(20)); Table created.
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.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Marul Mehta
INET: marul_at_zycus.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Sat Aug 31 2002 - 03:48:18 CDT