Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Straight SQL always put perform PL/SQL?

Re: Straight SQL always put perform PL/SQL?

From: Jonathan Lewis <>
Date: Thu, 13 Dec 2007 18:41:09 -0000
Message-ID: <>

<> wrote in message
> 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 )
> where exists
> (select 1 from test1
> where;
> 399999 rows updated.
> Elapsed: 00:04:15.01
> SQL> declare
> 2 v_count number;
> 3 cursor test_curs is
> 4 select,test.col1 from test,test1
> 5 where;
> 6 begin
> 7 for test_cur in test_curs
> 8 loop
> 9 update test
> 10 set col1=test_cur.col1
> 11 where;
> 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
> will be able to use index.
> Any comments will be highly appreciated

A couple of problems with your comparison:

Your index on is not unique, so the scalar subquery used in the update could (in theory) return multiple rows. In the SQL case this would crash the query. The pl/sql code would "multiply" rows from test, and update them multiple times, which means you don't get a deterministic result.

Assume you have the appropriate unique index in place on, then your pl/sql is the coded equivalent of an update to a join view:

update (

        select,test.col1 from test,test1


    id = col1

However, whilst this update is running, conflicting updates could get in ahead of the N million row update, do a quick change and commit.

In the case of the SQL, the statement would have to rollback if it hit a conflict, and start again with a 'write consistent' update - effectively doing a 'select for update / update' the second time around. (See Ask Tom, search for 'write consistency').

In the case of the PL/SQL, the code will simply be doing a read-consistent query, and updating rows identified by id. This allows you to get a 'lost update' from the conflict (a change made by the interfering update gets over-written by the pl/sql loop).

In your case of the 1 hour vs. 5 minutes, one of the causes of extra time may have been an implicit rollback and restart.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Thu Dec 13 2007 - 12:41:09 CST

Original text of this message