Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Will I always get same 100 rows when I do rownum < 100?

361137Jan 4 2012 — edited Jan 4 2012
If I have a query that pulls from a table and does where rownum < 100, will it get the same 100 rows as a second query doing the same thing, or is that a dangerous assumption?

Example...

TABLE TEST (
TEST_ID NUMBER [PK],
COL1 NUMBER,
PROCESSED_FLAG CHAR(1) DEFAULT 'N'
)

TABLE TEST2 (
TEST2_ID NUMBER [PK],
COL1 NUMBER
)

INSERT INTO TEST2 (COL1)
SELECT TEST2_SEQUENCE.NEXTVAL, COL1 FROM TEST WHERE ROWNUM < 100;

UPDATE TEST
SET PROCESSED_FLAG = 'Y'
WHERE TEST_ID IN (SELECT TEST_ID FROM TEST WHERE ROWNUM < 100);

----------------

Will that second update always update the same 100 rows that were inserted by the first query?

Or if I need to order them to ensure that, how would I best go about doing that without killing performance?

Does the hint /* FIRST_ROWS */ help here?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 1 2012
Added on Jan 4 2012
8 comments
871 views