Re: Weird behavior of the table with unique index

In article <36BE1882.E52FBE9_at_uptick.com>,   Alla Gribov <agribov_at_uptick.com> 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.
>
>

Most likely COLUMN1 or/and COLUMN2 datatype is different from lv_column1 or/and lv_column2 datatype and requires COLUMN1 or/and COLUMN2 conversion. Since conversion is expression against COLUMN1 or/and COLUMN2 and Oracle does not use index on expressions you end up with full scan,

Solomon Yakobson.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Feb 09 1999 - 00:00:00 CET