Is there any problem with doing does a straight update
with no PL/SQL ? Thats typically the most efficient
way.
hth
connor
- Rick_Cale_at_teamhealth.com wrote: > Hi DBAs
>
> Here are 2 scenario's. Which one is more efficient?
> Assume 10,000 records to update
>
> Thanks
> Rick
>
> 1. For each record update field1 with subsquery
> Assume cursor is declared
> FOR cursor_rec IN new_patients_cur LOOP
> UPDATE <table>
> SET field1 =
> (SELECT value
> FROM table
> WHERE ...)
> WHERE ....
> END LOOP;
>
> 2. For each record load nested table with data then
> update all records with
> a single FORALL
>
> t_type1 IS TABLE OF table1.field1%TYPE;
> v_type1 t_type1(10000);
>
> /* Load the PLSQL nested table */
> FOR cursor_rec IN new_patients_cur LOOP
> v_loop_ctr := v_loop_ctr + 1;
> v_type1 := cursor_rec.field1;
> END LOOP;
>
> /* INSERT all 10,000 records with on context switch
> */
> FORALL v_count IN 1...10000)
> UPDATE field1 = v_type1(v_count);
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
> INET: Rick_Cale_at_teamhealth.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Feb 04 2002 - 16:30:35 CST
