Re: Straight SQL always put perform PL/SQL?

On Dec 17, 4:48 pm, dshprope..._at_gmail.com wrote:
> 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

You replied on this thread, yet still did not respond to such basic questions as to edition and version in your environment?

Are you aware that bulk collection could be occurring underneath the covers without you being aware of it ... in newer versions?

Its "your case" not "our case" as you could not be bothered to provide enough info for others to produce a test case as yours is not reproducible without knowing the environment.

How about starting it over again with a description of the environment for your test case along the lines of:

Oracle database server 10g release 2 standard edition one 32 bit with the 10.2.0.3 patchset applied, 10.2.0.3 p14 patch applied on w2k3 server release 2 service pack 1.

good luck.

-bdbafh Received on Mon Dec 17 2007 - 16:51:17 CST