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 g49JFii24304 for ; Thu, 9 May 2002 15:15:44 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id MAA37972; Thu, 9 May 2002 12:24:30 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 0045D821; Thu, 09 May 2002 12:18:55 -0800 Message-ID: Date: Thu, 09 May 2002 12:18:55 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Harvinder Singh" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Harvinder Singh" Subject: optimizer not using function based indexes Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by naude.co.za id g49JFii24304 Hi, We are trying to use function based indexes in our application but it looks like optimizer is not using them. So we created a sample table emp with 3 columns empno,ename,sal and populate this table with 100000 rows and created function based index on upper(ename). we try to run simple query like below and optimzer is not using index: select sal from emp where upper(ename)=upper('abc98'); Even we use index hint optimizer is still not using index..... 1) table is analyzed as compute after creating index. 2) query rewrite is granted to user 3) query_rewrite_enabled=true. 4) optimzer mode is choose. 5) optimer should use index as there is no way full scan can be faster than index in this case as i tried to create simple index on ename and remove upper function from where clause of query.. What can be the possible reason that optimzer is not using index?????? Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: Harvinder.Singh@MetraTech.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).