On 10.12.2007 19:08, Ed Prochak wrote:
> On Dec 10, 9:51 am, dshprope..._at_gmail.com wrote:
>> http://tkyte.blogspot.com/2006/10/slow-by-slow.html
>> In there Tom Kytes says:
>> * You should do it in a single SQL statement if at all possible.
>> * If you cannot do it in a single SQL Statement, then do it in PL/SQL.
>>
>> I was a true beliver of that until this case. We have a huge table,
>> and we need to update it with
>> values from another table.
>>
>> Let me give you an example
>> create table test (id number,col1 varchar2(20)) pctfree 99;
>> (this is only for creating a huge table with lots of columns)
>> declare
>> v_number number :=1;
>> begin
>> loop
>> insert into test values (v_number, 'a');
>> v_number := v_number+1;
>> exit when v_number > 1000000;
>> end loop;
>> commit;
>> end;
>>
>> create table test1 as select * from test where id < 400000;
>> update test1 set col1='b';
>> commit;
>>
>> create index test_ind on test(id);
>> create index test1_ind on test1(id);
>> analyze table test compute statistics;
>> analyze table test1 compute statistics;
>> SQL> update test
>> set col1=(select col1 from test1
>> where test.id=test1.id )
>> where exists
>> (select 1 from test1
>> where test.id=test1.id);
>> 399999 rows updated.
>> Elapsed: 00:04:15.01
>> SQL> declare
>> 2 v_count number;
>> 3 cursor test_curs is
>> 4 select test.id,test.col1 from test,test1
>> 5 where test.id=test1.id;
>> 6 begin
>> 7 for test_cur in test_curs
>> 8 loop
>> 9 update test
>> 10 set col1=test_cur.col1
>> 11 where id=test_cur.id;
>> 12 end loop;
>> 13 commit;
>> 14 end;
>> 15 /
>> PL/SQL procedure successfully completed.
>> Elapsed: 00:04:30.82
>>
>> Here the difference is not huge yet, but in our case, single SQL will
>> take an hour to finish, while
>> PL/SQL only take 5 min. The bigger the table test, the huger the
>> difference.
>>
>> And the reason is simple, as there will be 40% of rows needs to be
>> updated, Oracle will pick full
>> table scan of table test(a huge table). While in PL/SQL, "UPDATE TEST
>> SET COL1=:B2 WHERE ID=:B1"
>> will be able to use index.
>>
>> Any comments will be highly appreciated
>
> Yet another example of the fundamental performance rule:
> you never know the results until you test it.
>
> And no matter how up-to-date the statistics are, you might be able to
> outdo the optimizer. This reflects the performance rule: know thy
> data. You know what to expect, while the optimizer can only
> estimate.
>
> and since I seem to be in a proverbial mood I'll close with:
> there's always an exception to any rule.
... except the one where there is no exception.
:-)
SCNR
robert
Received on Mon Dec 10 2007 - 12:23:04 CST
