안녕하세요.

증권??  프로젝트 튜닝 지원중  튜닝 내용 공유합니다. ( db: oracle) 

SQL :   SELECT ....
                              ( SELECT max(rel_id_nm)    from tab_1 b where b.rel_id = a.id ) 
                from tab_1 a 
              where a.is_deleted ='N'

현상 : 
   -   현재 인덱스 :   tab_1 ( id ) 
   - scalar subquery 부분에 사용된 컬럼(rel_id)에 인덱스가 없어 full table scan  을 조회 건수 만큼 수행됨 
       ( a.is_deleted ' ='N' 를 만족하는 데이터가 350여건, 전체 건수는 900 여건 ,  즉 full scan  을 350번 수행함)
  - 비록 데이터 건수가 작은 데이블이기 때문에 절대적인 응답시간은 양호함. 
  - 
튜닝 내용 
   1. 위의 sql  의 경우는 scalar subquery  를 조인으로 변경하는 것이 가장 효율적입니다. (best) 
   2. tab_1 테이블에 rel_id  컬럼으로 인덱스를 만들게 되면 (good) 
         tab_1(alias a)  full scan  -> tabl_1(alias b)  index scan  
        이런 과정을 거치게 되는데 실제적으로는 alias b 의 인덱스 full scan  과 유사한 현상이 발생합니다. 
        즉  tab_1(full scan 1회 ) ->  index range scan(350회)   -> table scan(with rowid ) : 여기서 350회 index scan을 하면 거의 전체 건수입니다. ) 

  3. 그래서 위의 sql문장은 조인으로 변경하는 것이 좋습니다.
         ( scalar subquery  를 조인으로 수정시에는 반드시 outer join 으로 변경해야 합니다. ) 
       select ...
           from tab_1 a, ( select  rel_id ,  max(rel_id_nm)  from tab_1 b group by rel_id ) b
       where a.id = b.rel_id (+)
      이렇게 하면 tab_1 full scan 1회   ->  tab_1(alias b) full scan 1회     이렇게 됩니다.

** 추가 검토
    위의 sql 문은 조회조건이  is_deleted ='N' 만 있기 때문에 조인으로 변경하는 것이 가장 좋지만, 
    추가 조건이 있고,  추가 조건으로 조회 건수가 줄어 든다면 위의 2번과 같이 인덱스를 만드는 것으로 진행해야 합니다.
    ( 예 ,  reg_date between sysdate - 10  and sysdate   ) 

공유차원에서  튜닝내용을 올렸는데, 혹시 궁금한 부분이 있거나 다른 의견이 계시면 연락주시기 바랍니다.

그럼  다음글에서 뵙겠습니다.