꿈꾸는 개발자, DBA 커뮤니티 구루비

집합 기반의 접근법
집합 기반의 접근법 데모 (10.2.0.4)
집합 기반의 접근법 데모 (9.2.0.1)
중복 제거 데모
1. 절차 기반
2. 집합 기반

집합 기반의 접근법

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;

순수 절차 기반


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
      ...

절차 기반 (with 집합 기반)


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