by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

많은 조회 패턴을 가진 SQL은 하나의 SQL이 아니다.




"많은 조회 패턴을 가진 sql은 하나의 sql이 아니다."

조회 패턴에 맞게 SQL 실행계획 분기 하기.

  • '조회패턴에 맞게 SQL실행계획 분기 하기'란 SQL의 실행계획이 주된 액세스 조건 별로 최적화 될 수 있도록 SQL 구조를 분리 하는 것을 의미한다.
  • 일반적으로 like'%' 조건이나 nvl 조건을 사용한 유형들이 대표적인 유형이다.

모든 RDBMS에서 중요한 원칙

  • '하나의 sql은 하나의 실행계획으로만 수행된다.'
여기서 하나의 실행계획이라는 의미는 '조인순서, 조인방법, 테이블별 액세스방법이 하나'라는 의미이다.

LIKE '%', NVL등을 이용해 하나의 SQL을 작성할 경우 사용자는 oprimizer가 sql조건에 따라 각각 효율적으로 수행되기를 기대한다.

하지만 SQL은 하나의 실행계획으로만 수행되기 때문에 기대했던 것과 달리 때때로 비효율적인 수행을 하게 된다.
  • Ex SQL
    LIKE
    select t1.*,
      t2.*,
      t3.*
    from t1,
      t2,
      t3
    where t1.c1 like &b1 || '%'
      and t1.c2 like &b2 || '%'
      and t2.c1 like &b3 || '%'
      and t2.c2 like &b4 || '%'
      and t3.c1 like &b5 || '%'
      and t3.c2 like &b6 || '%'
      and t1.no = t2.no
      and t1.no = t3.no
      and t2.no = t3.no
    
    NVL
    select t1.*,
      t2.*,
      t3
    from t1,
      t2 t3
    where t1.c1 nvl('&b1', t1.c1)
      and t1.c2 nvl('&b2', t1.c2)
      and t2.c1 nvl('&b3', t2.c1)
      and t2.c2 nvl('&b4', t2.c2)
      and t3.c1 nvl('&b5', t3.c1)
      and t3.c2 nvl('&b6', t3.c2)
      and t1.no = t2.no
      and t1.no = t3.no
      and t2.no = t3.no
    
    
  • 위와 같이 하나의 sql에 다양한 조회패턴을 가지는 경우에는 각 조회패턴 별로 가장 효율적인 Access path를 검증 한 후 각 패턴 별로 최적화 된 실행계획으로 수행될 수 있도록 sql을 분리하는 것이 좋다.
  • 위 sql에서 변수 :b1부터 :b6까지 값이 무작위 조회시 효율적인 순서로 여러 패턴으로 실행계획을 나누도록 한다.
  • 적절한 테이블 조인 순서.
    패턴 t1.c1 like :b1 ll '%' t2.c1 like :b3 ll '%' t3.c1 like :b5 ll'%' 테이블 조인 순서
    1 O O O T1 -> T2 -> T3
    2 O O X T1 -> T2 -> T3
    3 O X O T1 -> T3 -> T2
    4 O X X T1 -> T2 -> T3
    5 X X X T2 -> T3 -> T1
    : : : : :
  • 프로그램에 적용한 예
    if :b1 is not null then
    			select /*+LEADING(t1) USE_NL(t1, t2, t3)*/
    ..		
    	elsif :b3 is not null then
    			select /*+LEADING(t1) USE_NL(t1, t2, t3)*/
    ..		
    	else
    			select /*+FULL(T1) FULL(T2) FULL(T3) USE_HASH(T1, T2, T3)*/
    ..
    endif;
    

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.