Oracle-L: RE: Function-Based Index not working

From: Seefelt, Beth <Beth.Seefelt_at_TetleyUSA.com>
Date: Sat, 31 Aug 2002 05:28:18 -0800
Message-ID: <F001.004C4F95.20020831052818@fatcity.com>

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-----
Sent: Saturday, August 31, 2002 4:48 AM
To: Multiple recipients of list ORACLE-L

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: Seefelt, Beth
INET: Beth.Seefelt_at_TetleyUSA.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 - 08:28:18 CDT