Home » SQL & PL/SQL » SQL & PL/SQL » where current of use (oracle 8)
where current of use [message #285421] Tue, 04 December 2007 08:37 Go to next message
rosee
Messages: 1
Registered: December 2007
Location: France
Junior Member
Hi,

I know that the WHERE CURRENT OF clause is used to update or to delete datas which are in a declared cursor, at the current record level.

But we know that the treatment in a cursor is done line after line; so why do we need to precise that the updating or deleting will happen in the current record .

for example if i have this updating
Cursor c IS select rowid,ste,client , name from TCLIENT FOR UPDATE;

BEGIN

FOR r IN c
LOOP
UPDATE TCLIENT set name='A' where ste = r.ste AND client = r.client;
UPDATE TCLIENT set name='A' where rowid=r.rowid;

UPDATE TCLIENT set name='A' where current of c;
END LOOP;
END;
these UPDATE requests give the same result but what is the difference between the third one(with where the current of clause)and the two others ones?

Secondly, i know that from the moment we use the for update clause, the cursor 's datas are locked.
Is the difference comes at this level( locking level).
Could you show me examples where we see the difference between queries we use the where current of clause and queries we don't.

Thanks a lot for your help.

Regards

Nathalie




[Updated on: Tue, 04 December 2007 08:38]

Report message to a moderator

Re: where current of use [message #285448 is a reply to message #285421] Tue, 04 December 2007 11:57 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
rosee wrote on Tue, 04 December 2007 06:37

1 - UPDATE TCLIENT set name='A' where ste = r.ste AND client = r.client;
2 - UPDATE TCLIENT set name='A' where rowid=r.rowid;
3 - UPDATE TCLIENT set name='A' where current of c;


Functionally statements 2 and 3 are the same when the cursor is opened FOR UPDATE (3 must be used in conjuction with FOR UPDATE).
1 differs in that the row being updated must be found for each iteration (either a full tablescan or index access), whereas 2 and 3 go directly to the row using ROWID.
The difference in 2 and 3 is basically stylistic. Using WHERE CURRENT OF, the cursor does not have to explicitly query the ROWID.

As for locking, Oracle performs a row level lock in this case.

The best advise I can think of deals with large transactions. It useful to lock the rows of the cursor in most situations; however, if I am performing a large transaction within the CURSOR FOR LOOP, and I need to COMMIT periodically, the FOR UPDATE CANNOT be used, and in that case, I would suggest using method 1 without the FOR UPDATE.
Re: where current of use [message #285449 is a reply to message #285421] Tue, 04 December 2007 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

UPDATE TCLIENT set name='A' where ste = r.ste AND client = r.client;
UPDATE TCLIENT set name='A' where rowid=r.rowid;
UPDATE TCLIENT set name='A' where current of c;

The first one Oracle has to search in the table for the row(s), so it has to scan index and/or table and load the blocks in SGA.
The second one Oracle has to find and load the block associated to the rowid. This is done with a direct access to it (no scan on table or index).
The third one Oracle does not have to do anything, block is already in PGA.

Locks (row level ones) on select for update are the same ones as for update.

Of course this a theorical question as you should never use select for update and cursor loop for such an issue but directly use an update.

Regards
Michel
Re: where current of use [message #285451 is a reply to message #285449] Tue, 04 December 2007 13:26 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Michel Cadot wrote on Tue, 04 December 2007 10:20

This is done with a direct access to it (no scan on table or index).
The third one Oracle does not have to do anything, block is already in PGA.

Just curious...

The block goes into the PGA? I was under the impression the cursor (in the PGA) only contained a pointer to the row in the SGA.

Even if the block is in the PGA, the actual data to be manipulated still has to be found in the SGA, so does the CURRENT OF offer any performance advantage over the ROWID lookup?
Re: where current of use [message #285452 is a reply to message #285451] Tue, 04 December 2007 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, the block is in PGA.
It is loaded from SGA into PGA in order for the server process to work on it. It can't work in a block in SGA because SGA is shared.
The block does not have to be found in SGA because the block was already loaded by the loop and it is the current block as the select for update lock it so no one could modified it.

On the opposite, ROWID has to make a logical IO to search for the block in SGA and then Oracle sees that it already has this block in PGA but nevertheless it searched in the SGA and so waited, took latches, implied others waited and so on.

Regards
Michel
Re: where current of use [message #285456 is a reply to message #285452] Tue, 04 December 2007 14:01 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Interesting...is this discussed in the Oracle docs? I didn't see this level of detail in the Concepts and Performance Tuning guides. Maybe I overlooked it, or it is in a whitepaper somewhere?
Re: where current of use [message #285464 is a reply to message #285456] Tue, 04 December 2007 14:49 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if it is somewhere in the docs, maybe on Metalink.

Regards
Michel
Previous Topic: sql query
Next Topic: dbms_job.interval
Goto Forum:
  


Current Time: Fri Dec 09 15:41:56 CST 2016

Total time taken to generate the page: 0.11337 seconds