Re: How to get latest inserted record in a table

Hi Tom,
I agree with you about the guessing part.  I originally answered someone who
appeared to have a database from someplace with data already in it.  They
were questioning how to extract the data in some timestamped order, but I
read it that they didn't have a timestamp ;^)  So I suggested to create a
new table (with a proper timestamp) as a "Select From..." the old table
using the Rowid as a guess/helper.
Cheers,
Earl

-----Original Message-----
From: Thomas Gaines [mailto:tanguero@xxxxxxxxxx]
Sent: Tuesday, November 26, 2002 3:06 PM
To: Oracle
Subject: [oracle] Re: How to get latest inserted record in a table


> Hi Thomas,
> Searching AskTom's archives, I find that rowids are unique to table, and I
> have seen the row number increment in lock-step with the time of insert;
> i.e. insert a record, commit, rowid row number (RRR) = AAb; insert next
> record, commit, rowid row number = AAc etc.  (Ref Oracle8i Concepts Part
> Number A76965-01 Chapter 12 - Extended Rowids)
> Earl

Earl -

You are correct about what the rowid represents, but you certainly cannot
determine with certainty when that row was filled.  The rowid represents the
LOCATION of the data on a disk.  It's true that you can make some guesses
about when the data was written, but only if the data is written as a group
to disk.  In that case, the records will follow each other consecutively in
time and on disk, and you can make some decisions.

However, if Oracle sees that there are holes in a particular data block that
can be filled, then it will do so under the correct circumstances.  (To
learn about these circumstances, read chapter six of Tom Kyte's book.)
I know that he's written a great deal on his site, in his book, and in the
various Oracle-related newsgroups about this.  He's developed some
extremely convincing test cases that illustrate what Oracle is doing behind
the scenes.  Read up!

In short, you CAN make some guesses about when a record was inserted,
but they are guesses only.  The only way to determine a record's insert
time with absolute confidence is to attach this data to a particular record.

Bye,
Tom
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to leave-oracle-1796914O@xxxxxxxxxxxxx