Re: How to find last record in a table?

"Hans Forbrich" <hforbric_at_yahoo.net> wrote in message news:4005F38F.B8410012_at_yahoo.net...
> "B. Jones" wrote:
> >
> > Hey, gang.
> > I've been relying on a simple little statement that I THOUGHT would
> > show me the last record in a table. I found out today that it's
> > unreliable (returned records that were inserted on December 12 when
> > there were records from today).
> > Here's my statement...
> > select * from <table> where rowid = (select max(rowid) from <table>);
> >
> > What do you guys do to find the last record in a table?
> >
> > Thanks.
>
> In case you haven't caught on from other posts, you need to define
> 'last'.
>
> In entity-relationship theory and most relational database
> implementations, there is no inherent concept of order, and especially
> not 'order of insert'. If you need such an order, it should become an
> attribute of the entity and therefore a column and therefore sortable.
>
> Suggest you look up the formal definition of 'rowid'. While rowid
> implies the location of a row, I have not read anywhere that the
> location needs to be consecutive.

Hans is correct. The rowid does not have to be consecutive. Oracle is going to insert the row into a block that the row fits in. Since rows could vary in width it is possible - very likely - that the rowid will not be consecutive. eg(vastly simplified)
insert a record into myTable with a 4 K block size and this record is 3K

record goes into first block as first row.

Now insert another record that is also 3K. record goes into the second block and is the first row in that block.

Now insert another record that is 500 bytes. It is probably going to go into the first block as the 2nd row in that block. The max rowid will not point to the 2nd inserted row which is the 1st record in the 2nd block. You can also think of an example where you delete some rows and what happens in the next insert. I've vastly over simplified this example and I am ignoring any consideration of pctfree and pctused.

Jim Received on Wed Jan 14 2004 - 20:37:25 CST