DevStarSJ.github.io 으로 이사했습니다.: 대용량 데이터베이스 솔루션 2권 #16 논리합연산자

4장 논리합연산자의 ACCESS 효율화

1. 논리합 연산자의 이해

- AND (논리곱) : 안좋은 조건 * 안좋은 조건 => 범위가 줄어듬
- OR (논리합) : 똑똑한 조건 + 멍청한 조건 => 멍청한 조건
(똑똑한 조건 100개 + 멍청한 조건 1개 = 멍청한 조건)

- 처리주관 조건에서 AND 는 정의역 범위가 줄어들고 OR는 넓어진다.
- FULL OR UNIQUE = FULL
- DRIVING 조건에서는 항상 AND가 유리하며, CHECK 조건에서는 오히려 OR가 유리한 경우도 있다.
- 설계시 응축화, 단순화된 DATA MODEL을 구축하여 OR 조건을 최대한 사용안해도 되게 하는게 좋다.
- 그러나 Optimizer가 좋아하는 OR도 있으므로, 정확하게 사용하면 충분히 극복 가능하고 오히려 유리하게 활용할 수 있다.

1.1 OR 과 IN

* IN은 OR의 부분집합
   - IN은 같은 칼럼 안에서의 OR
   - IN은 점 (=)의 OR 모임
* 실행계획에 동일한 영향을 미침
   - 매우 유사하나 활용방법에는 큰 차이가 있다.
   - IN은 단발 (=)의 집합, OR는 Range-Scan의 집합

* 주의사항
   - INDEX 구성에 대한 전략에 전략에 유의할 것
     (DRIVING 조건을 고려)
   - 단순 OR만 사용해야 한다. : A OR (B OR (C OR D)) 같은건 사용하지 말자.
   - OR, IN 을 없앨 수 있다면 최대한 제거하라.
   - IN을 전략적으로 활용하면 성능향상에 도움된다. (선을 점으로... RANGE-SCAN을 UNIQUE 들의 CONCATENATION 으로)

1.2 논리합 연산자의 실행계획

1.2.1 OR (다른 Column)

1. A = 1 인것을 INDEX를 이용하여 찾아서
2. MEMORY에 저장해 둠
   (DBMS에 따라서는 이미 ACCESS한
    ROWID들을 기억해둠)
3. B LIKE 'S%'를 찾으면서
4. 이미 MEMORY에 찾은 것들은 PASS하고
   (DBMS에 따라 TABLE ROWID로 CHECK)
5. 새로 찾은것만 MEMORY에 저장

- DRIVING 조건이면 분리된 실행계획
(각각을 Optimizer가 최적화)
- DRIVING 조건 중 어느하나가 다른것의 진부분집합이면 한쪽으로 귀속
( INDEX OR FULL-SCAN => FULL-SCAN )
- 부분범위처리 가능(Oracle). 하지만 DBMS마다 다름
SORT하여 전체범위처리하는 DBMS도 있음

CONCATENATION
TABLE ACCESS BY ROWID OF TAB1
INDEX RANGE SCAN OF A_INDEX
TABLE ACCESS BY ROWID OF TAB1
INDEX RANGE SCAN OF B_INDEX

1.2.2 IN (동일 Column)

1. A = 1 인것을 INDEX를 이용하여 찾아서
2. MEMORY에 저장해 둠
   (DBMS에 따라서는 이미 ACCESS한
    ROWID들을 기억해둠)
3. A = 5 를 찾으면서
4. 이미 MEMORY에 찾은 것들은 PASS하고
   (DBMS에 따라 TABLE ROWID로 CHECK)
5. 새로 찾은것만 MEMORY에 저장

- DRIVING 조건이면 분리된 실행계획
- 비교값간에 교집합이 없어 비효율적인 실행이 없음
- 부분범위처리 가능(Oracle). 하지만 DBMS마다 다름
- RANGE-SCAN에 비해 원하는 부분만 ACCESS가 가능하여 보다 효율적인 실행계획이 가능
(징검다리 역할 : Optimizer가 가장 좋아하는 실행계획이다.)

CONCATENATION
TABLE ACCESS BY ROWID OF TAB1
INDEX RANGE SCAN OF A_INDEX
TABLE ACCESS BY ROWID OF TAB1
INDEX RANGE SCAN OF A_INDEX

1.3 실행계획 분할

1.3.1 INDEX의 분포도 문제 해소

고객CODE 대부분이 101이라면
- 101이면 FULL-SCAN
- 101이 아니면 INDEX-SCAN
으로 해야한다.
어떻게 SQL문을 작성해야 실행계획이 2가지로 나뉠까 ?

SELECT SUBSTR(매출일,1,6) , SUM(매출액), SUM(손익액)
  FROM 매출손익
 WHERE (:IN_CUST <> '101' AND
        거래처코드 = :IN_CUST AND
        매출일 LIKE :IN_DATE||'%')
    OR (:IN_CUST = '101' AND
        RTRIM(거래처코드) = :IN_CUST AND
        매출일 LIKE :IN_DATE||'%');

이렇게 하면 될까 ?
INDEX-SCAN OR FULL-SCAN 은 당연 FULL-SCAN 이다.
이 실행계획은 언제나 FULL-SCAN하는 것으로 된다.

그럼 제대로 실행계획을 분할 하려면 어떻게 해야 할까 ?

SELECT SUBSTR(매출일,1,6) , SUM(매출액), SUM(손익액)
  FROM 매출손익
 WHERE :IN_CUST <> '101'
   AND 거래처코드 = :IN_CUST
   AND 매출일 LIKE :IN_DATE||'%'
 UNION ALL
SELECT SUBSTR(매출일,1,6) , SUM(매출액), SUM(손익액)
  FROM 매출손익
 WHERE :IN_CUST = '101'
   AND RTRIM(거래처코드) = :IN_CUST
   AND 매출일 LIKE :IN_DATE||'%';

UNION ALL 을 이용한 실행계획 분할시에는 하나만 참집합으로 해야한다. 그래서 둘 이상이 동시에 실행되는 일은 없어야 한다.

1.3.2 복잡한 OR 해소

SELECT 상태, 출고번호, 고객번호, 출고일, 운송비 FROM 출고내역 WHERE (:SW = 1 AND (상태 LIKE '1%' OR 상태 LIKE '2%')) OR (:SW = 2 AND 상태 LIKE '3%')) ORDER BY 상태	복잡한 OR의 실행계획은 왠만하면 FULL-SCAN이 된다. 그리고 상태를 ORDER BY ? 상태에 INDEX가 잡혀있다 치더라도 FULL-SCAN 하면 INDEX에 의한 자동 SORT가 안되기 때문에 ORDER BY를 사용할 수 밖에 없다. 복잡한 OR는 DECODE를 이용하여 단순 OR로 가공하면 CONCATENATION 실행계획으로 바뀐다.
SELECT 상태, 출고번호, 고객번호, 출고일, 운송비 FROM 출고내역 WHERE 상태 LIKE DECODE(:SW, 1, '2%')) - - 우변상수로 가공 OR 상태 LIKE DECODE(:SW, 1, '1%', '3%'));	CONCATENATION TABLE ACCESS BY ROWID OF 출고내역 INDEX RANGE SCAN OF 상태_INDEX TABLE ACCESS BY ROWID OF CNFRHSODUR INDEX RANGE SCAN OF 상태_INDEX

1.3.3 OR 연산자 해소

SELECT ...
  FROM ...
 WHERE ...
   AND ((:SW = '1' AND
         COL1 = '10' AND
         DATE BETWEEN SYSDATE - 30 AND SYSDATE AND
         COL2 = :CODE)
    OR  (:SW = '2' AND
         COL1 BETWEEN '20' AND '30' AND
         DATE BETWEEN SYSDATE - 60 AND SYSDATE AND
         COL2 = :CODE));

SELECT ...
  FROM ...
 WHERE ...
   AND COL1 IN (DECODE(:SW,'1','10','20'),
                DECODE(:SW,'2','30'))
   AND DATE BETWEEN SYSDATE - 30 * :SW AND SYSDATE
   AND COL2 = :CODE;

DECODE 나 연산을 이용하여 상수쪽을 가공해서 가능한 OR를 제거하자.
(통분하고 단순화시키는 수학적 능력이 필요하다.)

1.4 DATA MODELING 을 잘못한 사례

어느 자동차 공장의 이야기다.
자동차는 미리 만들어서 판매하는게 아니라 고객의 주문을 받으면 공장에서 해당 자동차를 찍어서 고객에게 인도한다.
공장 대기실에는 현황판이 있으며, 현황판에는 고객이름별 현재 차량 생산공정이 어디에 진행중인지가 나와있고 여기에 출고가 된 것을 보고 각 대리점에서 차량을 인도해간다.
원래 IBM DB2로 되어있단 DB를 Oracle로 이관했다. 주말동안 이관작업을 끝내고 월요일 가동을 시작하자마자 현황판이 먹통이 되었다.
실제로 차량은 이미 모든 공정을 마치고 포장까지 끝낸 상태인데 현황판에는 아직 공정진행중으로 몇시간째 먹통인 상태라 대리점 담당자는 대기실에서 차가 왜 안나온다고 안내여직원에게 욕하고 폭력사태까지 벌어질 지경이고 공장에서는 차량이 이미 다 만들어졌는데 가져가지 않아서 공정은 계속 밀리고 있었다. 결국 아비규환이 된 공장에서는 왜 그런지 원인을 찾기 위해서 DB 컨설팅을 요청했다.

공정관련 Table을 보니 아래와 같이 되어있었다.

각 공정별로 ROW가 아닌 COLUMN별로 나열해 놓았다. 총 공정은 24개 공정이 되었다. 분명 1정규화에 어긋한 설계이다.
그리고 INDEX는 19개가 잡혀있었다. 공정이 24개라면서 INDEX를 19개만 ? 더군다나 그중 공정에 관련된 INDEX는 9개밖에 되지 않았다. 상식적으로 INDEX가 최소 25개는 되어야 하지 않나 ? PK와 24개 공정에 대해서 말이다.

SELECT ...
  FROM 차대별진행내역
 WHERE (투입일시 LIKE :V_DATE||'%'
    OR  출고일시 LIKE :V_DATE||'%'
    OR  입문일시 LIKE :V_DATE||'%'
    OR  ... )
   AND 생산라인 = :V_LINE;

현황판에서 사용중인 SQL은 왼쪽과 같은 OR의 연속이었다. OR은 DRIVING 조건과 INDEX에 주의해서 사용해야 한다. OR로 연결된 조건중에 INDEX가 하나라도 없으면 그냥 Full-scan으로 보면 된다.
더군다나 현황판이라면 거의 최근Data만 조회할텐데 5천만건이 넘는 Table을 Full-scan하니 말이다. 현황판은 5분마다 Refresh되는데, SQL문은 몇시간이 넘게 걸리고, 앞의 SQL문이 끝나지 않았는데 다시 SQL문을 DB로 호출하고... 이러니 DB에서 답변을 줄 수가 없다.
일단 1정규화를 적용하여 각 공정을 COLUMN이 아닌 ROW로 표현하였다.

SELECT ...
  FROM 차대별MASTER X,
       (SELECT 차대번호,
               DECODE(구분,'1',발생일시) A,
               DECODE(구분,'2',발생일시) B,
               ...
          FROM 차대이력
         WHERE 발생일시 LIKE :V_DATE||'%'
         GROUP BY 차대번호) Y
 WHERE X.차대번호 = Y.차대번호
   AND X.생산라인 = :V_LINE;