| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Straight SQL always put perform PL/SQL?
On Dec 21, 5:15 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
wrote:
> <dshprope..._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 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- Hide quoted text -
>
> - Show quoted text -
Showing the pertinent details from using my last attempt at rewriting your query and Jonathan's re-write we see:
SQL> declare
2 v_count number;
3 CURSOR test_cur IS
4 select test1.id1,test1.id2,test1.col1 from test, test1
5 where test1.id1=test.id1
6 and test1.id2 = test.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;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.46
SQL>
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 (a.id1, a.id2) in
499 rows updated.
Elapsed: 00:00:00.10
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 295 | 35990 | 180 (2)| 00:00:03 | | 1 | UPDATE | TEST | | | | | | 2 | NESTED LOOPS | | 295 | 35990 | 180 (2)| 00:00:03 | | 3 | TABLE ACCESS BY INDEX ROWID | TEST | 744 | 83328 | 68 (3)| 00:00:01 | | 4 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | TEST_IDX3 | | | | | |* 6 | INDEX UNIQUE SCAN | TEST1_IND | 1 | 10 | 1 (0)| 00:00:01 | |* 7 | FILTER | | | | | | | 8 | TABLE ACCESS BY INDEX ROWID | TEST1 | 1 | 112 | 1 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | TEST1_IND | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
5 - access("A"."ID3"='Y')
6 - access("A"."ID1"="B"."ID1" AND "A"."ID2"="B"."ID2")
7 - filter(:B1='Y')
9 - access("B"."ID1"=:B1 AND "B"."ID2"=:B2)
Statistics
1 recursive calls
103 db block gets
2582 consistent gets
0 physical reads
162800 redo size
396 bytes sent via SQL*Net to client
505 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
499 rows processed
SQL>
SQL> update test a
2 set a.col1 = (
3 select col1 4 from test1 b 5 where b.id1 = a.id1 6 and b.id2 = a.id2 7 ) 8 where a.id3 = 'Y' 9 and exists ( 10 select 1 11 from test1 b 12 where b.id1 = a.id1 13 and b.id2 = a.id2 14 ) 15 ;
499 rows updated.
Elapsed: 00:00:00.09
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | 122 | 180 (2)| 00:00:03 | | 1 | UPDATE | TEST | | | | | | 2 | NESTED LOOPS SEMI | | 1 | 122 | 180 (2)| 00:00:03 | | 3 | TABLE ACCESS BY INDEX ROWID | TEST | 744 | 83328 | 68 (3)| 00:00:01 | | 4 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | TEST_IDX3 | | | | | |* 6 | INDEX UNIQUE SCAN | TEST1_IND | 1 | 10 | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | TEST1 | 1 | 112 | 1 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | TEST1_IND | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
5 - access("A"."ID3"='Y')
6 - access("B"."ID1"="A"."ID1" AND "B"."ID2"="A"."ID2")
8 - access("B"."ID1"=:B1 AND "B"."ID2"=:B2)
Statistics
1 recursive calls
101 db block gets
2582 consistent gets
0 physical reads
157824 redo size
397 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
499 rows processed
SQL> So SQL is faster than PL/SQL, when you write it correctly.
Thanks, Jonathan, for providing the correct query.
David Fitzjarrell Received on Fri Dec 21 2007 - 09:02:26 CST
 |
 |