집합 기반의 접근법
- 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;