CODEWITHSUNDEEP
oracle-databaseplsql
Gabriel Anderson
Gabriel Anderson

Reputation: 1391

Why Oracle changes rowid with fetch?

I have a query like this:

select w.rowid, w.waclogin
  from tableA w, tableB wa, tableC a
 where wa.alucod = a.alucod
   and w.waclogin = wa.waclogin
   and a.cpf = '31808013875'
   and rownum <= 1;

The results are:

ROWID               WACLOGIN
AAA0CEAHSAABE07ABA  31808013875

But when I use fetch (for performance) the rowid returned is different:

select w.rowid, w.waclogin
  from tableA w, tableB wa, tableC a
 where wa.alucod = a.alucod
   and w.waclogin = wa.waclogin
   and a.cpf = '31808013875'
 fetch first row only;

Results in:

ROWID               WACLOGIN
AAA0DMAHaAAA+ZcAAX  31808013875

Why fetch changes the rowid? For me this no makes sense.

Update

When fetch is used, that row id returned is from table B, instead of table A.

row id from 3 tables

Upvotes: 0

Views: 397

Answers (1)

Justin Cave
Justin Cave

Reputation: 231681

There are two rows in tableA with the same wacLogin value (but obviously different rowID values). Neither of your queries specifies an order by so which of those rows is returned is arbitrary. Presumably, there is a slightly different query plan being used for both queries so each one returns a different arbitrary row. Of course, tomorrow, either or both queries could start returning a different arbitrary row if the query plan or physical organization of the table changes. If you want the same row to be returned in both cases, you'd need to make both queries deterministic with an order by clause that uniquely orders the results.

Upvotes: 5

Related Questions