Re: Weird behavior of the table with unique index

If you are coming from forms (from the lv_), then my first thought is that the lv_x fields are different types, such as column1 being a date and lv_column1 being char. When Oracle does the automatic conversion, it will do it on the table field, disabling indexes. If this is true (the different types), try forcing the conversion such as 'column1=to_date(lv_column1)'

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.
>
>

Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.

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