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

From: Steve Perry <sperry_at_sprynet.com>
Date: Sat, 31 Aug 2002 07:03:19 -0800
Message-ID: <F001.004C5031.20020831070319@fatcity.com>

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.

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: Steve Perry
INET: sperry_at_sprynet.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 - 10:03:19 CDT