오라클 관련 튜닝 컨설팅 회사에서 말한거라고 하네요... 도움이 될수 있으니 참고 하세요...
1. 좌변을 가공하지 말라 (DB column을 가공하지 말고 치환시켜 상수(변수)부분을 가공하라.
원리
1. 인뎃스 컬럼은 비교되시 전에 변형이 일어나면 인덱스를 사용할 수 없다.
2. 부정형(Not, <>)으로 조건을 기술한 경우에도 인덱스를 사용하지 않는다.
3. 인덱스 컬럼이 NULL로 비교되면 사용될수 없다.
4. 옵티마이져가 필요에 따라 상기 적용원칙을 준수했음에도 불구하고 특정 인덱스의 사용을 취사 선택함으로써 사용되지 않는 인덱스가 생길수 있다.
예1)
기존)
select dept,
ename,
sal
from emp
where substr(job, 1, 4) = 'SALE'
해결책)
select dept,
ename,
sal
from emp
where job like 'SALE%'
예2)
기존)
select empno,
ename,
job
from emp
where sal * 12 = 35000000
해결책)
select empno,
ename,
job
from emp
where sal = 35000000/12
예3)
기존)
select empno,
ename,
job
from emp
where to_char(hiredate, 'YYYYMMDD') = '20050809'
해결책)
select empno,
ename,
job
from emp
where hiredate = to_date('20050809', 'YYYYMMDD')
예4)
기존)
select empno,
ename,
job
from emp
where nvl(job, 'X') = 'CLERK'
해결책)
select empno,
ename,
job
from emp
where job = 'CLERK'
예5)
기존)
select empno,
ename,
job
from emp
where job||dept = 'CLERK10'
해결책)
select empno,
ename,
job
from emp
where job = 'CLERK'
and dept = '10'
예6)
기존)
select *
from tab1
where col1 || col2 = :FLD
해결책)
select *
from tab1
where col1 = substr(:FLD, 1, 3)
and col2 = substr(:FLD, 4, 2)
2. 데이터 존재 유무를 확인하기 위해 count(*) ... cnt > 0 를 사용하지 말 것.
기존)
select count(*)
from tbl_point
where user_id = ''
해결책)
SELECT 1 AS cnt
WHERE EXISTS (select 'X'
from tbl_point
where user_id = 'locusty')
3. Decode 또는 Case를 사용시에 새끼를 증손자 이상 낳치 마라. (decode(decode...))
case when (
case when (
case when ( ) then end ) then end) then end as col2
이렇게 증손자 이상으로 들어가면 연산자 개산에 cost가 발생해서 처리 속도가 떨리집니다.
4. Union 을 사용할 때 다시 한번 Union all로 사용할 수 없는지 확인하라.
상식적으로 알고 있는 부분.
-union all은 두개의 결과값에서 중복되는 결과값까지 출력
-union은 두개의 결과값에서 중복되는 결과값을 제거하고 결과값을 출력
내부적인 처리 절차.
-union은 중복되는 결과값을 제거를 하기위해 내부적으로 sort가 발생하면서 distinct가 발생
우리가 알아야 하는 부분은 눈으로 보이는 부분이 아니라 내부적인 처리 알고리즘입니다.
5. In Line View 또는 out of temp를 사용할 때 진정 필요한 In Line View인지를 확인하라.
in line view 예제)
select *
from dept t1, (select col1, col2 from emp where dept_no) t2
where condition1, condition1
이 부분은 옵티마이져의 원리를 아셔야 알수 있는 내용입니다.
질의 처리 단계와 옵티마이저의 역활
Query --> Parse --> Query Rewrite --> Query Optimization --> QEP Generation --> Query Excution --> Result
sql이 실행이 되면 위와 같은 순서로 결과를 생성합니다.
위 순서에서 'Query Rewrite' 단계에서 서브질의와 뷰의 병합이 수행됩니다.
예1)
create view vw_emp
as
select *
from emp
where deptno = 10;
sql문)
select empno
from vw_emp
where empno > 11910;
'Query Rewrite' 단계
select empno
from emp
where deptno = 10
and empno > 11910;
line view도 동일한 과정을 수행합니다.
6. 조인 SQL일 경우 집합의 복제(카테시안곱)를 제외하고는
연결고리 Relation 상 1:M 이든, M:1이든, 1:1 이든 어느 한쪽은 반드시 1 인지 확인하고
함부로 Outer Join을 하지말고 Outer join이 자주 나타난다면 설계자(모델러)에게
다시 한번 확인하라.
카테시안 곱이란?
table1 : (1, 2, 3, 4)
table2 : (2, 3)
1)정상적인 연결고리에서의 결과값 :
-row(1) --> 2, 2
-row(2) --> 2, 2
2)카테시안 곱의 결과값(연결고리 불량) :
-row(1) --> 2, 1
-row(2) --> 2, 2
-row(3) --> 2, 3
-row(4) --> 2, 4
-row(5) --> 3, 1
-row(6) --> 3, 2
-row(7) --> 3, 3
-row(8) --> 3, 4
outer join 이란?
-outer join은 두 테이블 간의 관계에서 발생하는 조인 방식으로써 한쪽 테이블에 데이터가 없더라도 원래의 테이블 정보는 나오도록 할때 사용을 합니다.
이때 문제는 outer join이 걸리는 테이블은 무조건 Full table scan이 발생을 합니다.
보통의 DB 모델링에서 문제가 없이 진행이 되었다면 1:m, 1:1 관계로 모든 ERD가 생성이 됩니다.
그런데 코딩/개발 단계에서 outer join이 자주 발생을 한다는 것은 설계상의 문제가 있다는 것을 말합니다.
7. 결과에만 만족하지 말고 실행계획(Execution plan)에 관심을 가지고 절차형 로직(If Then Else)을 버리고 집합적 하나의 SQL로 임무를 완수토록 하라.
모든 query는 작성 후 실행계획을 확인해서 개발자가 원하는 형태로 수행이 되었는지 확인을 해 봐야 합니다.
인덱스가 생성이 되어 있고 정상적인 연결고리로 조인이 이루어져도 CBO(Cost Base Optimize) DBMS에서는 옵티마이저에 의해 인덱스 취사가 발생할수도 있으며
개발자 실수로 인덱스 컬럼의 가공이 발생해서 인덱스를 사용을 할수 없는 경우도 발생할수 있습니다.
기본 사항으로 실행계획은 확인하시고 개발해야 합니다.