Re: Update

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_at_gmail.com> To: "freelists" <oracle-l_at_freelists.org> Sent: Monday, March 12, 2007 7:37 PM
Subject: Update

--
http://www.freelists.org/webpage/oracle-l