Re: 01555 and select statement

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Thu, 11 May 2006 13:37:42 +0200
Message-ID: <e3v7it$qkh$1@news5.zwoll1.ov.home.nl>

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.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Thu May 11 2006 - 06:37:42 CDT