by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

가능한 한 적게 작성하라

Added by 오화균, last edited by 오화균 on 12월 10, 2009



집합 기반의 접근법

  • RDBMS 에서는 집합 기반의 접근법이 동작하지 않는 다는 것이 판명된 이후에 절차 기반의 코드를 사용해야 한다.
  • SQL 로 처리할 수 없는 경우에만 PL/SQL 을 사용하는 것을 목표로 해야 한다.
  • 코드를 작성할 때에 루틴을 화면 크기에 맞추어야 한다.
절차 기반 집합 기반 
begin
  for x in ( select * from table@remote_db )
  loop
    insert into table ( c1, c2, ... ) values ( x.c1, x.c2, ... );
  end loop;
end;

insert into table (c1, c2, ...)
select c1, c2, ... from table@remote_db;
순수 절차 기반 절차 기반 (with 집합 기반) 
for a in ( select * from t1 )
loop
  for b in ( select * from t2 where t2.key = t1.key )
  loop
    for c in ( select * from t3 where t3.key = t2.key )
    loop
      ...

for x in ( select * from t1, t2, t3 
                   where t1.key = t2.key
                     and t2.key = t3.key ) loop

집합 기반의 접근법 데모 (10.2.0.4)

SQL> create table t1 ( a int primary key, y char(80) );

테이블이 생성되었습니다.

SQL> create table t2 ( b int primary key, a references t1, y char(80) );

테이블이 생성되었습니다.

SQL> create index t2_a_idx on t2(a);

인덱스가 생성되었습니다.

SQL> create table t3 ( c int primary key, b references t2, y char(80) );

테이블이 생성되었습니다.

SQL> create index t3_b_idx on t3(b);

인덱스가 생성되었습니다.

SQL> insert into t1
select rownum, 'x' from all_objects where rownum <= 1000;  2

1000 개의 행이 만들어졌습니다.

SQL> insert into t2
select rownum, mod(rownum,1000)+1, 'x' from all_objects where rownum <= 5000;  2

5000 개의 행이 만들어졌습니다.

SQL> insert into t3
select rownum, mod(rownum,5000)+1, 'x' from all_objects;  2

40875 개의 행이 만들어졌습니다.

SQL> exec runstats_pkg.rs_start;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> begin
  2  for i in 1 .. 1000
  3  loop
  4    for x in ( select /*+ use_nl(t1 t2 t3) */ t1.a t1a, t1.y t1y,
  5                      t2.b t2b, t2.a t2a, t2.y t2y,
  6                      t3.c t3c, t3.b t3b, t3.y t3y
  7                 from t1, t2, t3
  8                where t1.a = i
  9                  and t2.a (+) = t1.a
 10                  and t3.b (+) = t2.b )
  loop
 11   12      null;
 13    end loop;
 14
 15  end loop;
 16  end;
 17  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec runstats_pkg.rs_middle;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> begin
  2  for i in 1 .. 1000
  3  loop
  4    for a in ( select t1.a, t1.y from t1 where t1.a = i )
  5    loop
  6      for b in ( select t2.b, t2.a, t2.y from t2 where t2.a = a.a )
  7      loop
  8        for c in ( select t3.c, t3.b, t3.y from t3 where t3.b = b.b )
  9        loop
 10          null;
 11        end loop;
 12      end loop;
 13    end loop;
 14  end loop;
 15  end;
 16  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 574 hsecs
Run2 ran in 701 hsecs
run 1 ran in 81.88% of the time

Name                                  Run1        Run2        Diff
LATCH.shared pool simulator             83       1,097       1,014
LATCH.cache buffers chains         113,949     116,802       2,853
STAT...buffer is pinned count       42,875      39,875      -3,000
STAT...consistent gets              58,015      61,017       3,002
STAT...consistent gets from ca      58,015      61,017       3,002
STAT...session logical reads        58,035      61,040       3,005
STAT...consistent gets - exami       3,005       7,006       4,001
STAT...execute count                 1,005       7,005       6,000
STAT...calls to get snapshot s       1,001       7,001       6,000
STAT...recursive calls               1,003       7,007       6,004

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
119,507     123,964       4,457     96.40%

PL/SQL 처리가 정상적으로 완료되었습니다.

집합 기반의 접근법 데모 (9.2.0.1)

Run1 ran in 421 hsecs
Run2 ran in 479 hsecs
run 1 ran in 87.89% of the time
        
Name                                  Run1        Run2        Diff
LATCH.cache buffer handles           2,004           0      -2,004
STAT...buffer is not pinned co     105,200     100,204      -4,996
LATCH.shared pool                    1,269       7,056       5,787
STAT...no work - consistent re      71,589      65,590      -5,999
STAT...calls to get snapshot s       1,001       7,001       6,000
STAT...execute count                 1,005       7,005       6,000
STAT...consistent gets - exami       3,007       9,010       6,003
LATCH.cache buffers chains         164,385     154,931      -9,454
LATCH.library cache                  2,459      14,091      11,632
LATCH.library cache pin              2,240      14,062      11,822
STAT...recursive calls              28,799      40,803      12,004
        
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
179,633     195,649      16,016     91.81%

중복 제거 데모

  • 절차 코드를 볼 때는 언제나 집합 기반의 방법이 없을지 고민 필요
    절차 기반 집합 기반 
    insert into t ( c1, c2, .... )
select c1, c2, ....
  from t1, t2, t3, t4, ....
 where ..join conditions..;

loop
  delete from t
   where (c1, c2) in (select c1, min(c2)
                        from t
		       group by c1
		      having count(1) > 1);
  exit when sql%rowcount = 0;
end loop;

    insert into t ( c1, c2, .... )
select c1, c2, ......
  from 
       (select c1, c2, .... ,
               max(c2) over ( partition by c1 ) max_c2,
	       count(*) over ( partition by c1, c2 ) cnt
	  from t1, t2, t3, t4, ....
	 where .... )
 where c2 = max_c2
   and cnt = 1;

Runstats.sql

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.