|
|
|
|
Re: which is best to locate row in oracle table [message #661142 is a reply to message #661137] |
Thu, 09 March 2017 00:48 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
asliyanage wrote on Thu, 09 March 2017 04:49I found that we can use Rowid to locate the row in oracle. So i need to know its better than the using primery key or indexing ?
Consider this:orclz>
orclz> select * from dept where rowid='AAAWy5AAGAAAAC3AAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
orclz> alter table dept move;
Table altered.
orclz> select * from dept where rowid='AAAWy5AAGAAAAC3AAA';
select * from dept where rowid='AAAWy5AAGAAAAC3AAA'
*
ERROR at line 1:
ORA-01410: invalid ROWID
orclz> Not so good, is it?
|
|
|
|
Re: which is best to locate row in oracle table [message #661145 is a reply to message #661144] |
Thu, 09 March 2017 01:23 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
asliyanage wrote on Thu, 09 March 2017 07:13that means if we change the table structure , we cant use the rowid It doesn't mean that at all:orclz> select * from dept where rowid='AAAW3NAAGAAAAFbAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
orclz> alter table dept add (newcol varchar2(10));
Table altered.
orclz> update dept set newcol='idiot' where rowid='AAAW3NAAGAAAAFbAAA';
1 row updated.
orclz> select * from dept where rowid='AAAW3NAAGAAAAFbAAA';
DEPTNO DNAME LOC NEWCOL
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK idiot
orclz> just do a few tests, and you'll find out that using rowids is, how shall I put it, not very sensible.
|
|
|
|
|
|
Re: which is best to locate row in oracle table [message #661149 is a reply to message #661147] |
Thu, 09 March 2017 02:28 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Perhaps one could justify something like this:orclz>
orclz> var rid varchar2(18)
orclz> insert into dept values(99,'new','uk') returning rowid into :rid;
1 row created.
orclz> select * from dept where rowid=:rid;
DEPTNO DNAME LOC
---------- -------------- -------------
99 new uk
orclz> the row lock will block anything that would change the rowid. Pretty unlikely, though.
|
|
|
|
|
|
Re: which is best to locate row in oracle table [message #661153 is a reply to message #661152] |
Thu, 09 March 2017 03:43 |
cookiemonster
Messages: 13922 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Rowid is useful in situations where you select data for update (thus locking it) and then do stuff to / review the data before updating/deleting.
Then rowid is the most efficient way to do the subsequent update/delete.
Since it can change between transactions you can't store it in other tables, as others have mentioned. It's this fact that means it's usefulness is limited.
|
|
|
|