sum(decode)참조

Textus Ciceronis

3.2.3 SUM(DECODE…) 사용시 주의사항
- 편리한 나머지 남용하는 사례가 늘고 있다;

- 오남용 사례와 치료요법
가. NULL값의 처리
- 예의 널 공포증 문제
- 함부로 NVL함수를(특히 행 단위 처리 내에서) 사용하면 비효율이 발생한다.
- NULL 값은 애초에 비교 대상에서 제외되므로, NULL인 행을 선택하는 DECODE가 아니면 NVL을 사용할 필요가 없다.
- SUM(DECODE…) 연산에서는 NULL은 아예 연산에서 제외되므로 굳이 NVL(col, 0)을 사용할 필요가 없다. (NVL을 DECODE 안에 사용하면 쓸데없는 연산이 발생한다.)
- SUM(DECODE…)에서 DECODE로 추출된 행 전체의 값이 NULL이어서 결과가 NULL인 경우, 0으로 표시해주고 싶다면, SUM 바깥에 NVL을 사용해주면 된다.
- 두 개 이상의 열의 연산에 대한 SUM이 필요한 경우
: DECODE문 안에 NVL을 안 쓰면 한 열만 NULL인 행도 연산에서 제외되는 문제 발생
à 각 열의 SUM에 대한 연산으로 전개하면 굳이 NVL을 사용하지 않아도 된다.
- ELSE 부의 값이 0인 경우 SUM에서는 NULL인 경우와 결과가 달라지지 않으므로 굳이 ELSE부를 쓸 이유가 없다. (ELSE부 값이 없으면 NULL처리되므로 수행속도의 차이 발생)

나. 반복 DECODE의 감소
- SUM(DECODE…)에서 가장 부하가 큰 부분은 DECODE 콤보; 부분이다.
- 특히 nested decode는 매우 부하가 크다.
- 수행속도를 위해 DECODE를 단순하게 만들어 줄 필요가 있다.
- 수행속도 절감 사례;
- 여러 열에 대해 계속 다중 DECODE를 쓰는 경우
: 각 열을 하나로 concatnation해서 DECODE하면 하나의 DECODE로 줄일 수 있다. (DECODE 안은 좀 길어지지만;)
- 범위 처리(a : SUM(DECODE(SIGN(b-x), 1, DECODE(SIGN(a-x), -1, y)))
à SUM(DECODE(ABS(a-x)+ABS(b-x), b-a, y))
- 열 조합에 의한 경우의 수가 매우 많은 경우
: 2진분류나 10진분류등 기수법 체계를 활용한다.
- 여러가지 수학함수를 이용해서 최대한 간단한 수학적 모델을 만들어낸다.

다. SUM(DECODE…)와 GROUP BY 비교
- SUM(DECODE…)를 이용해 추출할 열이 많은 경우의 부하를 줄이기 위해 GROUP BY를 사용할 수 있다.
SUM(DECODE(공통수식, 경우1, 값1)), SUM(DECODE(공통수식, 경우2, 값2)), SUM(DECODE(공통수식, 경우3, 값3)), ……
à SUM(DECODE(A, 경우1, 값1)), SUM(DECODE(A, 경우2, 값2)), SUM(DECODE(A, 경우3, 값3)), …... FROM (SELECT 공통수식 A, …… GROUP BY 공통수식)
è 결과값은 뒤의 SQL이 좀더 보기 흉해-_-지지만 수행속도는 훨씬 빠르다.
(GROUP BY를 한 번더 사용하는 성형수술은 부하가 크지 않다.)
- SUM(DECODE…)를 이용해 추출할 열이 많은 경우의 부하를 줄이기 위해 GROUP BY를 사용할 수 있다.

라. COUNT(DECODE…)의 활용
- 개수만 알아내는 데에는 SUM보다 COUNT가 수행속도면에서 훨씬 유리하다. (근데 대체 개수 알아내는데 SUM을 왜 쓰냐? -_-)
- COUNT(특정 열) 보다 COUNT(*)이 더 빠르다.
- *는 ‘모든 열’이 아니라 ‘조건을 만족하는 열에 대한 wild card’의 의미를 가진다.

마. GROUP BY 문에서 MIN의 활용
- 일관성을 지키지 않은 못된-_- 테이블을 처리해야 하는 경우, GROUP BY 연산에서 해당 열이 NULL인 행과 NULL이 아닌 행에 대해 각각 새로운 행이 생성되는 경우가 있다.
à MIN을 이용해 해결한다.
- SUM, AVG등은 숫자에만 가능하지만, MIN, MAX등은 모든 형태에 다 사용 가능하다.

바. SQL을 어떻게 공부할 것인가?
- 뻔한 얘기;

3.3. UPDATE문의 활용
3.3.1. 확장 UPDATE문
- DECODE와 같이 조건 처리를 할 수 없는 DBMS에서는 효용이 적다.
- UPDATE에서 가장 중요한 것은 UPDATE할 대상 집합의 명확화이다.
- UPDATE 구문에서는 WHERE절이 선택하는 집합의 크기가 UPDATE의 작업량을 결정하므로 잘 관리해야 한다.
- WHERE절에 있는 서브쿼리가 먼저 수행되면, 결과물이 상수의 집합이 되므로 WHERE절의 처리 범위를 줄일 수 있다.
- 행을 FETCH해서 가공, 처리, 다시 UPDATE하는 절차적 PL/SQL 프로그래밍 루틴을, UPDATE와 SELECT를 이용해서 하나의 SQL로 만들 수 있다.
- 프로그램 루틴의 SQL화 작업 시 주의점
1. SQL이 매우 커지므로 큰 롤백 세그먼트가 필요하며, 롤백이나 커밋시에 매우 많은 행 이 수정되므로 다른 작업에 영향을 줄 수가 있다.
2. 처리 시에 발생하는 개별 행의 에러를 선별하기가 곤란하다.
3. 하나의 SELECT 가공 결과로 여러 테이블을 UPDATE할 수 없다.
4. 서브쿼리에 조건을 만족하는 값이 하나도 없어 선택된 행이 없는 경우, NULL이 업데 이트 될 수 있다. à 가장 critical한 문제점
è EXISTS루틴 등으로 해결
- Error와 Fail의 차이점 : Error는 SQL이 수행되지 않았을 때, Fail은 수행되었으나 결 과가 공집합일 때 발생한다.
- GROUP함수에서 SQL은 절대 실패하지 않는다. (최소 하나의 행이 생성된다.)
è 실패한 SQL에서는 NVL을 사용해도 NULL 값의 발생을 막을 수 없지만, GROUP 함수와 같이 실패하지 않은 SQL에서는 NVL을 사용해 NULL값을 가공할 수 있다.
5. UPDATE문은 항상 UPDATE되는 테이블이 선행해야 하고, SET 절 내의 서브쿼리는 항상 나중에 반복수행되므로, 서브쿼리에서 가공을 위해 추출해야 하는 행이 많을 때에 는 다중처리 방법을 이용해야 한다.

3.3.2. 수정가능 조인 뷰
- 오라클 7.3 이상부터 적용
- 과거에 비해 비교적 자유롭게 조인 뷰에서 INSERT, DELETE, UPDATE를 할 수 있다. (그래도 여전히 제약이 많다.)

가. 수정가능 조인뷰의 제한사항
- 오라클 7.3부터 FROM절에 하나 이상의 테이블이나 뷰가 위치하는 조인이 된 뷰를 수정할 수 있게 됨
- 제약조건 : DISTINCT, 그룹함수(SUM, AVG등), 집합처리(UNION, INTERSECT등), GROUP BY와 HAVING처리, ROWNUM의 사용, 순환처리(CONNECT BY/START WITH 구문)을 사용한 뷰는 수정할 수 없다.
- 최종 뷰로 병합 가능한 뷰만 수정할 수 있다.
- 조인된 테이블 중에서 키보존 테이블만 수정할 수 있다.

나. 키보존 테이블이란?
- 조인으로 인해 변화가 일어난 집합의 논리적인 기본키가 자신의 기본키대로 유지되는 테이블 (조인을 했어도 자기 집합의 키 레벨은 변하지 않는 테이블)
- 1:M 조인의 경우 - 조인 결과의 키는 M측 테이블의 키 레벨이 되므로, M측 테이블은 키 레벨이 변하지 않지만, 1측 테이블은 조인 집합을 식별하는 기본키가 될 수 없다.
- M:M 조인의 경우 – 조인 결과는 둘의 Cartesian Product 만큼의 행이 생성 되므로, 두 테이블 모두 키보존 테이블이 될 수 없다. (M:M은 구현 단계에서 나와서는 안되는 구조이므로 별 걱정할 필요 없다-_-)
- 1:1 조인의 경우 – 조인 결과가 양쪽 테이블의 키 레벨과 같으므로, 두 테이블 모두 키보존 테이블이 될 수 있다.
- 키 레벨이 변경되지 않았어도 OUTER JOIN에 의해 생성된 집합에서는 최하위 테이블이 키보존 테이블이 될 수 없는 경우가 있다. (M측 테이블이 OUTER JOIN되는 경우 NULL 필드들이 양산되므로 더 이상 기본키의 역할을 할 수 없다.)

- 진정한-_- 키보존 테이블이 되기 위해서는 위 조건 말고도, 조인의 연결고리 중에서 조인되는 상대 집합의 연결고리에 반드시 Unique Index가 있어야 한다. (PK 지정으로 자동생성된 Unique Index여도 상관없다.)
- 조인뷰의 UPDATE에서 WHERE절에는 아무거나 올 수 있지만, SET 절에는 키보존 테이블의 열만 올 수 있다.
- 조인뷰의 DELETE에서는 반드시 단 하나의 키보존 테이블만 가져야 한다.
- 조인뷰의 INSERT는 DELETE와 같이 하나의 키보존 테이블만 가져야 가능하며, 여기에 덧붙여 원 테이블의 제약조건(constraints)을 만족해야 한다. (당연한 얘기;)

다. 수정가능 조인뷰의 활용