Commit In loop gives wrong output?

Question

I am trying to insert 1 to 10 numbers except 6and 8 in table messages,but when i fetch it from table mesages1, output is coming in this order 4 5 7 9 10 1 2 3 It should be like this 1 2 3 4 5 7 9 10 According to the logic ,it works fine when i omit commit or put it some where else, Please explain why it is happening? this is my code.

BEGIN
    FOR i IN 1..10
    LOOP
    IF i<>6 AND i<>8
    THEN
    INSERT INTO messages1
    VALUES (i);
    END IF;
    commit;
    END LOOP;
END;

select * from messages1;

Answer 1

If you don't use ORDER BY, you should assume the order the results appear in is undefined. Often the results are in the same order they were inserted in, but it's not guaranteed.

Bottom line, if you want your results in some specific order, use ORDER BY.

Answer 2

As Matti says you need the order by clause explicity to guarantee the ordering is returned correctly.

When you have pending changes (ie uncommitted ones) you are the only one able to see them (generally...) this because they haven't been added to the data store where the other data is. Oracle maintains a separate list of pending changes which it uses to alter the results it it gets from the main data store. In your example the changing from this list happens to be returning in order, as there is very little data in the example Oracle presumably isn't needing to split the pending data in any way for optimise its storage.

Once the data is committed it will go into the main database storage and be ordered in any number of possible ways depending on how the table and partition is set up.

So in short, the data is coming from two different places before and after the commit, it just so happens they are returning in different orderings, but don't rely on them not always behaving like that.