RE: optimizer not using function based indexes

and stats have to have been collected on the index and base table as well

just checked the 9i docs... I was wrong you do need the upper on the column, my bad.. but you need the stats, the compatibility set (as Beth says)

|--------+-------------------------->


|        |                          |

|        |                          |

|        |          Beth.Seefelt_at_tet|

|        |          leyusa.com      |

|        |                          |

|        |          05/09/2002 05:24|

|        |          PM              |

|        |          Please respond  |

|        |          to ORACLE-L     |

|        |                          |

|--------+-------------------------->
  >----------------------------------------------------|
  |                                                    |
  |       To:     ORACLE-L_at_fatcity.com                 |
  |       cc:     (bcc: Rachel Carmichael)             |
  |       Subject:     RE: optimizer not using function|
  |       based indexes                                |
  >----------------------------------------------------|

Another requirement is that your compatibility init.ora parameter must be set to 8.1.7 or higher, I believe, but check the doco.

If that doesn't fix it, then trying using a hint to force the use of that index. If it doesn't work with the hint, then you know you have an environment problem that is preventing the use of function-based indexes. If it does work with the hint, then you know that function based is working but you have to figure out why the optimizer is not choosing it.

HTH, Beth

-----Original Message-----
Sent: Thursday, May 09, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L

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




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rachel_Carmichael_at_Sonymusic.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).