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) */