From: "spencer" Subject: Re: ROWID always increase for insert-only table? Date: 2000/05/05 Message-ID: #1/1 References: <8evg0r$svi$1@nnrp1.deja.com> X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Complaints-To: abuse@swbell.net X-Trace: news.swbell.net 957579990 208.190.213.116 (Fri, 05 May 2000 21:26:30 CDT) Organization: SBC Internet Services X-MSMail-Priority: Normal NNTP-Posting-Date: Fri, 05 May 2000 21:26:30 CDT Newsgroups: comp.databases.oracle.server wrote in message news:8evg0r$svi$1@nnrp1.deja.com... > Hello all, > > We had a complaint today from financial dept. about some long-made > reports not being orderded correctly by date as usual. By further > inspection, I realized that instead being ordered by the DATE column > of the table, the select was being ordered by ROWID, since the table > is very large. > The person who did it made the assumption that, since using ROWID > for ordering is much faster than using a common column, and the table > never gets deleted or updated (inserts only), it would make sense to > use ORDER BY ROWID instead of ORDER BY DATE_COLUMN . > When I tried to reproduce the select on SQL*PLUS, showing the rowid, > it really gave me the wrong order. After that I tried this select: > > select max(ROWID) from table > > and it also returned the wrong row (a rowid from a row inserted on > the table on 1999, when there are many records inserted on 2000 after > that one), showing that the greatest ROWID really was not assigned to > the last inserted row in the table. > > So, I had to change the 'order by' clause to use the date column, and > everything worked again except by the fact that the report is much > slower now. > > As I always believed that it should be true, can someone explain me > the reason of this? We were using 7.3.4 when the report was made, and > today we use 8.0.5 . > > Thanks for the help, > > Luis Derani ( lderani@uol.com.br ) > Syst.Analist > Universo Online - SP - Brazil > > > Sent via Deja.com http://www.deja.com/ > Before you buy. > as far as i know, Oracle does not guarantee that rows inserted sequentially into a table will necessarily be assigned a ROWID that is greater than the ROWID of any and all rows already in the table. as i understand it, ROWID is assigned based on the location of the row in data block, and the location of the block within the segment. a couple of scenarios come to mind: the rows were not inserted in a particular order. a previously "full" data block was put back onto the freelist when space used in the block fell below the specified value, and inserts of new rows went into those blocks. some of the data in the table was reloaded.