Home » SQL & PL/SQL » SQL & PL/SQL » which is best to locate row in oracle table
which is best to locate row in oracle table [message #661137] Wed, 08 March 2017 22:49 Go to next message
asliyanage
Messages: 60
Registered: January 2017
Member
I 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 ?
Re: which is best to locate row in oracle table [message #661138 is a reply to message #661137] Wed, 08 March 2017 23:10 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
asliyanage wrote on Wed, 08 March 2017 20:49
I 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 ?
Which metric measures best?

post SQL & results that show how you obtain ROWID.

What problem are you trying to solve?

How do I ask a question on the forums?



Re: which is best to locate row in oracle table [message #661139 is a reply to message #661138] Wed, 08 March 2017 23:13 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
i know when we searching a row in table we can use primery key or rowid to select the row. I need to which one is faster or performance wise good ?
Re: which is best to locate row in oracle table [message #661140 is a reply to message #661139] Wed, 08 March 2017 23:20 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
asliyanage wrote on Wed, 08 March 2017 21:13
i know when we searching a row in table we can use primery key or rowid to select the row. I need to which one is faster or performance wise good ?
Since you know, then perform both alternatives & measure which is better.
Re: which is best to locate row in oracle table [message #661142 is a reply to message #661137] Thu, 09 March 2017 00:48 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
asliyanage wrote on Thu, 09 March 2017 04:49
I 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 #661144 is a reply to message #661142] Thu, 09 March 2017 01:13 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
that means if we change the table structure , we cant use the rowid
Re: which is best to locate row in oracle table [message #661145 is a reply to message #661144] Thu, 09 March 2017 01:23 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
asliyanage wrote on Thu, 09 March 2017 07:13
that 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 #661146 is a reply to message #661144] Thu, 09 March 2017 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

asliyanage wrote on Thu, 09 March 2017 08:13
that means if we change the table structure , we cant use the rowid
In short this means:
1/ Using ROWID is the fastest way to access a row
2/ Using ROWID is the riskiest way to access a row (as the location of a row can change)

You can use ROWID in a single statement.
You must never store ROWID.

Re: which is best to locate row in oracle table [message #661147 is a reply to message #661146] Thu, 09 March 2017 02:04 Go to previous messageGo to next message
Roachcoach
Messages: 1513
Registered: May 2010
Location: UK
Senior Member
Plus to find the rowid, you had to access it in the first place via another method... Wink
Re: which is best to locate row in oracle table [message #661148 is a reply to message #661147] Thu, 09 March 2017 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Indeed. Laughing

Re: which is best to locate row in oracle table [message #661149 is a reply to message #661147] Thu, 09 March 2017 02:28 Go to previous messageGo to next message
John Watson
Messages: 7265
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 #661150 is a reply to message #661149] Thu, 09 March 2017 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, this is one of the rare cases.
I sometimes saw applications that insert quite empty rows and then update them with computed values or values entered in other forms.

[Updated on: Thu, 09 March 2017 02:49]

Report message to a moderator

Re: which is best to locate row in oracle table [message #661151 is a reply to message #661150] Thu, 09 March 2017 02:52 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Holy smoke! The row migration must have been horrific. Fire the programmer.
Re: which is best to locate row in oracle table [message #661152 is a reply to message #661151] Thu, 09 March 2017 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, it was and this is the reason (the row migration issue) I, as a DBA, saw this and had to analyze the application behavior.
It was more the designer problem than the programmer one who just followed the specification... and I was happy when there were no commits between each window.

Re: which is best to locate row in oracle table [message #661153 is a reply to message #661152] Thu, 09 March 2017 03:43 Go to previous messageGo to next message
cookiemonster
Messages: 13017
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.
Re: which is best to locate row in oracle table [message #661159 is a reply to message #661153] Thu, 09 March 2017 07:11 Go to previous message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
You can use rowid within a cursor or a statement but NEVER use it otherwise and NEVER EVER store it in a table to access another table.
Previous Topic: Exchange Partition
Next Topic: How do I total each column of data with multiple queries?
Goto Forum:
  


Current Time: Sun Feb 25 04:26:04 CST 2018

Total time taken to generate the page: 0.02357 seconds