Will I always get same 100 rows when I do rownum < 100?
361137Jan 4 2012 — edited Jan 4 2012If 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?