Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g7VEl8724012 for ; Sat, 31 Aug 2002 10:47:08 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id FAA43174; Sat, 31 Aug 2002 05:45:52 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004C4F95; Sat, 31 Aug 2002 05:28:18 -0800 Message-ID: Date: Sat, 31 Aug 2002 05:28:18 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Seefelt, Beth" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Seefelt, Beth" Subject: RE: Function-Based Index not working Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C250E9.3CCDB438" ------_=_NextPart_001_01C250E9.3CCDB438 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =20 Hi, =20 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. =20 HTH, =20 Beth =20 =20 -----Original Message----- Sent: Saturday, August 31, 2002 4:48 AM To: Multiple recipients of list ORACLE-L Hi, =20 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. =20 1. SQL> create table employees (last_name varchar2(20)); Table created. =20 2. SQL> CREATE INDEX upper_ix ON employees (UPPER(last_name)); Index created. =20 Made the autotrace on and than:- =20 3. SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL = ORDER BY UPPER(last_name); no rows selected. =20 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE 1 0 SORT (ORDER BY) 2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' =20 =20 I fired without order by clause also but no use. =20 Now can any body please let tell me why this Oracle is having a full = scan of the employee table. =20 TIA, Marul. =20 =20 =20 ------_=_NextPart_001_01C250E9.3CCDB438 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,

Try=20 running the query with a hint that forces the index. If it still = doesn't=20 use the index, then you missed one of the steps needed to enable = function-based=20 indexes. If it does use the index, then you've done everything = right, but=20 the optimizer is deciding the fts is a better = option.

HTH,

Beth

-----Original Message-----
From: Marul Mehta=20 [mailto:marul@zycus.com]
Sent: Saturday, August 31, 2002 4:48=20 AM
To: Multiple recipients of list ORACLE-L
Subject: = Function-Based Index not working

Hi,

Can you please help me out in solving = this weird=20 problem of funcation-based index not being used when I query the=20 table.

This is the comand I fired and the = result it=20 returned me.

1. SQL> create table employees = (last_name=20 varchar2(20));

Table = created.

2. SQL> CREATE INDEX upper_ix ON = employees=20 (UPPER(last_name));

Index = created.

Made the autotrace on and = than:-

3. SELECT last_name FROM employees = WHERE=20 UPPER(last_name) IS NOT NULL ORDER BY = UPPER(last_name);

no rows = selected.

Execution=20 Plan
----------------------------------------------------------
&nb= sp; =20 0      SELECT STATEMENT=20 Optimizer=3DCHOOSE
   1    0   = SORT (ORDER=20 BY)
   2    1     TABLE = ACCESS=20 (FULL) OF 'EMPLOYEES'

I fired without order by clause also = but no=20 use.

Now can any body please let tell me why = this Oracle=20 is having a full scan of the employee table.

TIA,

Marul.

------_=_NextPart_001_01C250E9.3CCDB438-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: Beth.Seefelt@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@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).