SEO

#1 (**permalink**) 02-23-2008, 07:29 AM

Hi there....
A simple question...
Using OCI I already has fetched a row (using a WHERE clause) **FOR UPDATE**.
After some data manipulation and conditions I want to Update the record with
new data...
Is it faster do the UPDATE using the RowId or using the WHERE clause again!?

TIA!!!

Danny

#2 (**permalink**) 02-23-2008, 07:30 AM

Hello Daniel,

Access by rowid is the fastest access path.

Regards,

Ron
DBA Infopower
http://www.dbainfopower.com
Standard disclaimer:
http://www.dbainfopower.com/dbaip_ad...isclaimer.html

"Daniel" <danny.icha@usa.net> wrote in message
news:96eef697.0403041432.2047d6c9@posting.google.c om...
> Hi there....
> A simple question...
> Using OCI I already has fetched a row (using a WHERE clause) **FOR
UPDATE**.
> After some data manipulation and conditions I want to Update the record
with
> new data...
> Is it faster do the UPDATE using the RowId or using the WHERE clause
again!?
>
> TIA!!!
>
> Danny

#3 (**permalink**) 02-23-2008, 07:30 AM

On 4 Mar 2004 14:32:37 -0800, danny.icha@usa.net (Daniel) wrote:

>Hi there....
>A simple question...
>Using OCI I already has fetched a row (using a WHERE clause) **FOR UPDATE**.
>After some data manipulation and conditions I want to Update the record with
>new data...
>Is it faster do the UPDATE using the RowId or using the WHERE clause again!?
>
>TIA!!!
>
>Danny

It is faster to use the rowid. Think of it: even if you have the
primary key in your where statement, you will use 3 or 4 LIOs. Using
the rowid you have only one.

--
Sybrand Bakker, Senior Oracle DBA

#4 (**permalink**) 02-23-2008, 07:30 AM

Small note (May not be a problem but just wanted to show).
create table x(a number);
insert into x values(1);

from session 1
declare
v1 rowid;
begin
select rowid into v1 from x;
dbms_lock.sleep(20);
update x set a=100 where rowid=v1;
end;
/

from session 2 - After 5 secs of starting the above in session 1
alter table x move tablespace users;

Session 1 error
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 6

Even worse you may update a wrong row.
May want to convert that select rowid into v1 from x into a select
rowid into v1 from x for update.

regards
Srivenu

#5 (**permalink**) 02-23-2008, 07:32 AM

I don't know of anyone that would consider executing DML code on a
table while they were re-organizing it like this. So your example
doesn't make a lot of sense.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

srivenu@hotmail.com (srivenu) wrote in message news:<1a68177.0403050353.370e1024@posting.google.c om>...
> Small note (May not be a problem but just wanted to show).
> create table x(a number);
> insert into x values(1);
>
> from session 1
> declare
> v1 rowid;
> begin
> select rowid into v1 from x;
> dbms_lock.sleep(20);
> update x set a=100 where rowid=v1;
> end;
> /
>
> from session 2 - After 5 secs of starting the above in session 1
> alter table x move tablespace users;
>
> Session 1 error
> ERROR at line 1:
> ORA-01410: invalid ROWID
> ORA-06512: at line 6
>
> Even worse you may update a wrong row.
> May want to convert that select rowid into v1 from x into a select
> rowid into v1 from x for update.
>
> regards
> Srivenu

#6 (**permalink**) 02-23-2008, 07:32 AM

Mark wrote:

> I don't know of anyone that would consider executing DML code on a
> table while they were re-organizing it like this. So your example
> doesn't make a lot of sense.
>
> Mark Simmons
> Sr. Oracle DBA
> Sabre-Holdings, Southlake, TX
>
> srivenu@hotmail.com (srivenu) wrote in message news:<1a68177.0403050353.370e1024@posting.google.c om>...
>
>>Small note (May not be a problem but just wanted to show).
>>create table x(a number);
>>insert into x values(1);
>>
>>from session 1
>>declare
>>v1 rowid;
>>begin
>>select rowid into v1 from x;
>>dbms_lock.sleep(20);
>>update x set a=100 where rowid=v1;
>>end;
>>/
>>
>>from session 2 - After 5 secs of starting the above in session 1
>>alter table x move tablespace users;
>>
>>Session 1 error
>>ERROR at line 1:
>>ORA-01410: invalid ROWID
>>ORA-06512: at line 6
>>
>>Even worse you may update a wrong row.
>>May want to convert that select rowid into v1 from x into a select
>>rowid into v1 from x for update.
>>
>>regards
>>Srivenu

If someone wants to alter a table's structure while the database
is up and the table is in use they should look at the DBMS_REDEFINITON
built in package.

http://www.psoug.org/reference/dbms_redefinition.html

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Updating by RowId when alread has fetched the row....