Home » SQL & PL/SQL » SQL & PL/SQL » Order of retreival from table
Order of retreival from table [message #194957] Tue, 26 September 2006 05:28 Go to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Hi,

When ever we fire a statement like 'Select * from emp' in our schema, is it necessary that the order of rows returned from the table is always same. Is there a possibility that the position of rows are changed in different retreivals. Assume our EMP table is having close to 1 million records.

thanks
Re: Order of retreival from table [message #194959 is a reply to message #194957] Tue, 26 September 2006 05:35 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

Is there a possibility that the position of rows are changed in different retreivals.


Yes. There is no guarantee of order without an order by.
Re: Order of retreival from table [message #194963 is a reply to message #194959] Tue, 26 September 2006 05:46 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member

Hi JSI2001,

Thanks for the reply. However, why is it that the data which is stored in data blocks taken from the Heap memory could be retreived in different order in subsequent retreivals.



Re: Order of retreival from table [message #194971 is a reply to message #194963] Tue, 26 September 2006 06:10 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

taken from the Heap memory
What is the heap memory? I assuming that you are talking about Buffer cache?
Oracle (as with most rdms) are very particular about this
without an order by, you cannot guarantee the order of a result set Why? personally, I really don't care, it is a fact, immutable. You used the term heap. That kinda answers your question. Oracle simply says 'Get all the data requested' if you add an order by, it then says, 'Now return in this order'. That's how it works.
Re: Order of retreival from table [message #194974 is a reply to message #194963] Tue, 26 September 2006 06:19 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Because if you use two different queries to access the data, then they may well access the rows in a different order. Just because the block is cached doesn't mean that every query will return all the rows in that block at the same time.

Most of the time, the data may well come back in the same order, but a wide range of things can affect this (new statistics, indexes, join types, optimiser version...), and Oracle explicitly state that the order of rows is not guaranteed without an Order By
Quote:

order_by_clause
Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.


Example:
SQL> create table test_emp (empno  number, ename  varchar2(30), deptno number);

Table created.

SQL> 
SQL> begin
  2    for i in 1..10000 loop
  3      insert into test_emp values (i,'Employee '||i,mod(i,10)+1);
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> create index test_emp_idx on test_emp(empno desc, deptno);

Index created.

SQL> 
SQL> analyze table test_emp compute statistics;

Table analyzed.

SQL> 
SQL> select * from test_emp where deptno = 5 and rownum < 10;

     EMPNO ENAME                              DEPTNO
---------- ------------------------------ ----------
         4 Employee 4                              5
        14 Employee 14                             5
        24 Employee 24                             5
        34 Employee 34                             5
        44 Employee 44                             5
        54 Employee 54                             5
        64 Employee 64                             5
        74 Employee 74                             5
        84 Employee 84                             5

9 rows selected.

SQL> 
SQL> select empno,deptno from test_emp where deptno = 5 and rownum < 10;

     EMPNO     DEPTNO
---------- ----------
      9994          5
      9984          5
      9974          5
      9964          5
      9954          5
      9944          5
      9934          5
      9924          5
      9914          5

9 rows selected.
Previous Topic: explain query
Next Topic: NEED TO SHOW THE TOP 5 DATA
Goto Forum:
  


Current Time: Tue Dec 06 02:37:07 CST 2016

Total time taken to generate the page: 0.05536 seconds