RE: Function-Based Index not working

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:marul_at_zycus.com]
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.     

Content-Type: text/plain; name="ReadMe.txt"; charset="us-ascii" Content-Transfer-Encoding: 7bit

The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification.

------_=_NextPart_001_01C250CE.6B165B8E--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sandeep Kurliye
  INET: Sandeep_at_almoayyedintl.com.bh

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).



application/ms-tnef attachment: winmail.dat