Re: Weird behavior of the table with unique index
Date: 1999/02/09
Message-ID: <79o2kv$si$1_at_nnrp1.dejanews.com>#1/1
In article <36d64368.24863081_at_192.86.155.100>,
tkyte_at_us.oracle.com wrote:
> A copy of this was sent to Alla Gribov <agribov_at_uptick.com>
> (if that email address didn't require changing)
> On Sun, 07 Feb 1999 17:49:38 -0500, you 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
> >
Since your Primary Key is in this order (COLUMN1, COLUMN2)
Can you try SELECT * FROM TABLE
WHERE COLUMN1 = lv_column1
AND COLUMN2 = lv_column2.
As what I know the order of specifying Where clause is important.
> >Explain plan shows me "Full table scan" instead of "Unique index scan".
> >
>
> what are lv_column2 and lv_column1? are they columns of the same TABLE? if
so,
> an index could never be used as lv_column1 and lv_column2 are not constants.
> Every row would have to be inspected to see if that rows column1, column2 =
> lv_column1, lv_column2 for that row.
>
> Need more info it lv_column1 and lv_column2 are not in the same table as the
> above query would indicate they are....
>
> >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.
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
>
-----------== 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