Frank van Bortel wrote:
> xhoster_at_gmail.com schreef:
>> niy38_at_hotmail.com wrote:
>>> how can that happen?
>>
>> How can *what* happen? As you don't quote any context, I have no idea
>> what
>> you are talking about.
>>
>>> for rec in (select * from a_big_table where a=1)
>>> loop
>>> update a_big_table set a=0;
>>> commit;
>>> end loop
>>
>> What on earth do you think this is going to do?
>>
>> Since there is no where clause on your update, you are going to update
>> every single row in the table. And you are doing to do it once for every
>> row in the table which originally had a=1.
>>
>> Why not just do:
>>
>> update a_big_table set a=0;
>>
>>
>> Xho
>>
>
> I think Xho hit the nail on the head here.
> No other reply goes here, but this is the main culprit.
> niy38: do you have any idea what you are doing? Is it really
> necessary to update some 7,200,000,000,000 records?
> (600k * 1.2M).
>
> Consider a "big" table on my single-core PC:
> SQL> create table big_table as select * from all_objects;
> Table created.
>
> SQL> insert into big_table select * from big_table;
> 24519 rows created.
>
> SQL> /
> 49038 rows created.
> [repeat a few times...]
>
> SQL> exec dbms_stats.gather_table_stats('HR','BIG_TABLE');
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:31.11
> SQL> select count(*) from big_table;
>
> COUNT(*)
> ----------
> 1569216
>
> Elapsed: 00:00:00.50
> SQL> update big_table set status='A';
> 1569216 rows updated.
>
> Elapsed: 00:00:50.53
>
> SQL> rollback;
> Rollback complete.
>
> Let's see how your statement (modified to run once!) works out:
> declare
> cursor c_b is select * from big_table for update;
> r_b c_b%rowtype;
> begin
> for r_b in c_b loop
> update big_table set status='A' where current of c_b;
> end loop;
> end;
> SQL> /
> PL/SQL procedure successfully completed.
> Elapsed: 00:02:58.74
>
> We went from 51 to 179 seconds; well over triple the time!
>
> Bottom line: do in SQL what you can do in SQL.
My assumption was that the demo cursor loop code was a quickly
thrown together example. If it is actual code then Xho is absolutely
correct and the OP needs to take a basic SQL course.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu May 11 2006 - 11:13:23 CDT
