Mooyoung2020

Mooyoung2020

SQL Tunner

Oracle OUTER JOIN 정리 2

Updated:

예제 테이블 생성

  • 대구지점 테이블
CDATE CUSTOMER SALES
20120501 112054 1
20120502 112054 2
20120504 112054 4
20120505 112054 5
20120501 112055 6
20120503 112055 8
20120505 112055 10
20120504 112055 9
20120502 112056 12
20120504 112056 14
20120503 112056 13
20120505 112056 15
  • 광주지점 테이블
CDATE CUSTOMER SALES
20120502 112056 12
20120503 112056 13
20120504 112056 14
20120505 112056 15
20120501 112057 16
20120502 112057 17
20120505 112057 20
20120504 112057 19
20120501 112058 21
20120503 112058 23
20120502 112058 22
20120504 112058 24

1. 오라클 LEFT OUTER JOIN 을 스칼라쿼리로 변경

1편에 이어 OUTER JOIN을 스칼라쿼리형태로 변경하는것을 알아봅니다. (+)표시가 붙은 테이블이 Select List절로 올라가면서 (+) 표시가 없어진다. 2개 SQL 형태는 동일한 결과값을 보장합니다. 

- Left Outer 조인
 SELECT A.ID,B.NAME
  FROM FULLOUTER1 A,   FULLOUTER2 B
  WHERE A.ID=B.ID(+);

- 스칼라 서브쿼리
SELECT A.ID,(SELECT B.NAME FROM FULLOUTER2 B
             WHERE A.ID = B.ID) NAME
FROM FULLOUTER1 A;
A.ID B.ID A.NAME B.NAME
1001 1001 박무영 박무영
1002 1002 이무영 이무영
1005   최무룡  
1003   김지미  
1004   이연희  

2. 실전예제 1

위의 샘플데이타를 가지고 아래의 요구사항에 맞는 SQL을 작성해봅니다.
요구사항) 각 지점이 동일한 고객과 거래할수 있습니다. 2012년 5월1일부터 2012년 5월5일까지 각 고객별 1지점과 2지점을 동시에 보여주자. 최종결과는 아래와 같이 보여주면 됩니다.

고객 대구 광주
112058 90  
112057 72  
112056 54 54
112055   33
112054   12

가. 실전예제 1 Solution 

가. 조인 기준열 만들기
select customer
from test_u1 
where cdate between '20120501' and '20120505'
union 
select customer
from test_u2
where cdate between '20120501' and '20120505';

나. 데이터를 가져오는 SQL (칼럼결정)
select customer, sum(sales) sales1
from test_u1
where cdate between '20120501' and '20120505'
group by customer;

다. 가항과 나항을 합쳐 서로 연결하기
Select  a.customer as 고객, sales1 as 대구, sales2 as 광주  
from 
      (select customer  --조인 기준열 sql
      from 대구
      where cdate between '20120501' and '20120505'
      union 
      select customer
      from 광주
      where cdate between '20120501' and '20120505')  A,
      (select customer, sum(sales) sales1 –-데이터 가져오는 sql
      from 대구
      where cdate between '20120501' and '20120505'
      group by customer ) B,
      (select customer, sum(sales) sales2 –-데이터 가져오는 sql
      from 광주
      where cdate between '20120501' and '20120505'
      group by customer ) C
where a.customer = b.customer(+)  --연결방법
 and  a.customer = c.customer(+) 
Order by a.customer;

3. 실전예제 2

위의 인라인뷰 형태의 SQL을 스칼라서브쿼리 형태의 SQL로 변경해봅니다.
인라인뷰에서 구조(기준열)을 만들고 스칼라서브쿼리에서 데이터를 만들어도 아웃터조인과 동일하다. 즉 스칼라서브쿼리는 인라인뷰에 올라오는 레코드의 연결조건이 NULL이라도 처리되는 특징이 있습니다.

select  a.customer 고객,    
    (select sum(b.sales) sales1 
     from 대구 b
     where a.customer = b.customer
     and b.cdate between '20120501' and '20120505'
     group by b.customer) 대구,  
    (select sum(c.sales) sales2 
     from 광주 c
     where a.customer = c.customer
     and c.cdate between '20120501' and '20120505'
     group by c.customer) 광주
from 
   (select customer  
    from 대구
    where cdate between '20120501' and '20120505'
    union 
    select customer
    from 광주
    where cdate between '20120501' and '20120505') a    
Order by a.customer;

4. 실전예제 3

UNION ALL을 사용하여 Full Outer Join대신 사용할수 있습니다.
이때의 UNION ALL은 동일 테이블이 아니라 다른 테이블을 논리적으로 합쳐서 조회하는 방식이라 좋은 성능을 발휘할수 있습니다.

select customer 고객, sum(sales1) 대구, sum(sales2) 광주
from 
   (select customer, sales sales1, 0 sales2
    from 대구
    where cdate between '20120501' and '20120505'
    union all
    select customer, 0 sales1, sales sales2  
    from 광주
    where cdate between '20120501' and '20120505'  )
Group by customer
order by customer;

5. 실전예제 4

Full Outer Join을 이용하여 동일한 결과값을 만들어봅니다.

select nvl(b.customer, c.customer) 고객, sales1 대구, sales2 광주
from 
    (select B.customer, sum(sales) sales1
     from 대구 B    
     where cdate between '20120501' and '20120505'
     group by customer ) b FULL OUTER JOIN
     (select C.customer, sum(sales) sales2
     from 광주 C      
     where cdate between '20120501' and '20120505'
     group by customer ) c
ON B.CUSTOMER = C.CUSTOMER
Order by B.customer;

6. 실전예제 성능비교

실전예제 1, 2, 3, 4번 SQL의 성능을 10046트레이스를 사용하여 확인해봅니다. 각각 50만건의 테스트 데이트를 생성하고 성능을 확인해본 결과 1번 보다 2번 SQL형태가 12,752블럭 대 325,176블럭으로 30배이상 차이가 난다. 왜 이럴까요? 3번 SQL의 스칼라서브쿼리안의 SUM이 문제를 발생시킨것입니다.

1번 SQL은 인라인뷰에서 데이타를 1건씩 공급받아 스칼라서브쿼리에서 SUM을 수행하므로 당연히 부하가 많이 방생합니다. 스칼라서브쿼리안에 그룹함수를 사용하게 되면 많은 부하를 발생시켜 속도가 저하되는 원인이됩니다. 스칼라서브쿼리는 단순 코드 변경용으로 OLTP시스템에서만 사용하자.

화면이 매우 빈번하게 사용하는 OLTP시스템의 SQL이라면 그룹함수가 없는 경우에는 스칼라서브쿼리가 OUTER JOIN보다 성능이 더 좋게 나옵니다. 반대로 DW성 배치처리라면 조인형태가 스칼라서브쿼리 형태보다 성능이 잘 나옵니다. 절대적인 기준은 아니지만 개념적으로는 맞는 이야기입니다. 하여간 스칼라 서브쿼리에 그룹함수를 넣지 않도록 하고, 만일 그렇게 만들어진 SQL이 있습니다면 3, 4번 처럼만 변경해도 훨씬 성능이 좋아질것입니다. 스칼라서브쿼리의 사용방법은 다음에 포스팅하도록 하겠습니다.

3번 4번 SQL은 각각 Full Outer Join, Union all로 형태변환을 한 SQL인데 10046 트레이스를 확인해보면 IO 블럭수가 6376블럭으로 1번 SQL의 절반으로 확 줄었다. 왜냐하면 Full Outer Join은 native hash full Outer Join 기능이 11g DB부터 내부적으로 활성화되어 1번 SQL처럼 동일 테이블에 2번씩 4번 접근해야 하는것을 동일 테이블에 한번씩, 2번만 접근하도록 기능이 개선되었기 때문입니다.

UNION ALL을 사용했을때에는 다른 테이블을 논리적으로 1개의 테이블로 수직으로 붙이는 개념으로 사용한것으로 테이블에 1번씩 총 2번 접근하였기 때문에 3, 4번 SQL의 IO블럭 총 개수가 6376개로 동일하게 나온것입니다.

가. 1번 SQL : 인라인뷰형태의 OUTER join 형태
실행계획
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     50   SORT ORDER BY (cr=12752 pr=0 pw=0 time=0 us cost=23006 size=65000000 card=1000000)
     50    HASH JOIN RIGHT OUTER (cr=12752 pr=0 pw=0 time=98 us cost=7469 size=65000000 card=1000000)
     25     VIEW  (cr=3188 pr=0 pw=0 time=24 us cost=918 size=650 card=25)
     25      HASH GROUP BY (cr=3188 pr=0 pw=0 time=0 us cost=918 size=350 card=25)
 500000       TABLE ACCESS FULL RT3 (cr=3188 pr=0 pw=0 time=160224 us cost=896 size=7000000 card=500000)
     50     HASH JOIN RIGHT OUTER (cr=9564 pr=0 pw=0 time=98 us cost=6545 size=39000000 card=1000000)
     25      VIEW  (cr=3188 pr=0 pw=0 time=48 us cost=918 size=650 card=25)
     25       HASH GROUP BY (cr=3188 pr=0 pw=0 time=24 us cost=918 size=350 card=25)
 500000        TABLE ACCESS FULL RT3 (cr=3188 pr=0 pw=0 time=160224 us cost=896 size=7000000 card=500000)
     50      VIEW  (cr=6376 pr=0 pw=0 time=49 us cost=5621 size=13000000 card=1000000)
     50       SORT UNIQUE (cr=6376 pr=0 pw=0 time=0 us cost=5621 size=9000000 card=1000000)
1000000        UNION-ALL  (cr=6376 pr=0 pw=0 time=516830 us)
 500000         TABLE ACCESS FULL RT3 (cr=3188 pr=0 pw=0 time=162912 us cost=896 size=4500000 card=500000)
 500000         TABLE ACCESS FULL RT3 (cr=3188 pr=0 pw=0 time=163936 us cost=896 size=4500000 card=500000)
  • 10046 트레이스
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 6 1.125 1.123 0 12752 0 50
Total 8 1.125 1.124 0 12752 0 50 
나. 2번 SQL : 스칼라 서브쿼리 형태<br/>
실행계획
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     25   SORT GROUP BY NOSORT (cr=159400 pr=0 pw=0 time=0 us cost=893 size=14 card=1)
 500000  TABLE ACCESS FULL RT3 (cr=159400 pr=0 pw=0 time=1958118 us cost=893 size=140000 card=10000)
     25   SORT GROUP BY NOSORT (cr=159400 pr=0 pw=0 time=0 us cost=893 size=14 card=1)
 500000  TABLE ACCESS FULL RT3 (cr=159400 pr=0 pw=0 time=2065119 us cost=893 size=140000 card=10000)
     50   SORT ORDER BY (cr=325176 pr=0 pw=0 time=0 us cost=10391 size=13000000 card=1000000)
     50    VIEW  (cr=6376 pr=0 pw=0 time=98 us cost=5621 size=13000000 card=1000000)
     50     SORT UNIQUE (cr=6376 pr=0 pw=0 time=49 us cost=5621 size=9000000 card=1000000)
1000000      UNION-ALL  (cr=6376 pr=0 pw=0 time=511326 us)
 500000       TABLE ACCESS FULL RT3 (cr=3188 pr=0 pw=0 time=161248 us cost=896 size=4500000 card=500000)
 500000       TABLE ACCESS FULL RT3 (cr=3188 pr=0 pw=0 time=160224 us cost=896 size=4500000 card=500000)
  • 10046 트레이스
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 6 8.703 8.699 0 325176 0 50
Total 8 8.703 8.702 0 325176 0 50 
다. 3번 SQL : UNION ALL 사용
실행계획
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     50   SORT ORDER BY (cr=6376 pr=0 pw=0 time=0 us cost=1838 size=2600 card=50)
     50    VIEW  VW_FOJ_0 (cr=6376 pr=0 pw=0 time=98 us cost=1837 size=2600 card=50)
     50     HASH JOIN FULL OUTER (cr=6376 pr=0 pw=0 time=98 us cost=1837 size=2600 card=50)
     25      VIEW  (cr=3188 pr=0 pw=0 time=24 us cost=918 size=1300 card=50)
     25       HASH GROUP BY (cr=3188 pr=0 pw=0 time=0 us cost=918 size=700 card=50)
 500000        TABLE ACCESS FULL RT3 (cr=3188 pr=0 pw=0 time=158561 us cost=896 size=7000000 card=500000)
     25      VIEW  (cr=3188 pr=0 pw=0 time=24 us cost=918 size=1300 card=50)
     25       HASH GROUP BY (cr=3188 pr=0 pw=0 time=0 us cost=918 size=700 card=50)
 500000        TABLE ACCESS FULL RT3 (cr=3188 pr=0 pw=0 time=166623 us cost=896 size=7000000 card=500000)
  • 10046 트레이스
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 6 0.484 0.474 0 6376 0 50
Total 8 0.484 0.474 0 6376 0 50 
라. 4번 SQL : Full Outer Join
실행계획
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     50   SORT ORDER BY (cr=6376 pr=0 pw=0 time=0 us cost=1838 size=2600 card=50)
     50    VIEW  VW_FOJ_0 (cr=6376 pr=0 pw=0 time=98 us cost=1837 size=2600 card=50)
     50     HASH JOIN FULL OUTER (cr=6376 pr=0 pw=0 time=98 us cost=1837 size=2600 card=50)
     25      VIEW  (cr=3188 pr=0 pw=0 time=24 us cost=918 size=1300 card=50)
     25       HASH GROUP BY (cr=3188 pr=0 pw=0 time=0 us cost=918 size=700 card=50)
 500000        TABLE ACCESS FULL RT3 (cr=3188 pr=0 pw=0 time=158561 us cost=896 size=7000000 card=500000)
     25      VIEW  (cr=3188 pr=0 pw=0 time=24 us cost=918 size=1300 card=50)
     25       HASH GROUP BY (cr=3188 pr=0 pw=0 time=0 us cost=918 size=700 card=50)
 500000        TABLE ACCESS FULL RT3 (cr=3188 pr=0 pw=0 time=166623 us cost=896 size=7000000 card=500000)
  • 10046 트레이스
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 6 0.484 0.474 0 6376 0 50
Total 8 0.484 0.474 0 6376 0 50

7. 결언

OUTER JOIN 기능은 모든 개발자들이 필히 알아야하는 필수적인 기술입니다. 이번 칼럼부터는 본격적으로 OUTER JOIN의 SQL형태변환과 성능비교를 시작하였습니다. 그리고 이 칼럼에 나오는 4가지 SQL형태와 변경방법, 그리고 성능비교는 중요한 이야기이므로 꼭 기억하여 현장에서 사용해보길 바랍니다. 다음 칼럼에서도 여러가지 SQL형태변환과 성능비교를 하여 우리가 어떤 SQL형태를 사용하여야 할지 알아보겠습니다.

You May Also Enjoy