Re: Weird behavior of the table with unique index

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 21:45:06 -0500, you wrote:

>Sorry, I did not make myself clear.
>
>lv_column1 and lv_column2 are local variables.
>
>Thanks for the prompt reply.
>
>Alla
>

how did you do the explain plan then? Can we see the original block of code and the explain plan command and all? I'm not sure what you mean by "local variables" in the context of an explain plan -- if they are not prefixed by a colon -- a bind variable -- there isn't really the concept of a 'local variable' in explain plan....

can you cut and paste the explain plan command you are using and the output thereof?

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