Search code examples
oracle-databaseselectinsertoracle18c

Why aren't rows returned in the order of insertion?

Oracle 18c:

I have 1000 rows of test data:

create table lines (id number, shape sdo_geometry);
begin
    insert into lines (id, shape) values (1, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(574360, 4767080, 574200, 4766980)));
    insert into lines (id, shape) values (2, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(573650, 4769050, 573580, 4768870)));
    insert into lines (id, shape) values (3, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(574290, 4767090, 574200, 4767070)));
    insert into lines (id, shape) values (4, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(571430, 4768160, 571260, 4768040)));
    insert into lines (id, shape) values (5, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(571500, 4769030, 571350, 4768930)));
...
end;
/

Full data here: db<>fiddle

When I select the data:

select 
    id,
    sdo_util.to_wktgeometry(shape) 
from 
    lines

...the data doesn't get returned in the order that I inserted it.

SQL Developer / on-prem db:

enter image description here

db<>fiddle:

enter image description here

I would have expected ID #1 to be the first row, and so on.

I know in reality, we would never rely on the order of the rows in the table. We would sort the data using order by if the ordering was important.

But I'm still curious, why wouldn't the data be returned in the order that it was inserted in? What's going on there?

Solution

  • The short answer is: tables are basically “heaps" and the rows are inserted where ever they want.