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

zauberberg wrote:
> 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.

It's the multiuser thing that makes it possible to disorder it with a few rows. Imagine the case of someone inserting a row with a value from a sequence, another person does the same thing... but waits a few minutes before committing, while the first person inserts a few more, committing each time. Meanwhile, a third person starts a select, and won't even see the second person's row. Later, third person might see it out of order, or not out of order, no way to predict.

Cached data may be way ahead of data on disk, too. Oracle may need to get block information from several places to build a consistent view from the start of each users transaction. http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/intro.htm#i61063

jg

--
@home.com is bogus.  Problem in hint query.
SQLERRM = ORA-30554: function-based index DB102.CATALOG_VIEW_INDEX is
disabled 
WORD = read consistency 
REAL_WORD = read consistency