Re: Straight SQL always put perform PL/SQL?

<dshproperty_at_gmail.com> wrote in message news:3f8191db-8372-4407-844a-1427b56a43f7_at_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.
>
>

I note that you didn't try rewriting the query as an update join view, which is still the fairest comparison with rewriting the PL/SQL to use a cursor loop driven by a join.

However, it might help if you wrote the right query for your new example:

update test a
 set a.col1 = (
  select col1
  from test1 b
  where b.id1 = a.id1
  and b.id2 = a.id2
 )
where a.id3 = 'Y'
and exists (
  select 1
  from test1 b
  where b.id1 = a.id1
  and b.id2 = a.id2
 )
;

I think you want to update TEST (the large table) where id3 = 'Y'. You want to update it with the matching value from TEST1 (the small table) where id1 and id2 match.

Your version of the query put the "test.id3 = 'Y'" predicate in the subqueries - which made it impossible for Oracle to unnest the where clause subquery, or transform the query into a semi join.

The join view update would look like this: update
 (
 select
  a.col1 cola, b.col1 colb
 from
  test a, test1 b
 where
  a.id1 = b.id1
 and a.id2 = b.id2
 and a.id3 = 'Y'
 ) v
set cola = colb
;

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html