Re: Pls Help... Oracle sort order

Comments embedded.
On Jul 6, 3:40 pm, Amritha.Da..._at_gmail.com wrote:
> In which sort order does oracle display the records if there is no
> sort order specified in the SQL query?
>

There is no 'order' to a heap table. If YOU don't code an ORDER BY clause you can't expect any ordering of the results. The only table which could be ordered is an IOT (index-organized table) as it requires a primary key:

SQL> create table zing (a number primary key, b number) organization index
SQL>
SQL> / Table created.

SQL> insert into zing values (1,1);

1 row created.

SQL> insert into zing values (2,1);

1 row created.

SQL> insert into zing values (0,1);

1 row created.

SQL> insert into zing values (4,1);

1 row created.

SQL> commit;

Commit complete.

SQL> select *
  2 from zing
  3 /

         A B
---------- ----------

         0          1
         1          1
         2          1
         4          1

SQL> insert into zing values (3,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select *
  2 from zing
  3 /

         A B
---------- ----------

         0          1
         1          1
         2          1
         3          2
         4          1

SQL>
> Is there any article which tells about this?

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:74320098178823

>
> My Oracle version is 9i
>

It doesn't matter if you're using Oracle 2 or Oracle 10gR2, the answer is still the same. Maybe in Oracle 44 ordered heap tables will exist.

> Thanks.

David Fitzjarrell Received on Fri Jul 06 2007 - 15:53:26 CDT