Re: Straight SQL always put perform PL/SQL?

From: <dshproperty_at_gmail.com>
Date: Mon, 17 Dec 2007 13:48:56 -0800 (PST)
Message-ID: <3f8191db-8372-4407-844a-1427b56a43f7@q77g2000hsh.googlegroups.com>

Thanks very much for your respond. I read the "write consistency' as well. I think it is the opertimizer somehow messed up, the cursor knows to use the index, while update statement choose a full table scan on a big table.

To demonstrate it, I re-create the simplified situation of our case.

---

create table test (id1 number,id2 number,id3 char(1), col1 char(2000)) pctfree 90;
(this is to mimic creating a huge table which makes full table scan expensive)
declare
v_number number :=1;
begin
loop
insert into test values (v_number, v_number + 1,'n','a'); v_number := v_number+1;
exit when v_number > 1000000;
end loop;
commit;
end;

create table test1 as select * from test where id1 < 400000; update test1 set col1='b';
update test set id3='Y' where id1 < 500; commit;

create unique index test_ind on test(id1,id2);  create bitmap index test_idx3 on test(id3); create unique index test1_ind on test1(id1,id2);

exec dbms_stats.gather_table_stats('scott', 'TEST', method_opt=>'for all indexed
columns', cascade=>TRUE);
exec dbms_stats.gather_table_stats('scott', 'TEST1', method_opt=>'for all indexed
columns', cascade=>TRUE);

SQL> declare
  2 v_count number;
  3 CURSOR test_cur IS

  4  select test.id1,test.id2,test.col1 from test, test1
  5  where test.id1=test1.id1
  6  and test.id2 = test1.id2 and test.id3='Y';

  7 BEGIN
  8 FOR test_curs IN test_cur
  9 LOOP

 10       update test
 11       set col1 = test_curs.col1
 12       where test.id1 = test_curs.id1
 13         and test.id2 = test_curs.id2
 14         and test.id3='Y';
 15     END LOOP;

 16 commit;
 17 END;
 18 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20

SQL> update test a
  2 set a.col1 = (select col1
  3 from test1 b
  4 where a.id1=b.id1
  5 and a.id2 = b.id2 and a.id3='Y')
  6 where exists
  7 (select 1 from test1 b where a.id1=b.id1   8 and a.id2 = b.id2 and a.id3='Y');

499 rows updated.
Elapsed: 00:01:27.75

PL/SQL is much faster.

Thanks very much for your help.

>
> A couple of problems with your comparison:
>
> Your index on test1.id 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 test1.id, then your pl/sql is the coded
> equivalent of an update to a join view:
>
> update (
> select test.id,test.col1 from test,test1
> where test.id=test1.id
> )
> set
> 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.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Mon Dec 17 2007 - 15:48:56 CST