SQL | FROM 서브쿼리 인라인뷰 사용 성능향상 튜닝

쿼리 가이드 라인 

 

가급적 WHERE 조건에서는 인덱스 컬럼을 모두 사용한다.

---

인덱스를 만들어 놓더라도 WHERE 조건을 어떻게 명시하느냐에 따라 옵티마이저가 인덱스를 사용할 수도 있고 사용하지 않을 수도 있다.

예를 들어 A와 B라는 컬럼의 인덱스를 만들었는데, WHERE 조건에서 A 컬럼만 사용한다면 인덱스를 타지 않게 된다.

 

예)

CONTRACT 테이블에서 CONTRACT_NO, CONTRACT_REV 컬럼이 CON_NO_IDX 인덱스로 만들어져 있을  때, 아래와 같은 쿼리는 인덱스를 사용하지 않고 FULL SCAN을 하게 된다.

SELECT *
FROM CONTRACT
WHERE CONTRACT_REV ='1'

아래와 같이 사용하여야 한다.

SELECT *
FROM CONTRACT
WHERE CONTRACT_NO = '900000'
AND CONTRACT_REV = '1'

 

인덱스 컬럼에 사용하는 연산자는 가급적 동등 연산자(=)를 사용하라.

---

인덱스 컬럼을 WHERE조건에 모두 명시하였더라도 LIKE와 같은 연산자를 사용하면 인덱스 효율이 떨어진다.

LIKE 외에도 IS NULL, IS NOT NULL, NOT IN 등이 사용되었을 경우에도 마찬가지 현상이 발생한다.

 

인덱스 컬럼은 변형하여 사용하지 않도록 한다.

---

WHERE 조건에 인덱스 컬럼을 사용했고, 동등 연산자를 사용했다 하더라도 인덱스 컬럼에 변형을 가하게 되면 인덱스를 사용하지 못한다.

SELECT *
FROM CONTRACT
WHERE SUBSTR(CONTRACT_NO, 1,1,) = '9'
AND CONTRACT_REV = '1'

 

컬럼에 변형을 가하였을 때

SELECT *
FROM CONTRACT
WHERE CREATOR_ID LIKE 'KKK%'

SELECT *
FROM CONTRACT
WHERE SUBSTR(CREATOR_ID, 1, 3) = 'KKK'

CREATOR_ID가 UNIQUE 인덱스일 경우, 첫 번째 문장은 LIKE를 사용하여 INDEX ROWID SCAN이 아닌 INDEX RANGE SCAN 방식을 사용하게 된다.

하지만 두 번째는 컬럼자체에 변형을 가했기 때문에 FULL SCAN을 하게 된다.

 

 

 

 

 

 

OR 보다는 AND를 사용해라.

---

옵티마이저의 OR-Expansion 처리는 OR 연산자로 연결된 쿼리를 UNION ALL로 변환하므로 OR 보다는 AND를 사용해야 성능 좋은 쿼리를 작성할 수 있다.

 

 

그룹핑 쿼리를 사용할 경우 가급적 HAVING 보다는 WHERE 절에서 데이터를 필터링하라.

---

그룹핑 쿼리 처리순서는 WHERE 조건이 먼저 처리되므로 가급적 필터링 할 대상은 WHERE 조건에서 처리할 수 있게 쿼리를 작성하도록 한다.

HAVING 절은 이미 WHERE 절에서 처리된 로우들을 대상으로 조건을 감시하기 때문에 좋은 성능을 발휘하기가 힘들다.

 

 

DISTINCT는 가급적 사용하지 않는다.

---

DISTINCT는 키워드 내부적으로 정렬 작업을 수반하기 때문에 꼭 필요한 경우가 아니라면 사용하지 않는다.

 

 

IN, NOT IN 대신 EXISTS 와 NOT EXISTS를 사용하라

---

IN 이나 NOT IN 연산자 보다는 EXISTS 나 NOT EXISTS를 사용하는 것이 더 좋은 성능을 발휘한다. 

 

IN 사용

SELECT A.*
FROM CONTRACT A, CONTRACTOR B
WHERE A.CONTRACT_NO = B.CONTRACT_NO
AND B.CONTRACT_NO ('1111', '2222', '3333');

 

EXISTS 사용

SELECT *
FROM CONTRACT A
WHERE EXISTS ( SELECT 1
                 FROM CONTRACTOR B
                WHERE A.CONTRACT_NO = B.CONTRACT_NO
                  AND B.CONTRACT_NO IN ('1111', '2222', '3333') );

 

언뜻 보면 아래의 문장이 성능이 좋지 않을 것 같지만,

EXISTS를 사용한 두 번째 쿼리의 비용(2)이 IN을 사용한 첫 번째 쿼리의 비용(3) 보다 더 적다. 실제 테이블에 저장된 데이터 양이 많지 않아 성능 차이는 거의 없다고 볼 수 있지만, 수십, 수백만 건의 데이터가 저장되어 있다면 눈에 띄게 그차이가 드러난다.

=> 생각해보기

 

 

 

 

 

 

SET 연산자 사용시 UNION 대신 UNION ALL을 사용하라.

---

UNION 연산자는 연결된 쿼리에서 동일한 로우는 제거하고 한 로우만 반환하게 된다.

따라서 추가적으로 필터링하는 로직이 숨어 있으므로 UNION ALL 보다는 비용이 높을 수 밖에 없다.

 

(참고서적 : 뇌를 자극하는 오라클 프로그래밍)

출처: https://cornswrold.tistory.com/87

SQL 튜닝 (일반적인 SQL 튜닝 기법)

 

  

적절히 인라인 뷰를 사용하여 본래의 집합을 최소화 한 후 조인한다.
SQL 튜닝은 적절한 집합을 먼저 만드는 데에서 시작한다.

 

튜닝 전 SQL

SELECT  /*+ index(c EXT00002_I1) */

             B.SALE_EMP_NO EMP_NO, 

             MAX(F.EMP_NM||'('||F.EMP_NO||')') EMP_NM, 
             B.PAY_MAN_NO,

             MAX(E.CUST_NM||'('||D.PAY_MAN_NO||')') PAY_MAN,

             B.UNIBUS_Svc_CD, 
             MAX(C.CODE_NM) CODE_NM
FROM     (SELECT * FROM TBSLO_ISVCDM WHERE svc_stat_cd = '01') B,
             (SELECT * FROM EXT00002 WHERE GROUP_CD = 'X0007') C,
             TBMKA_PAYER D, 
             TBMKA_CUST E, 
             TBSGU_USER F
WHERE   B.UNIBUS_Svc_CD = C.CODE_ID(+) 
AND       B.PAY_MAN_NO = D.PAY_MAN_NO (+) 
AND       D.CUST_NO = E.CUST_NO (+)   
AND       B.SALE_EMP_NO = F.EMP_NO (+) 
AND       B.SVC_STAT_CD != '02'  
AND       B.UNIBUS_Svc_CD NOT IN ('AJ','CJ','BF','BY')
AND       B.SALE_EMP_NO LIKE '%'  
AND       B.PAY_MAN_NO LIKE '%'  
AND       B.UNIBUS_Svc_CD LIKE '%'    
AND       F.SAP_DEPT_CD LIKE '%'
GROUP BY B.SALE_EMP_NO, B.PAY_MAN_NO, B.UNIBUS_Svc_CD

튜닝 전 SQL 설명

SQL을 딱 보면 where 조건에 B 테이블에 조건을 많이 걸고 있다. 

B테이블과 다른 테이블이 아웃터 조인

오라클은 B 테이블을 access 
B테이블이 주access 테이블로 data 집합이 만들어 진다..

Rows     Row Source Operation
-------  ---------------------------------------------------
 24014  SORT GROUP BY
 43956   NESTED LOOPS OUTER
 43956    NESTED LOOPS OUTER
 43956     NESTED LOOPS OUTER
 43956      NESTED LOOPS OUTER
 43956       NESTED LOOPS
 50373        TABLE ACCESS FULL TBSLO_ISVCDM
 43956        TABLE ACCESS BY INDEX ROWID TBSGU_USER
 43956         INDEX UNIQUE SCAN PK_TBSGU_USER (object id 27277)
 43921       TABLE ACCESS BY INDEX ROWID TBMKA_PAYER
 43921        INDEX UNIQUE SCAN PK_TBMKA_PAYER (object id 26727)
 43921      TABLE ACCESS BY INDEX ROWID TBMKA_CUST
 43921       INDEX UNIQUE SCAN PK_TBMKA_CUST (object id 28584)
 43956     TABLE ACCESS BY INDEX ROWID EXT00002
243766      INDEX RANGE SCAN TEMP_IDX1 (object id 28491)
 10296    REMOTE

path 설명

뭔지는 잘 모르겠지만, 최종 24014를 뽑아내기 위해서 두배 이상되는 데이터를 계속 가지고 다니면서 최종 group by 하면서 데이터를 줄이고 있다. SELECT의 컬럼을 보니 **명을 가져오는거 보니까 이 값들은 group by 한 이후에 조인을 하는방법, 그리고 243766 이 문제가됨


** 튜닝 후 **

SELECT x.sale_emp_no emp_no,

            x.emp_nm, 

            x.pay_man_no, 

            E.CUST_NM||'('||D.PAY_MAN_NO||')' PAY_MAN, 

            x.unibus_svc_cd,
            (SELECT /*+ index(EXT00002 EXT00002_I1) */ code_nm 
             FROM    EXT00002 

             WHERE  group_cd = 'X0007' AND x.unibus_svc_cd = code_id) code_nm
FROM     (SELECT b.sale_emp_no, b.PAY_MAN_NO,b.UNIBUS_Svc_CD, 

                           MAX(b.cust_no) cust_no, 
                           MAX(F.EMP_NM||'('||F.EMP_NO||')') EMP_NM
              FROM    TBSLO_ISVCDM b, TBSGU_USER f 
              WHERE   b.svc_stat_cd != '02' 
              AND       UNIBUS_Svc_CD NOT IN ('AJ','CJ','BF','BY')
              AND       b.SALE_EMP_NO LIKE '%' 
              AND       b.PAY_MAN_NO LIKE '%' 
              AND       b.UNIBUS_Svc_CD LIKE '%'
              AND       F.SAP_DEPT_CD LIKE '43000' 
              AND       B.SALE_EMP_NO = F.EMP_NO (+)
              GROUP BY b.sale_emp_no, b.PAY_MAN_NO,b.UNIBUS_Svc_CD) x,
              TBMKA_PAYER D, 
              TBMKA_CUST E
WHERE     x.PAY_MAN_NO  = D.PAY_MAN_NO (+) 
AND         D.CUST_NO = E.CUST_NO (+)

튜닝 후 SQL 설명

위 SQL 조건을 주는 것이 B와 F이므로 이 값에서 데이터를 줄일 수 있습니다. 나머지는 아웃터 조인이여서 있어도 그만 없어도 되는 값들. 즉 추출되는 row 수를 결정짓을 수 없는 요소들입니다. 그래서 B와 F를 조인을 한 후 GROUP BY을 하면 위 튜닝 전 SQL 최종 row 수 24014을 인라인 뷰를 통해 첫단계에서 우리가 원하는 양의 데이터를 뽑아냅니다.
그런 후 다른 테이블들과 조인을 합니다. 계속 24014을 유지를 합니다. 그럼. 여기서 의문이 생기죠. 위에서  243766나 되는 테이블과의 조인은 왜 조인을 안하고 access 패스 위로 빠졌냐 하는 것. 이건 코드명을 가져오기 위한 것. 즉, 대세에는 영향을 못미치는 것입니다. 주로 명을 가져오는 것은 조인이 아닌 스칼라 서브쿼리를 통해서 가져오는 것이 좋습니다.

Rows     Row Source Operation
-------  ---------------------------------------------------
   360  TABLE ACCESS BY INDEX ROWID EXT00002
   360   INDEX RANGE SCAN EXT00002_I1 (object id 27785)
 24014  NESTED LOOPS OUTER
 24014   NESTED LOOPS OUTER
 24014    VIEW
 24014     SORT GROUP BY
 43956      NESTED LOOPS
 50373       TABLE ACCESS FULL TBSLO_ISVCDM
 43956       TABLE ACCESS BY INDEX ROWID TBSGU_USER
 43956        INDEX UNIQUE SCAN PK_TBSGU_USER (object id 27277)
 23979    TABLE ACCESS BY INDEX ROWID TBMKA_PAYER
 23979     INDEX UNIQUE SCAN PK_TBMKA_PAYER (object id 26727)
 23979   TABLE ACCESS BY INDEX ROWID TBMKA_CUST
 23979    INDEX UNIQUE SCAN PK_TBMKA_CUST (object id 28584)

path 설명

주 테이블에, 여기서는 B(TBSLO_ISVCDM)와 F(TBSGU_USER) 데이터 집합을 만들고 나머지는 데이터의 집합에 영향을 주지 못하는 것들과 조인을 합니다. 당연히 조인전에 group by해서 집합을 만드는 것이 좋겠죠. 그럼. 여기서 화면에 바로 보여줘야하는 OLTP성 프로그램이라면 이 SQL이 좋을까요?
답은 반반입니다. group by을 하면 부분범위 처리가 되지 못하죠. 어떻게 보면 위 튜닝전 SQL이 더 빨리 나올 수도 있습니다. 여기서 제가 말씀드리고 싶은 건 SQL문 자체만 가지고는 맞다 틀리다 말씀드릴 수 없다는 겁니다. 데이터의 양과 물리구조. 프로그램의 성격 등 모든 것을 다 따져봐야 최적의 SQL이 나오게 됩니다

** 정리
- 모든 SQL은 어떤 테이블이 주테이블이 되는지를 확인하여 이 데이터를 최소화 시키려 노력해야 합니다.
- 데이터를 만들어 내는 주테이블의 집합은 여기서 보는 것과 같이 인라인 뷰, 힌트 등을 이용하여 집합을 만듭니다.
- 단순히 명을 가져오는 것는 스칼라 서브쿼리로 해결합시다.

** 응용
- 그럼. 위 SQL이 화면단에서 10건만 가져오는 프로그램이라면 어떻게 해야 할까요? 당연히 인라인 뷰부분이 1< ... <10 까지 조건을 준 후에 나머지 테이블 들과 조인을 해야합니다.
단, 여기에는 조건이 있습니다. 나머지 테이블들과 조인으로 인하여 데이터량이 변경된다면 적용할 수 없습니다.
- F 테이블에 조건이 없고 group by 할 필요가 없다면 B테이블에서 데이터를 10건만 짤라서 10건만 나머지 테이블과 조인을 하게 된다면 속도가 상승합니다.

** Tip.- 스칼라 서브쿼리란?
  SELECT에 있는 컬럼에 인라인 뷰값의 서브쿼리를 넣는 것입니다.

SELECT  a.xx,
        b.xx, 
        (SELECT com_cd_nm 
           FROM TBSGC_COMCD 
          WHERE grp_cd = 'SL717' 
            AND com_cd = a.svc_stat_cd),
        a.yy
FROM ...

 

항상 스칼라 서브쿼리만 쓰는것이 아니다

스칼라 서브쿼리와 조인되는 것들의 row수가 작아야 좋다,

 

출처 : https://tigerjune.tistory.com/82

적절한 인라인 뷰 사용으로 인한 성능향상 사례