Re: Update
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <ranko.mosic@xxxxxxxxx>, "freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 Mar 2007 00:26:49 +0100
Hi Ranko,
I need to update large table sub_svc_parm ( over billion rows). This
is PL/SQL block that does it:
I want to rewrite so it is executed as single SQL update statement.
Any suggestions ?
First of all check if the execution plan of the select performs well.
SELECT /*+ FIRST_ROWS */ b.sub_svc_id, d.destination_group_id
I never use FIRST_ROWS hint especially if the intention is to process all
rows returned by the query. But the plan could be perfectly suitable. I
assume that the expectation is that a relatively few rows would be updated.
In that case could your "do it yourself nested loop update" adequate. If the
intention is only to avoid scanning the large table and a non trivial part
of it should be updated, you'll with a high probability face a problem.
I'd expect a single update statement either using correlated subqueries or
updateable join view (this would be preferable in case of mass update, but
probally requires a temporary table with the result of your cursor
statement) would yield a better performance.
Regards,
Jaromir
----- Original Message -----
From: "Ranko Mosic" <ranko.mosic@xxxxxxxxx>
To: "freelists" <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 12, 2007 7:37 PM
Subject: Update
--
//www.freelists.org/webpage/oracle-l
Other related posts:
