Home » SQL & PL/SQL » SQL & PL/SQL » How to find which record is inserted first (Oracle 9i)
How to find which record is inserted first [message #448943] Thu, 25 March 2010 22:58 Go to next message
chandramatta
Messages: 15
Registered: January 2010
Junior Member

Hi,
I have a table with lot of records.
In this table, how do I find, which record is inserted just before a record in my question.

consider this example..

SQL> desc test1
Name Null? Type
------------------------------- -------- ----
SEQ_ID NUMBER
NAME VARCHAR2(1)

SQL> select * from test1;

SEQ_ID N
--------- -
1016 a
1107 b
1018 c
1109 d
1200 e
1020 e

in the above data, if I want to know what record is inserted before "1018", how can I find it.

Can we find out based on ROWID.?

Matt.




Re: How to find which record is inserted first [message #448944 is a reply to message #448943] Thu, 25 March 2010 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
Rows in a table are like balls in a basket.
If you remove the RED ball, how do you know which ball entered the basket just before the RED ball?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: How to find which record is inserted first [message #448950 is a reply to message #448943] Thu, 25 March 2010 23:25 Go to previous messageGo to next message
chandramatta
Messages: 15
Registered: January 2010
Junior Member

Hi Black Swan, Thank you for your reply.
Apologies if I violated any of the guidelines for this forum.

Is there any way to find out order of records inserted in to a table. Is RowId sequential in a table.?

Kindly help me.
Re: How to find which record is inserted first [message #448951 is a reply to message #448950] Thu, 25 March 2010 23:32 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
>Is there any way to find out order of records inserted in to a table.
YES, include a TIMESTAMP field that gets populated upon INSERT.

>Is RowId sequential in a table.?
ROWID is pseudo column that gets populated during SELECT which can be impacted by ORDER BY clause
Re: How to find which record is inserted first [message #448954 is a reply to message #448951] Fri, 26 March 2010 00:20 Go to previous messageGo to next message
chandramatta
Messages: 15
Registered: January 2010
Junior Member

Hi Blackswan,

- Unfortunately I can not change structure of this table to add a new column for timestamp. thats my bad.

- I believe it's rownum, which gets assigned to each record during SELECT query.
And also this rownum will be impacted based on ORDER BY clause.

Please correct me if I am wrong.

Thanks.
Re: How to find which record is inserted first [message #448961 is a reply to message #448954] Fri, 26 March 2010 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you are right it is ROWNUM.
ROWID is the physical address of the rows and it is no related to time not more that when people buy flat these ones are taken in sequential order.

There are 2 ways to get a "timestamp" for row, either add a timestamp column or add rowdependency to the table definition (at creation time, so too late for you). In both case you also should have a sequential number as many rows can be inserted with the same "insert select" statement.
So, at the end, if only the order of row insert is your issue, it is a sequence and sequential column you need (but once again it is not possible for you if you can't modify the table).

Regards
Michel
Re: How to find which record is inserted first [message #448970 is a reply to message #448961] Fri, 26 March 2010 01:37 Go to previous messageGo to next message
chandramatta
Messages: 15
Registered: January 2010
Junior Member

Thanks Michel. Thats useful.
Re: How to find which record is inserted first [message #449010 is a reply to message #448961] Fri, 26 March 2010 04:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In addition, if it is just the order of the rows that you are interested in, you could add a NUMBER column, and populate it with a value fetched from a sequence - this would achieve much the same effect.

Re: How to find which record is inserted first [message #449056 is a reply to message #448950] Fri, 26 March 2010 08:06 Go to previous messageGo to next message
joy_division
Messages: 4615
Registered: February 2005
Location: East Coast USA
Senior Member
chandramatta wrote on Fri, 26 March 2010 00:25

Is there any way to find out order of records inserted in to a table.


You might have it already. What is this column SEQ_ID? Is it truly a sequence? Is it populated by using an Oracle sequence? If so, then you might have your answer.
Re: How to find which record is inserted first [message #449185 is a reply to message #448943] Sat, 27 March 2010 11:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
There are commonly three ways in which this is done, none of which is available to you.

Quote:
1) add a number column and use a sequence defined using the WITH ORDER clause and NOCYCLE clause (check with your DBA to see how the SEQ_ID column is populated, maybe you will get lucky).

2) add a varchar2 column and use a GUID

3) add a timestamp column and use a timestamp (TECHNICALLY NOT VALID, but it works given that with current computing technology no two timestamps will be generated by oracle even for concurrent transactions. The granularity of timestamp is for the moment too fine. However this is likely to change in the future so using this method could potentially give you problems later.


As I recall, ROWDEPENDENCIES does not give you what you want. This will only track oracle's SCN at the row level rather than the block level, but it won't help you know which row was inserted before another as multiple rows commited in the same transaction should contain the same SCN. At least that is how I understand it.

ROWID won't tell you the order of rows inserted.
ROWNUM won't tell you the order of rows inserted.

Kevin

[Updated on: Sat, 27 March 2010 11:45]

Report message to a moderator

Re: How to find which record is inserted first [message #449194 is a reply to message #449185] Sat, 27 March 2010 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
multiple rows commited in the same transaction should contain the same SCN. At least that is how I understand it.

You're right, not for the same transaction but for the same statement, this is why I said:
Quote:
In both case you also should have a sequential number as many rows can be inserted with the same "insert select" statement.


Regards
Michel
Re: How to find which record is inserted first [message #449197 is a reply to message #448943] Sat, 27 March 2010 12:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks for the clarification. I will have to re-read the docs on this subject as I am apparently forgetting too much.

Kevin
Re: How to find which record is inserted first [message #449200 is a reply to message #449197] Sat, 27 March 2010 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But I was wrong, it is the commit SCN that is recorded in each row and so the same SCN for all rows inserted/modified during a transaction (and not only a statement):
SQL> create table t (val integer) rowdependencies;

Table created.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    7185941

1 row selected.

SQL> insert into t select 10+level from dual connect by level <= 3;

3 rows created.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    7185946

1 row selected.

SQL> insert into t select 20+level from dual connect by level <= 3;

3 rows created.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    7185975

1 row selected.

SQL> select ora_rowscn, val from t order by val;
ORA_ROWSCN        VAL
---------- ----------
                   11
                   12
                   13
                   21
                   22
                   23

6 rows selected.

SQL> commit;

Commit complete.

SQL> select ora_rowscn, val from t order by val;
ORA_ROWSCN        VAL
---------- ----------
   7185980         11
   7185980         12
   7185980         13
   7185980         21
   7185980         22
   7185980         23

6 rows selected.

Regards
Michel
Re: How to find which record is inserted first [message #449221 is a reply to message #448943] Sun, 28 March 2010 06:02 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Michel for doing the testing. I always remember it this way because I remember SCN to mean SYSTEM COMMIT NUMBER.

So for the moment, until someone smarter than us answers, it appears that to know the order in which rows are inserted, one must modify the table with a column intended to perform this function and the write some code in order to populate this column.

This question of course begs a bigger question:

Quote:
How does one know the order in which rows were operated on in general? For example, what does an update mean to "order of rows" and what does a delete mean? How do you track each?

Kevin

[Updated on: Sun, 28 March 2010 06:07]

Report message to a moderator

Previous Topic: Partition
Next Topic: Ora-00907 - missing right paranthesis
Goto Forum:
  


Current Time: Sun Sep 25 07:50:06 CDT 2016

Total time taken to generate the page: 0.04039 seconds