Re: Indexes and ROWNUM.

From: Kristian Myllymäki <kristian_at_kmja.com>
Date: Wed, 7 Apr 2004 22:09:44 +0200
Message-ID: <neZcc.7884$EV2.68148@amstwist00>

Why don't you do a simple test?

But no, the rownum clause would not change the access path from an index range scan to a full table scan. If the update uses the index without the rownum clause it would still use the index but use a COUNT (STOPKEY) to stop the range scan when it has found the given number of index matches.

The only possible change to a full table scan that I could think of would be if you used WHERE FLD1 IS NULL.

/Kristian

"Daniel" <danny.icha_at_usa.net> wrote in message news:96eef697.0404071144.1722bbac_at_posting.google.com...
> Hi...
> Simple question....
>
> I wanted to know if I use the ROWNUM in the WHERE clause it disables the
> indexed.
> For example: (In the case I have many records with FLD1 = 1)
> UPDATE MYTABLE SET FLD2 = 'HELLO WORD' WHERE FLD1 = 1 AND ROWNUM < 2;
> If the table has an index, the UPDATE will do a FullScan or it will use
> the index if there is any!?!?
>
> Thanks in advance...
>
> Daniel
Received on Wed Apr 07 2004 - 15:09:44 CDT