Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What is the order of query result by default ?

Re: What is the order of query result by default ?

From: zauberberg <jwberg_at_verizon.net>
Date: 6 Sep 2006 11:04:56 -0700
Message-ID: <1157565896.416358.140830@e3g2000cwe.googlegroups.com>

Maxim Demenko wrote:
> krislioe_at_gmail.com schrieb:
> > Hi All,
> >
> > If I do : select * from myTable, is it guaranteed that the query
> > resultset is always the order when the data is inserted into the table
> > ?
> In Oracle it is guaranteed, that you have *no guarantee* in ordering of
> result set retrieved from heap organized tables unless you specify ORDER
> BY clause
> > For example :
> >
> > create table myTable(col1 varchar(2) primary key)
> >
> > Insert into mtTable values('x4')
> > Insert into mtTable values('x3')
> > Insert into mtTable values('x1')
> > Insert into mtTable values('x2')
> >
> You can not rely on this order, it depends on a lot of factors, the only
> reliable possibility is to specify ORDER BY clause
> > select * from myTable // without any where condition.
> WHERE condition is irrelevant to order of data set
> >
> > then the query result is ALWAYS :
> As somebody said, ALWAYS IS NEVER TRUE, NEVER IS ALWAYS FALSE ;-)
> > x4
> > x3
> > x1
> > x2
> >
> > Thank you ,
> > xtanto
> >
>
> Best regards
>
> Maxim

a relational db is not a list. so rows are inserted based on how the database sees the data. For simple cases like the one you presented, the rows are small and there is no insert/update/delete activity, so it probably will end up in insert order. Once you start looking at a table that has hundreds or thousands of rows, with updates ( causing rows to be split across physical data blocks) and deletes ( which may free up space for a smaller row to be inserted into), you must declare the order that you need to results sorted into. Received on Wed Sep 06 2006 - 13:04:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US