Re: Using indexes

On 24 Aug 2006 01:25:54 -0700, devjnr_at_gmail.com wrote:

>I have sql server backgrounds and I remember that (in a case
>insensitive environment) a query like this:
>
>where car = 'red' also select value like 'RED' or 'Red'
>
>In Oracle I'm seeing that it doesn't work this way.
>
>It can be simple to do something like this:
>
>where lower(car) = 'red'
>
>but I remember that this way in sql server indexes are not used, so a
>table scan is performed.
>
>What is the actual behavior of Oracle?
>
>Thx to all.

The behavior is version dependent.
In Oracle 9i there is no case insensitive sort. In Oracle 10g there is.

Independent of version any clause like
where <function>(<parameter>)=<constant> will NOT use an *ordinary* index.
It will use a Function Based Index, provided you create it first (and use the Cost Based Optimizer, and gather index stats on that index)

--
Sybrand Bakker, Senior Oracle DBA