본문 바로가기
㏈ª By β┖υΕJini/MS-SQL

개발자들이 하지 않아야 할 칠거지악 (엔코아:오라클)

by ㏈ª ☞ β┖υΕJini.κR 2006. 10. 17.

오라클 관련 튜닝 컨설팅 회사에서 말한거라고 하네요... 도움이 될수 있으니 참고 하세요...


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에서는 옵티마이저에 의해 인덱스 취사가 발생할수도 있으며
    개발자 실수로 인덱스 컬럼의 가공이 발생해서 인덱스를 사용을 할수 없는 경우도 발생할수 있습니다.
    기본 사항으로 실행계획은 확인하시고 개발해야 합니다.