Are the results deterministic, if I partition SQL SELECT query without ORDER BY?

Question

I have SQL SELECT query which returns a lot of rows, and I have to split it into several partitions. Ie, set max results to 10000 and iterate the rows calling the query select time with increasing first result (0, 10000, 20000). All the queries are done in same transaction, and data that my queries are fetching is not changing during the process (other data in those tables can change, though).

Is it ok to use just plain select:

select a from b where...

Or do I have to use order by with the select:

select a from b where ... order by c

In order to be sure that I will get all the rows? In other word, is it guaranteed that query without order by will always return the rows in the same order?

Adding order by to the query drops performance of the query dramatically.

I'm using Oracle, if that matters.

EDIT: Unfortunately I cannot take advantage of scrollable cursor.

Answer 1

No, without order by it is not guaranteed that query will ALWAYS return the rows in the same order.

Answer 2

Order is definitely not guaranteed without an order by clause, but whether or not your results will be deterministic (aside from the order) would depend on the where clause. For example, if you have a unique ID column and your where clause included a different filter range each time you access it, then you would have non-ordered deterministic results, i.e.:

select a from b where ID between 1 and 100
select a from b where ID between 101 and 200
select a from b where ID between 201 and 300

would all return distinct result sets, but order would not be any way guaranteed.

Answer 3

No guarantees unless you have an order by on the outermost query.

Bad SQL Server example, but same rules apply. Not guaranteed order even with inner qurey

SELECT
    *
FROM
    (
    SELECT
        *
    FROM
        Mytable
    ORDER BY SomeCol
    ) foo

Answer 4

Use Limit

So you would do:

SELECT * FROM table ORDER BY id LIMIT 0,100
SELECT * FROM table ORDER BY id LIMIT 101,100
SELECT * FROM table ORDER BY id LIMIT 201,100

The LIMIT would be from which position you want to start and the second variable would be how many results you want to see.
Its a good pagnation trick.