Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need help with cost based optimizer "logic"

Re: Need help with cost based optimizer "logic"

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 12 Apr 2005 20:23:11 +0200
Message-ID: <o54o51p83s83a7ern8rsvm4llimql14s0s@4ax.com>


On Tue, 12 Apr 2005 12:42:46 -0400, "Syltrem" <syltremzulu_at_videotron.ca> wrote:

>As opposed to here where it cannot ignore the UPPER as '1/4%' really is
>"something" ?
>

No.
Basically if you do this
<function>(<column name>) = <constant>
you are comparing something with multiple possible results to a constant.
If you however do this
<column name> = <function>(<expression>) the function will have only one result, as the expression is constant, and the index can be used.
In the other case as the outcome of <function>(<variable from table>) is unknown the index won't be used.

General rule: whenever you put a function around an indexed column, you force Oracle NOT to use the index.
This is true from (likely) the very first version of Oracle, and applies both to RBO AND CBO!!!
>If this is so, that would explain why it would try the index scan to find
>"anything", then only keep the ones that match "something".
>But is it really that smart as to have special logic for LIKE '%' ?

Yes. It is called 'INDEX RANGE SCAN'

--
Sybrand Bakker, Senior Oracle DBA
Received on Tue Apr 12 2005 - 13:23:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US