- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3901184&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
집합 기반의 접근법
- 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
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3901184&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.