3. SQL의 활용도 향상 방법
여러가지 SQL중에서 우리가 가장 잘 활용해야 하는 거은 'SELECT' 문이다. SELECT문은 단지 원하는 데이터를 읽어달라는 문장이 아니라 데이터를 읽어 필요한 가공처리를 한 후 그 결과를 요구하는 문장인 것이다. 우리가 어느선까지 가공 처리된 SELECT 문장을 생성할 수 있느냐에 따라 SQL의 역할은 크게 달라진다.
- SQL은 명령어가 아니라 하나의 프로그램
- 데이터베이스에게 일을 시킬 수 있는 유일한 수단
- 절차형 사고를 집합적 사고로 전환해야
- 동일한 결과를 내는 처리경로는 많으나 효율의 차이는 큼
- 응용력에 따른 개인차
- 사용상의 미묘한 차이가 엄청난 효율성의 차이를 가져옴
- 1000줄의 절차형 프로그램을 10여줄로 처리가능
- 1000초의 수행속도를 약간의 변경으로 1초로도 향상 가능
- 내부 처리절차를 모르는 사람은 세월이 흘러도 제자리
- 원리 이해를 바탕으로 정석을 익히고 응용력을 키워라!
3.1. DECODE함수를 이용한 IF처리
DECODE함수를 이용한 IF 처리는 아래와 같다. 중첩된 DECODE 처리는 수행속도에 나쁜 영향을 미친다.
DECODE 함수는 오로지 '=' 로만 비교되어야 하며, 함수를 수행한 결과는 항상 하나의 값만 가질 수 있을 뿐만 아니라
표현방법도 훨씬 자유롭지 못하다. 부등호 비교를 해결해야 하는 경우는 SIGN, FLOOR, CEIL 함수를 같이 사용하여
해결할 수 있다. (오라클 8i부터 case지원한다. decode보다 case가 유리하다)
문자열의 부등호 비교를 위한 처리는 GREATEST, LEAST 함수를 사용하여 해결할 수 있다.
이 함수들의 장점은 비교되는 값들의 데이터 타입에 전혀 영향을 받지 않으며 SIGN이나 CEIL, FLOOR와 달리
연산을 하지 않으므로 부하도 훨씬 적다.
3.2. SUM(DECODE...)의 활용
DECODE를 가장 빛나게 해주는 활용 방법은 SUM함수와 함께 사용되는 경우라 해도 지나치지 않다.
3.2.1. SUM(DECODE...)의 기본형
3.2.2. SUM(DECODE...)의 확장
가. 전체집합의 확장
SQL 처리의 기본이 되는 전체 집합은 테이블이나 뷰, 인라인뷰가 된다. 그렇다면 이 전체 집합을 늘리는 방법은 조인을
사용함으로써 해결된다. 우리는 정보의 가공에 필요한 정보와 추가적인 처리영역을 필요로 한다면 얼마든지 늘여서 SQL
내로 끌어들일 수 있다.
나. 대분류의 확장
확장한 전체집합에서 WHERE절에서 선별한 처리할 영역을 표시한 것.
다. 중분류의 확장
전체 집합에 모여진 로우들에 대해서 선별된 작업 영역의 모든 데이터들은 다양한 형태의 단위 집합으로 모여야 한다. 이것을 중분류라고 한다.
중분류는 작업영역인 대분류 내의 대상 로우들 중에서 'GROUP BY'를 사용하여 분류하며, 필요에 따라 'HAVING'을 사용하여 다시 선별할 수 있다.
라. 로우단위의 확장
이 SQL은 매출 테이블에서 매출일자가 98년 10월인 데이터를 읽어서 각 부서별로 '당일 매출수량', '현재일부터 1주일간의 매출수량', '월간 합계', '각 부서별로 가장 마지막에 발생한 일자의 매출수량'을 구하는 문장이다.
3.2.3. SUM(DECODE...) 사용시 주의사항
많은 사람들이 SUM(DECODE...) 방식에 어느정도 익숙해지면 더 이상 길고 복잡하게 절차형 언어로 처리하려 하지 않는다.
그러나 SQL의 실행계획이나 수행속도에 대한 안목이 없는 상태에서 대책없이 함부로 사용하는 경향이 많이 나나난다는 사실이
문제가 된다. 모든 경우의 문제는 조금만 더 생각하여 핵심적인 아이디어를 찾아낸다면 수십분의 일로 코딩량을 줄일수 있고
그만큼 오버헤드를 줄일 수 있다.
가. NULL 값의 처리
NULL값은 어떤 값보다 크지도 작지도 않으며 NULL값과의 연산 결과는 무조건 NULL 값이 된다. 조건에서 어떤 컬럼을 비교했을때
NULL값이 있는 경우는 아예 처리 대상에서 제외된다. 이런 이유로 자신도 모르는 사이에 로직의 구멍이 생기는 것을 두려워하는 사람들이 함부로 NVL함수를 사용하여 NULL값을 없앤 후 처리 하려고 한다.
| 잘못 된 사용 예 | 올바른 사용 예 |
|---|---|
나. 반복 DECODE의 감소
SUM(DECODE...) 용법에서 가장 오버헤드를 주는 부분은 DECODE문을 연속해서 사용하는 경우이다.
아래와 같은 경우에 CASE를 쓰는것이 함수를 쓰지 않으므로 훨씬 유리하다.
P.1-106
사례1) 제조업을 하고 있는 (주)청산은 자재수급이 원활하지 않아 생산에 많은 차질을 빚고 있었다. 그 원인을 분석하기 위해 우선 다음 그림과 같이 거래선별로 발주에서 입고까지 소요되는 구매 리드타임(Lead Time)을 다양한 범위별로 집계해 보고자 한다. 구매 리드타임은 발주일에서 입고일 사이의 기간을 말하며 아직 입고가 되지 않은 건은 오늘까지의 기간을 적용한다. 설명을 간단하게 하귀 위하여 여기서 사용된 일자들은 date 타입으로 정의되어 있으며 일자에 '시분초'는 입력되지 않았다고 가정한다.
- SUM(DECODE(ABS(31 - 일수) + ABS(60 - 일수), 29, 수량)) ...
기하학적으로 볼때 어떤 두 점의 사이에 있는 점은 어느 위치에 있거나 항상 그 점과 범위의 최대치, 최소치 사이의 간격을 합한 값은 '최대치 - 최소치'와 같아진다. 이러한 사실은 그 점이 범위 밖에 있어도 동일한 결과를 얻는다. 그 이유는 그 점이 범위밖에 있을때는 어느 한쪽으로도 '최대치 - 최소치' 보다 크게 되지만 다른 쪽이 음수가 되므로 숫자상으로는 같을 수 있다.
P.1-110
다. SUM(DECODE...)와 GROUP BY 비교
위 2개의 SQL의 수행된 결과는 동일하다. 차이점은 하나는 일자별로 옆으로 나열되어 출력되고 다른 한가지는 세로로, 즉 각각의
일자별로 옆으로 나열되어 출력되고 다른 한가지는 세로로, 즉 각각의 일자가 로우단위로 집계되었다가 마지막에 옆으로 펼쳐졌다는
것이다. 그러나 수행속도는 나중에 소개한 SQL이 훨씬 유리하다.
P1-117 GROUP BY 후 처리 (사례)
위 그림의 위쪽 SQL에서 발생한 비효율을 찾아보자.
첫째. WHERE조건을 만족한 모든 로우들에 대해 DECODE처리와 환율 계산을 위한 연산이 반복적으로 수행된 것이다. 내용상으로 볼때 이러한 처리는 단기, 장기 구분이나 연도별로 데이터가 집계된 후에 처리해도 무방하다는 것을 우리는 알고 있다.
둘째. 국가와 환율 테이블은 국가와 가입년도로 집계된 후에 조인해도 관계가 없으므로 로우수가 많은 보험가입실적 테이블에서 조인할 필요가 없었다는 것이다.
이러한 비효율적인 부분을 없애기 위해 적절한 중간 집합을 먼저 생성한 후 나머지 조인과 DECODE처리를 하는 SQL이 위 그림의 아래쪽 SQL이다. 이 SQL의 마지막에 처리되는 SELECT-LIST의 DECODE는 실제로 줄어든 것이 없는 것처럼 보이지만 DECODE를 처리할 대상집합이 많이 줄어들었으므로 당연히 수행횟수는 줄어든다. 더구나 '국가' 테이블과 '환율' 테이블의 조인횟수도 같이 줄어들었으므로 그 차이는 훨씬 크다고 하겠다.
라. COUNT(DECODE ...) 의 활용
GROUP BY 를 이용한 통계 분석용 SQL 의 SELECT-LIST에서 단지 조건에 맞는 '개수'를 집계하고자 하는 경우에 많은 개발자들이 SUM(DECODE...)를 사용하고 있다. 이것은 COUNT(DECODE...)에 비해 비효율적이다. 만약 DECODE를 사용하지 않고 순수하게 함수의 부하만 비교한다면 COUNT는 SUM에 비해 약 30%~50%정도 유리하다.
이 비교를 위해서 COUNT(AMT)를 COUNT(*)로 했다면 더 큰 차이가 난다. COUNT(*)의 '*' 는 그 테이블에 있는 모든 컬럼을 말하는 것이 아니라 WHERE 절을 만족하는 로우를 지칭하는 '대명사'에 불과하므로 경우에 따라서는 테이블을 액세스할 필요가 없다.
위 SQL에 INDEX1 이 'A + B' 컬럼으루 구성되어 있다면 이 SQL은 COUNT(*) 에 '*'를 사용하였지만 테이블을 액세스하지 않고 'INDEX1'인덱스만 읽어내려간다. 여기에서 '*' 대신에 'A'나 'B'를 사용했더라도 물론 인덱스만 액세스한다. 그러나 수행속도나 결과에는 약간의 차이가 난다. 그 이유는 함수내에 어떤 입력값을 주었을때 그 값이 NULL인 경우에는 처리를 하지 않기 때문이다. 우리가 COUNT(*)를 했다면 로우를 읽지 않고 데이터 블록(BLOCK)내에 있는 로우 딕셔너리만 액세스하게 되므로 훨씬 유리해진다.
마. GROUP BY 문에서 MIN의 활용
바. SQL을 어떻게 공부할 것인가?
3.3. UPDATE문의 활용
UPDATE문도 SELECT문처럼 다량의 처리범위를 대상으로 관련된 주변의 정보를 참조하여 경우에 따라 다양한 연산이나 가공 처리를 하고 쓰기 처리까지 완료하는 절차형 언어에서와 같이 활용할 수 있다.
3.3.1. 확장 UDPATE문
높은 기능성을 가진 UPDATE문을 생성하기 위해서 우리가 가장 먼저 염두에 두어야 할 것은 UPDATE할 대상 집합을 명확히 하는 것이다. 대상 집합은 물론 WHERE절에 기술된다.
이와 같은 UPDATE를 사용하기 위해서는 몇가지 주의해야 할 사항이 있다.
첫째. 하나의 SQL로 처리되기 때문에 전체 작업이 완료되었을 때만 커밋이나 롤백을 할 수 있으므로 너무 많은 로우를 처리할때는 사용하지 않는 것이 좋다. 이러한 경우에는 아주 큰 롤백 세그먼트를 필요로 할 것이며, 변경된 블록이 너무 많이 발생하므로 다른 작업에 영향을 미칠 수 있다.
둘째. 처리 시에 발생되는 개별 로우에 대한 에러를 선별하기가 곤란하다는 것이다. 그러나 이러한 에러 사항은 WHERE 조건이나 DECODE를 적절히 활용하면 충분히 선별해낼 수가 있다.
셋째. 한번 가공된 결과를 이용하여 여러 개의 다른 테이블을 UPDATE할 수 없다는 것이다. UPDATE 테이블과 가공을 위한 참조 테이블이 하나의 SQL 로 묶여있기 때문에 수행과정에서 발생한 내용을 유지시킬 수 없음은 너무나 당연한 일이다. 간혹 한번 가공한 값을 여러개의 다른 테이블에 적용해야 하는 경우가 나타나기도 하는데 이런 경우는 하나의 UPDATE 문으로 구현하는 것이 바람직하지 않다. 물론 가공에 대한 부담이 매우 적다면 적용할 수도 있을 것이다. 그러나 이러한 경우는 가급적 다중처리 기법을 활용하기 바란다.
넷째. 서브쿼리가 실패했을대는 SET 절에 기술한 UPDATE 컬럼들에는 NULL값이 UPDATE 되어 버린다는 것이다.
3.3.2. 수정가능 조인뷰(Modifiable Join View)
뷰는 주로 SELECT에서 사용하기 위해서 적용해 왔다. 물론 과거에도 몇 가지 제한사항만 준수한다면 뷰에서도 INSERT, UPDATE, DELETE 를 할 수가 있었다. 그러나 그 제약이 너무 심하여 이러한 목적으로 활용한 경우는 찾아보기가 어려웠다. 참고로 여기서 말하는 '수정가능' 이란 말은 UPDATE만을 의미하는 것이 아니라 INSERT, DELETE를 포함하는 뜻이다. (오라클 7.3부터 가능한 기능이다.)
가. 수정가능 조인뷰의 제한사항
나. 키보존 (Key Preserved) 테이블이란?
키보존 테이블이란 말 그대로 조인으로 인해 변화가 일어난 집합의 논리적인 기본키가 자신의 기본키대로 유지되는 테이블을 말한다. 좀 더 쉽게 말하면 조인을 했을지라도 자기 집합의 키 레벨은 전혀 변하지 않는 테이블을 말한다.
가능한 오라클 dictionary 뷰를 자주 참조하는 습관을 들이는게 좋다.(dict)
다. 수정가능 조인뷰의 활용
수정가능 조인뷰의 활용은 크게 세가지로 나눌 수 있다.
첫째. 활용방법은 보안관리나 EUC(End User Computing)를 위해 다른 사용자 그룹에 어떤 오브젝트의 사용을 승인할 때 보다 요구에 적절히 부응한 형태로 제공할수 있다는 것이다.
둘째. 수행속도 향상을 위한 테이블의 물리적 설계 단계에서 수직분할을 할 때 과거에 비해 사용상의 부담이 줄어들었다는 것이다.
셋째. 확장된 UPDATE문의 활용 폭을 넓혀 주었다는 사실이다.
참고. Update Join View의 /*+ BYPASS_UJVC */를 이용한 튜닝 사례 (Undocument hint)
일반적으로 특정 테이블을 Update하기 위해서는 WHERE절에 EXISTS 또는 IN 등의 Sub-Query로 조인 조건을 만족하는 Row를 먼저 Check하고, 조건을 만족하는 Row에 대하여 SET 절에서 필요한 데이터를 검색하여 Update하는 것이 보통이다.
이 때, Update 해야 하는 Row가 많을 경우 WHERE절이나 SET절에서 테이블을 반복적으로 Random 액세스해야 하는 부담이 발생하므로 처리 범위가 넓은 Update문의 경우에는 'Updatable Join View'를 활용할 필요가 있다.
이 때, 조인되는 2개의 테이블은 반드시 1:1 또는 1:M의 관계여야 하며, Update되는 컬럼의 테이블은 M쪽 집합이어야 한다. 이것은 1쪽 집합인 Parent table의 조인 컬럼이 UK 또는 PK로 설정되어 있어야 함을 의미한다. 이 조건을 만족하지 못하는 Updatable Join View는 에러를 Return하며 실행되지 않는다.
(ORA-01779 cannot modify a column which maps to a non key-preserved table)
그러나, 일반적으로 View 또는 2개 이상의 테이블을 조인한 집합을 엑세스하는 경우가 많으므로 위의 UK나 PK Constraint를 설정하기 어려운 것이 현실이다.
따라서, 이러한 Constraint를 피해서 Updatable Join View를 사용할 수 있도록 BYPASS_UJVC 라는 힌트를 사용하여 튜닝할 수 있다.
UPDATE dept_salary ds
SET dept_salary_sum = (
SELECT SUM( salary_amt )
FROM monthly_salary ms
WHERE ms.dept_id = ds.dept_id
AND base_ym = '200703'
)
WHERE ds.base_ym = '200703'
AND EXISTS (
SELECT 1
FROM monthly_salary ms
WHERE ms.dept_id = ds.dept_id
AND base_ym = '200703'
) ;
-- 튜닝 후 SQL
UPDATE /*+ BYPASS_UJVC */
(
SELECT ms.dept_id ,
dept_salary_sum ,
SUM( salary_amt ) sum_salary_amt
FROM dept_salary ds ,
monthly_salary ms
WHERE ms.dept_id = ds.dept_id
AND base_ym = '200703'
GROUP BY ms.dept_id ,
dept_salary_sum
)
SET dept_salary_sum = sum_salary_amt ;
참조사이트>