Have Geek Brain

Wrong output in Commit In loop?

I'm trying to insert 1 to 10 numbers other than 6 and 8 in table posts, but when I get it from table mesages1, the output comes in this order 4 5 7 9 10 1 2 3 It should be like this 1 2 3 4 5 7 9 10 Logically, it works fine when I drop the commit or put it somewhere else, Please explain why this 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;

+2


source to share


2 answers


If you are not using ORDER BY

, you must accept the order in which the results are displayed is undefined. Often the results are in the same order in which they were inserted, but this is not guaranteed.



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

.

+8


source


As Matthey says, you need an order that explains the difference to ensure that the order is returned correctly.

If you have pending changes (i.e. uncommitted), you can see them (usually ...) because they have not been added to the datastore where the other data resides. Oracle maintains a separate pending change list, which it uses to modify the results from the underlying data store. In your example, the change from this list is in order, as there is very little data in the example, since apparently Oracle does not need to split the pending data in any way to optimize its storage.



Once the data is committed, it will go into the main database store and be ordered in any number of possible ways depending on how the table and partition are configured.

In short, the data comes from two different places before and after the commit, so it happens that it comes back in different orders, but does not rely on it, does not always behave that way.

0


source






More articles:


All Articles