RE: optimizer not using function based indexes

From: <Rachel_Carmichael_at_Sonymusic.com>
Date: Fri, 10 May 2002 05:23:19 -0800
Message-ID: <F001.0045E1F9.20020510052319@fatcity.com>

And I respectfully agree that I was wrong :) It happens, I think I already posted a note to the list apologizing and correcting my error.

This is what happens when I post from memory without double checking.... obviously I had done a "flush" of my shared_pool!

Rachel

|--------+----------------------->
|        |                       |
|        |                       |
|        |          MGogala_at_oxhp.|
|        |          com          |
|        |                       |
|        |          05/09/2002   |
|        |          05:43 PM     |
|        |          Please       |
|        |          respond to   |
|        |          ORACLE-L     |
|        |                       |
|--------+----------------------->
  >----------------------------------------------------|
  |                                                    |
  |       To:     ORACLE-L_at_fatcity.com                 |
  |       cc:     (bcc: Rachel Carmichael)             |
  |       Subject:     RE: optimizer not using function|
  |       based indexes                                |
  >----------------------------------------------------|

I respectfully disagree. In order to use a function based index you do need the upper. If optimizer doesn't pick it up straigt away, he should give a hand with a hint (/*+ INDEX(alias,index) */)

> -----Original Message-----
> From: Rachel_Carmichael_at_Sonymusic.com
> [mailto:Rachel_Carmichael_at_Sonymusic.com]
> Sent: Thursday, May 09, 2002 4:49 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: optimizer not using function based indexes

>
>
>
>

> you don't need the upper(ename) in your where clause... that
> forces oracle to
> NOT use an index

>
>
>
>


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

> |        |                             |

> |        |                             |

> |        |          Harvinder.Singh_at_met|

> |        |          ratech.com         |

> |        |                             |

> |        |          05/09/2002 04:18 PM|

> |        |          Please respond to  |

> |        |          ORACLE-L           |

> |        |                             |

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

>   >----------------------------------------------------|

>   |                                                    |

>   |       To:     ORACLE-L_at_fatcity.com                 |

>   |       cc:     (bcc: Rachel Carmichael)             |

>   |       Subject:     optimizer not using function    |

>   |       based indexes                                |

>   >----------------------------------------------------|

>
>
>
>

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

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

Received on Fri May 10 2002 - 08:23:19 CDT