Re: Weird behavior of the table with unique index

check the data types of both columns
and the corresponding local variables.
If the column and local variable differ in type, then Oracle may do a conversion of the column to match the type of the local variable.

this is equivalent to:

where to_char(column1) = lv_variable1
or
where to_number(column1) = lv_variable1

if any type conversion gets done on the column then the use of the index is not possible. Be sure to do any type conversion yourself explicity on the local variable such that it matches the type of the db
column. (ie where column1 = to_number(lv_variable))

-jay

Alla Gribov wrote:

> Hello, all;
>
> I am having a weird problem with a simple SELECT statement, that I've
> never seen before.
>
> I have a table, that has two columns as a primary key (which is an
> unique index). Table is kind of big (around 300,000 rows).
>
> Let's say that primary key is (COLUMN1, COLUMN2).
>
> I am running "Explain plan" on a following statement
> SELECT * FROM TABLE
> WHERE COLUMN2 = lv_column2
> AND COLUMN1 = lv_column1
>
> Explain plan shows me "Full table scan" instead of "Unique index scan".
>
> Even if I put a hint on this unique index, I am getting "Full index
> scan"
>
> Any ideas?????????????????????
>
> Thanks for your help
>
> Alla
>
> P.S. Version of Oracle used is 7.3.3.
Received on Tue Feb 16 1999 - 00:00:00 CET