|
|
|
|
|
Re: how to fetch last inserted record from oracle table? [message #332988 is a reply to message #332985] |
Thu, 10 July 2008 04:39 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Because a new column might be inserted with a ROWID lower than an already existing old one.
You can assume that the chances of a new row getting a higher rowid than the old ones in the table are somewhat higher than getting a lower one (especially when there have NEVER been any deletions from the table) but you can NOT depend on it.
|
|
|
Re: how to fetch last inserted record from oracle table? [message #332989 is a reply to message #332985] |
Thu, 10 July 2008 04:39 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Because there is no concept of 'last' in a relational database.
Unless you have an ORDER BY clause, there is no guarantee what order you will get the records back in.
FOr small tables that don't use an index (ie almost all demo tables in this forum) rows will almost invariably come back in the order that they were inserted in, as that is the order that a Full Table Scan will read them in.
But, in a live system where you delete rows, you will get rows inserted to fill in the gaps in blocks left by earlier deletions. This means that the highest rowid does not have to refer to the last inserted record.
|
|
|
Re: how to fetch last inserted record from oracle table? [message #332995 is a reply to message #332989] |
Thu, 10 July 2008 04:45 |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
JRowbottom wrote on Thu, 10 July 2008 04:39 | Because there is no concept of 'last' in a relational database.
Unless you have an ORDER BY clause, there is no guarantee what order you will get the records back in.
FOr small tables that don't use an index (ie almost all demo tables in this forum) rows will almost invariably come back in the order that they were inserted in, as that is the order that a Full Table Scan will read them in.
But, in a live system where you delete rows, you will get rows inserted to fill in the gaps in blocks left by earlier deletions. This means that the highest rowid does not have to refer to the last inserted record.
|
Thanks for the update. I would be thankful to you if you demonstrate with an example.
Say we deleted a record.Can we restore the same rowid? Can the records inserted take the same rowid? Is it possible.
Thanks in advance,
Oli
[Updated on: Thu, 10 July 2008 04:48] Report message to a moderator
|
|
|
|
Re: how to fetch last inserted record from oracle table? [message #333004 is a reply to message #332995] |
Thu, 10 July 2008 04:59 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It's pretty easy to demonstrate. Run this, and you should see what happens:create table test_0046 (col_1 number, col_2 number);
-- Insert more than a block worth of data.
insert into test_0046 select level, mod(level,100) from dual connect by level <= 2000;
-- Demonstrate that the data comes back in insert order
select col_1,col_2 from test_0046;
-- delete lots of data from the same block
delete test_0046 where col_1 <= 300;
-- Insert lots more data
insert into test_0046 select -1*level, 999 from dual connect by level <= 300;
select col_1,col_2 from test_0046;
|
|
|
|
|
|
|
|
|
|
|
|
|
|