| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: odd ( to me ) question
On Apr 12, 9:02 am, "EdStevens" <quetico_..._at_yahoo.com> wrote:
> On Apr 12, 7:13 am, "Ben" <bal..._at_comcast.net> wrote:
>
>
>
>
>
> > On Apr 12, 2:15 am, Jim Smith <usene..._at_ponder-stibbons.com> wrote:
>
> > > In message <1176339675.188623.205..._at_n59g2000hsh.googlegroups.com>, Ben
> > > <bal..._at_comcast.net> writes
>
> > > >On Apr 11, 6:15 pm, "joel garry" <joel-ga..._at_home.com> wrote:
> > > >> On Apr 11, 1:24 pm, "Ben" <bal..._at_comcast.net> wrote:
>
> > > >> > 9.2.0.5 EntEd AIX5L
>
> > > >> > If I have a read-only table and issue a select statment with no order
> > > >> > by clause, will the data be returned in the same order every time?
>
> > > >>http://jonathanlewis.wordpress.com/2007/03/05/ordering/
>
> > > >> jg
> > > >> --
> > > >> @home.com is bogus.http://volokh.com/posts/chain_1176127892.shtml
>
> > > >Sorry, I guess I was kind of beating about the bush, I really was just
> > > >wondering how oracle sorts by default with no order by clause when
> > > >selecting from one table. I thought maybe rowid or primary key but I
> > > >tried both it seemed like neither was the answer. I'm not trying to
> > > >rely on not using an order by, but the question was asked of me by a
> > > >user and I just didn't really know. I probably should but the thought
> > > >never really crossed my mind because of the order by.
>
> > > You can't rely on not using an order by.
>
> > > There is no default sort order - the order of a resultset largely
> > > depends on the access path used.
>
> > > If you do "select * from table" oracle will do a full table scan and the
> > > rows will be returned in the order the exist on disk which MAY be the
> > > order of insertion (unless there have been deletions and updates). If
> > > you use a where clause, oracle may use an index and the order will be
> > > the order of the index columns. If you join to another table all bets
> > > are off.
>
> > > There are some very simple cases where you can almost predict the order,
> > > but almost isn't good enough. And why bother? The SQL specification
> > > says that you can't rely on the order unless you use an order by clause
> > > - so why try to otherwise?
> > > --
> > > Jim Smith
> > > Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
> > > RSS <http://oracleandting.blogspot.com/atom.xml>- Hide quoted text -
>
> > > - Show quoted text -
>
> > I'm not trying to rely on it, that's the point. I'm trying to explain
> > to a user in laymans terms what order the data is coming out in. I
> > don't like to tell a user that I don't know, I'll tell them that I'll
> > have to find out. So I'm just trying to learn a little. I thought
> > about it last night and I think in my case since there is a where
> > clause it may be using an index order.
> > Like you were explaining though, I kind of assumed that the data would
> > be returned in the order that it was written to disk or last update or
> > something.
>
> > Once again, appreiciate all the good thoughts and knowledge.- Hide quoted text -
>
> > - Show quoted text -
>
> In layman's terms:
>
> "By design, there is *no* default sort sequence. Without an ORDER BY
> clause, the ordering of the returned rows is undefined and
> unpredictable."- Hide quoted text -
>
> - Show quoted text -
argggg. this just keeps bringing more questions to mind for me. in the instance that brought up this original question the select was actually using an index that included two fields. The two fields that were included where the same for all 4 rows of data. So again, there was no way to tell if it was sorting in any such manner. So I did a small test of my own, and it appears that in that case it returns rows in order of location on disk as well.
SQL> create table t1 (c1 char(2), c2 char(2), c3 char(2), c4 number) 2 tablespace crpdtat;
Table created.
SQL> create index t1_1 on t1( c1, c2 )
2 tablespace crpdtai;
Index created.
SQL> insert into t1 (c1, c2, c3, c4) values ('A', 'B', 'Z', 1);
1 row created.
SQL> insert into t1 (c1, c2, c3, c4) values ('A', 'B', 'W', 4);
1 row created.
SQL> insert into t1 (c1, c2, c3, c4) values ('A', 'B', 'X', 2);
1 row created.
SQL> insert into t1 (c1, c2, c3, c4) values ('A', 'B', 'Y', 3);
1 row created.
SQL> commit;
SQL> explain plan for
2 select rowid, c1, c2, c3, c4
3 from crpdta.t1
4 where c1 = 'A'
5 and c2 = 'B';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes |Cost |
| 0 | SELECT STATEMENT | | 4 | 48 | 2 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 4 | 48 | 2 | | 2 | INDEX RANGE SCAN | T1_1 | 4 | | 1 | ---------------------------------------------------------------------------
SQL> select rowid, c1, c2, c3, c4
2 from crpdta.t1
3 where c1 = 'A'
4 and c2 = 'B';
ROWID C1 C2 C3 C4
------------------ -- -- -- ---------- AAB+ptAAMAAD1hWAAA A B Z 1 AAB+ptAAMAAD1hWAAB A B W 4 AAB+ptAAMAAD1hWAAC A B X 2 AAB+ptAAMAAD1hWAAD A B Y 3
SQL> update crpdta.t1 set c4 = 9 where c4 = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select rowid, c1, c2, c3, c4
2 from crpdta.t1
3 where c1 = 'A'
4 and c2 = 'B';
ROWID C1 C2 C3 C4
------------------ -- -- -- ---------- AAB+ptAAMAAD1hWAAA A B Z 9 AAB+ptAAMAAD1hWAAB A B W 4 AAB+ptAAMAAD1hWAAC A B X 2 AAB+ptAAMAAD1hWAAD A B Y 3
SQL> delete from crpdta.t1 where c4 = 9;
1 row deleted.
SQL> commit;
SQL> select rowid, c1, c2, c3, c4
2 from crpdta.t1
3 where c1 = 'A'
4 and c2 = 'B';
ROWID C1 C2 C3 C4
------------------ -- -- -- ---------- AAB+ptAAMAAD1hWAAB A B W 4 AAB+ptAAMAAD1hWAAC A B X 2 AAB+ptAAMAAD1hWAAD A B Y 3
SQL> insert into crpdta.t1 (c1, c2, c3, c4) values ('A', 'B', 'Z', 1);
1 row created.
SQL> COMMIT; Commit complete.
SQL> select rowid, c1, c2, c3, c4
2 from crpdta.t1
3 where c1 = 'A'
4 and c2 = 'B';
ROWID C1 C2 C3 C4
------------------ -- -- -- ---------- AAB+ptAAMAAD1hWAAB A B W 4 AAB+ptAAMAAD1hWAAC A B X 2 AAB+ptAAMAAD1hWAAD A B Y 3 AAB+ptAAMAAD1hWAAE A B Z 1
i know, kind of pointless but it has helped me to explain to a user
what the root of their problem was and it sparked some thought and
intrigue. so all was not in vain.
thanks.
Received on Thu Apr 12 2007 - 09:14:29 CDT
 |
 |