Why Newly Inserted Row Displaying at the Top of the table in oracle databse 12c..?

Question

Before i was working with MS SQL Server and recently moved to Oracle.

I have created java app that insert data to oracle table, but those inserting data displaying at the top of the table not at the bottom and not like SQL Server. i want to insert it at the bottom of the table. How may i do that..?

Please refer to this Screenshot. As you can see their, user table consist of user id which is automatically increment by java application.

i know how to sort the data using SQL-developer but i need to fix this default saving style because the app that i created takes 'U002' as the last record.

They are just displayed like that. What the deal in displaying. Add `Order by` to get it in right order — Pரதீப், Oct 23 '16 at 17:16
A table in a relational database is an unordered set of rows per ANSI SQL relational database concepts. As such there is no first, last, top or bottom. The SQL `ORDER BY` clause is required to guarantee result set ordering. Without `ORDER BY`, the sequence of rows returned is undefined. — Dan Guzman, Oct 23 '16 at 17:42

score 0 · Answer 1 · answered Oct 23 '16 at 17:16

Well, simple table structure is heap. If you create table and not specify type - its in common heap table. Its mean that new rows may be inserted in any free table space (realy, there is some rules but now we may forget about it). It means that you cant predict where new rows will be in output of select without ordering. If you want to sort result ou should specify an order by clause.

select * 
  from user_tbl 
 order by userid

score 0 · Answer 2 · answered Oct 23 '16 at 18:46

Just a simple example to see tat you can have no order without an ORDER:

SQL> create table unsortedTable(a number, b varchar2(1000));

Table created.

SQL> insert into unsortedTable
  2  select level, lpad('X', 1000, 'X')
  3  from dual
  4  connect by level <=10;

10 rows created.

SQL> delete unsortedTable where a between 4 and 5;

2 rows deleted.

SQL> insert into unsortedTable
  2  select -level, lpad('Y', 1000, 'Y')
  3  from dual
  4  connect by level <=4;

4 rows created.

SQL> select a, substr(b, 1, 5)
  2  from unsortedTable;

         A SUBSTR(B,1,5)
---------- --------------------
         1 XXXXX
         2 XXXXX
         3 XXXXX
         6 XXXXX
         7 XXXXX
        -1 YYYYY
        -2 YYYYY
         8 XXXXX
         9 XXXXX
        10 XXXXX
        -3 YYYYY
        -4 YYYYY

12 rows selected.

SQL>

The same sequence of operations, adding an /*+ append */ hint to the second insert statement wil give:

SQL> select a, substr(b, 1, 5)
  2  from unsortedTable;

         A SUBSTR(B,1,5)
---------- --------------------
         1 XXXXX
         2 XXXXX
         3 XXXXX
         6 XXXXX
         7 XXXXX
         8 XXXXX
         9 XXXXX
        10 XXXXX
        -1 YYYYY
        -2 YYYYY
        -3 YYYYY
        -4 YYYYY

12 rows selected.

Notice that this does NOT mean that an APPEND gives you a reliable way or ordering.

Why Newly Inserted Row Displaying at the Top of the table in oracle databse 12c..?

2 Answers2