Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Need help with cost based optimizer "logic"
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 DBAReceived on Tue Apr 12 2005 - 13:23:11 CDT