Oracle order by undetermined

I'm trying to add pagination to a simple select query, like

select rownum rownum_,t.* from (select id,name from table) t
where rownum <=4

However the above query behaves differently when where condition is开发者_高级运维 there and where condition is not there. But on removing the name column, the behaviour is consistent. Can anyone tell why?

I understand rownum is determined when query is run, what I'm not able to understand is that a where condition with rownum <=n , changes the order

---

You have to understand that rownum is a pseudo column.

The rownum column in your query indicates the order ( first record, second, third... )

Then the records order returned by

select id,name from table

is not guaranteed. In your request, the rownum associated to the first record will be always 1 despict the fact that the first record might be different every time.

In conclusion, always use ORDER BY when you want a specific order.

---

The table might have an index on rownum that it's using when you restrict with rownum <= 4. The order will then likely be defined by the index. This is the way it works in MySQL at least. If you want a specific ordering, always use ORDER BY.

---

When you exclude the name column from your query, the id's values are fetched from an index, not from table, so you get another order.

The second issue I bet is because the Optimizer uses parallel for getting more rows. Parallel threads allways return a 'mess order' because they retrieve data in parallel not sequential. Try with a hint /*+ NO_PARALLEL(table) */