Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: What is the order of query result by default ?
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
![]() |
![]() |