효과적인 SQL 활용

 

1. Hard Parsing, Literal SQL에 의한 부하를 줄일 것

  1) 이미 수립된 실행 계획을 공유해서 재사용 가능하도록 SQL 작성

    - 구문 분석 비용 감소

    - 메모리 사용 개선

 

  2) 동일한 SQL 문장으로 사용

    - 텍스트 동일 (대/소문자, 빈 캄, 주석, 포함)

    - 참조 객체 동일

    - 바인드 변수의 데이터 형식 동일

 

   2번 항목을 이해할려면 Parse, Execute, Fetch 동작 방식을 알아야합니다.

   간단하게 설명하면 Client 가 Parse 로 SQL 문을 작성하여 보내면

   Parse 는 syntax check, semantic check 해서 과거에 수행한 SQL 인지 아닌지 확인하여

   Optimizer(Execution Plan, Row  Source생성) 확인하여 Shared SQL Area 에 보내게되는로직을 이해 해야합니다.

 

  3) SQL 형식 표준을 정하여 코딩

    - 대/소문자 규칙, 주석 사용 규칙 등

    - 자주 쓰는 것은 PL/SQL로 대치

 

 

2. 테이블 접근 횟수 줄이기

  1) CASE, DECODE

    - 프로그램 If, Else 처리를 제공

    - 하나의 쿼리에 여러 조건에 따른 결과값 추출 가능

--- CASE 문으로 전체 보너스 조회 가능
SELECT EMP_ID,
	EMP_NAME,
    (CASE
    	WHEN DEPT_NO = 10 THEN SALARY * 1.1
        WHEN DEPT_NO = 20 THEN SALARY * 1.2
        WHEN DEPT_NO = 30 THEN SALARY * 1.3
        	ELSE SALARY
     END) AS BONUS
FROM EMP;

 

  2) Merge

    - Insert와 Update를 동시에 처리 가능

    - 만약 UPDATE, INSERT 갯수가 확인을 하고 싶으면 PL/SQL 문에서는 가능하니까 여기서 확인하면 됩니다.

--- EMP의 정보를 TB_MERGE 테이블에 머지하는 SQL

MERGE INTO TB_MERGE D
USING (SELECT EMP_ID,
		(CASE 
        	WHEN DEPT_NO = 10 THEN 'Accounting'
            WHEN DEPT_NO = 10 THEN 'Research'
            WHEN DEPT_NO = 10 THEN 'R&D'
            	ELSE 'Other'
         END) AS DEPT_NAME
        FROM EMP ) S
ON (D.EMP_ID = S.EMP_ID AND D.YYYYMM = '202409')
WHEN MATCHED THEN
	UPDATE SET D.DEPT_NAME = S.DEPT_NAME
WHEN NOT MATXCHED THEN
	INSERT (EMPT_ID, YYYYMM, DEPT_NAME) 
	VALUES (S.EMPID, '202409', S.DEPT_NAME)

 

 

  3) Multi Table Insert

    - 하나의 데이터를 한번에 여러 테이블에 동시 입력

    - 시스템 관리를 할때는 잘 안씀 여러개를 넣으면 데이터 중복이기에,

    - 분석환경에서 쓰기에는 좋습니다.

--- 조건에 맞는 첫번째 테이블에 INSERT 하는 SQL (FIRST)
--- 모든 조건에 맞는 테이블에 넣을려면 ALL
INSERT FIRST -- or ALL
WHEN ORDER_TOTAL > 10000 THEN
	INTO PRIORITY_HANDLING VALUES (ID)
WHEN ORDER_TOTAL > 5000 THEN
	INTO SPECIAL_HANDLING VALUES (ID)
WHEN TOTAL > 3000 THEN
	INTO PRIVILEGE_HANDLING VALUES (ID)
ELSE
	INTO REGULAR_HANDLING VALUES (ID)
SELECT ORDER_TOTAL, ORDER_ID ID
FROM ORDERS;

 

 4) 분석 함수

    - 추출된 Row 에 대해서 Window (특정범위) 처리 (집계, 순위, 평균 등)를 제공

-- 결과를 만족시키는 레코드를 전체 집합으로 부서별로 최대 연봉의 정보를 보여준다

SELECT A.*,
	MAX(SAL) OVER (PARTITION BY DEPT_NO)
    MAX_SAL
FROM EMP A
ORDER BY ENAME

 

3. 데이터 구조를 바꿔야 하는 경우

  1) 테이블의 컬럼 순서 주의

    - PK 컬럼에 생성된 복합 컬럼 인덱스 사용시 부적절한 컬럼 순서로 인한 성능저하

 

4. 데이터 연결 방법 (JOIN)

  1) 조인 (JOIN)

    - RDBMS는 구조적으로 데이터의 무결성과 정합성을 고려하여 테이블을 설계하고 데이터를 저장한다.

    - 비즈니스 요구사항에 맞는 데이터를 Query 하기 위해서 JOIN은 필수 요소이다.

 

  2) index 선언하기

    - Nested Loop Join

    - 먼저 처리되는 테이블을 드라이빙 테이블이라고하는데 여기서는 TAB1.col1 = '10' 인데 인덱스처리가 되어있는거 보이죠?

    - (1) TAB1의 인덱스 col1='10' 검색 (idx_01)

    - (2) Random Access 

    - (3) TAB1의 row 데이터 획득

    - (4) col2 컬럼 조인 TAB2.key2 = TAB1.key1 (idx_02)

    - (5) TAB2의 row 데이터 획득

    - (6) TAB2의 col2='Y' 경우만 성공

    - 즉 순차적으로 진행이됩니다. 

SELECT TAB1.col1, TAB1.col2,
	TAB2.col1, TAB2.col2
FROM TAB1,
	 TAB2
WHERE TAB1.col1 = '10'
AND TAB1.key1 = TAB2.key2
AND TAB2.col2 = 'Y';

-- TAB1.col1 : 인덱스 idx_01
-- TAB2.key2 : 인덱스 idx_02

 

만약에 idx_02 인덱스가 없었다면? 모든 row를 다 읽어서 처리를 해야합니다.

그래서 idx 처리가 중요합니다.

 

즉, Nested Loop 에서는 반드시 idx_02, inner table 에 연결조건에 반드시 인덱스가 있어야하는겁니다.

 

Nested Loop Join 정리

1. 연결고리에 인덱스가 존재하는 경우에 사용해야한다 (inner table 인덱스 존재 필수)

2. 처리량이 적은 경우 유리하다 (랜덤 액세스 발생)

3. 순차적으로 처리하며, 부분범위 처리가 가능하다

4. 조인의 순서에 따라서 성능의 차이가 존재할 수 있어서, 처리범위를 줄일 수 있는 순서를 선택해야 한다.

 

Sort Merge Join 정리

1. 주로 처리량이 많으면서 항상 전체 범위를 처리해야 하는 경우에 유리

2. 조인 연결고리 상태에 연향을 받지 않으므로 연결고리를 위한 인덱스가 필요없다

3. 전체 범위를 처리하므로 Fetch 단위가 영향을 주지 않는다. 적당하게 큰 fetch 단위를 설정하는것이 좋다

4. 데이터가 소량인 경우에는 Nested Loops 조인 방식이 유리하다

 

만약에 대량의 경우 Hash 조인, 소량인 경우에는 Nested Loops 조인 방식이 유리

근데 부등호 조인 등 의도적일때만 발생하는게 좋고

만약 Sort Join 이 발생하면 의심하는게 좋아요

 

Hash Join 정리

1. 작은 테이블과 큰 테이블의 조인시에 유리

2. Hash 함수를 사용함으로 조인 조건이 항상 "=" 로 연결되어야 한다.

3. build(driving) 테이블에 Index가 꼭 필요하지 않다.

4. 각 테이블은 1번씩 읽게 된다

5. build(driving) 테이블의 크기가 매우 중요하다 ( In-Memory Hash 가 동작해야 성능이 좋다)

 

효과적인 SQL 활용 요약

1) SQL 은 기본적으로 Parse -> Execute -> Fetch 단계로 처리된다.

2) 반복적 Literal SQL에 의한 Hard Parsing은 성능에 악영향을 끼친다, 변수 Bind 처리를 하자

3) 고급 SQL을 활용하여 테이블 접근 횟수 (block I/O) 를 줄인다

      CASE / DECODE, Merge, Multi Table Insert, 분석함수(max 등) 사용한다

4) SQL 개선, 튜닝이 100% 정답은 아니다. 데이터 구조를 개선하는 것도 고려한다

5) Nested Loop 조인은 소량 추출에 사용하고, Inner Tabled 연결조건이 인덱스가 반드시 있어한다

6) Hash 조인은 대량 데이터 추출에 사용하고, build 테이블의 사이즈가 중요하다