* 요점.
적절히 인라인 뷰를 사용하여 본래의 집합을 최소화 한 후 조인을 합니다.
대부분의 SQL 튜닝은 적절한 집합을 먼저 만드는 데에서 시작합니다.
* 사례 SQL.
튜닝 전 SQL
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 집합이 만들어 질 것 같네요.
------- ---------------------------------------------------
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 요런것들 보니까 좀 문제가 있지 않을까 하는 의심을 하게 되죠.
지금 이 SQL을 보니까 데이터 이관할 때 잠깐 쓰던 SQL이네요.
** 튜닝 후 **
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 패스 위로 빠졌냐 하는 것. 이건 코드명을 가져오기 위한 것. 즉, 대세에는 영향을 못미치는 것입니다. 주로 명을 가져오는 것은 조인이 아닌 스카라 서브쿼리를 통해서 가져오는 것이 짱입니다.
------- ---------------------------------------------------
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에 있는 컬럼에 인라인 뷰값의 서브쿼리를 넣는 것입니다.
b.xx,
(SELECT com_cd_nm
FROM TBSGC_COMCD
WHERE grp_cd = 'SL717'
AND com_cd = a.svc_stat_cd),
a.yy
FROM ...
요게 성능에 정말 좋습니다. 보기에는 function과 비슷하지만 속도는 천지차이죠. 스칼라 서브쿼리를 사용합시다. 꼭. 요게 메모리에 저장이 되서 무지 빨라요. 근데. 항상 스칼라 서브쿼리가 좋냐? 당연히 아니죠. 스칼라 서브쿼리와 조인되는 것들의 row수가 작아야 좋죠.
출처 : http://www.dbguide.net/dbqa/dbqa120001.jsp?mode=view&pg=15&idx=530