데이터와이즌, 데이터를 잘 다루다!

컬럼의 부분값 검색을 위해 SUBSTR 함수 같은 예외적인 로직을 사용함으로써 인덱스를 사용하지 못하고, 최적 실행계획이 수립되지 않는 경우, 그런 예외 로직을 개선한다. 개선 전 (문제 상황) 관리내역 데이터를 과다하게 액세스함 (문제 원인) 암호화고객실명번호, 접촉전화번호 컬럼 조건절이 액세스 범위를 크게 줄일 수 있으나, 그 컬럼이 선두인  인덱스가 없고, 만약 있더라도 조건절의 ‘예외처리’로직으로 인해 그런 인덱스를 사용할 수 없는 상태임 참고로, 예외로직은 암호화고객실명번호 값이 NULL이면 암호화고객실명번호 컬럼 대신 고객실명번호 컬럼을 사용하라는 로직이며, NVL, DEDODE, OR 구문을 사용하면 적절한 인덱스가 있더라도 옵티마이저가 최적 실행계획을 수립하기 어려움 개선 방안 (개선 방안) 암호화고객실명번호, 접촉전화번호 각각 선두인 인덱스 2개를 생성함 (개선 방안) 인덱스 사용을 어렵게 하는 예외로직을 수정함 테이블 데이터를 조사해 보면 암호화고객실명번호 값이 NULL이면서 고객실명번호 값이 NULL이 아닌 것은 전체 15백만 건 중 1600여건(약 0.1%)으로 확인되며, 게다가 그 값은 암호화 불가한 비정상적인 값이고 조회할 가능성이 없는 값이어서 결국 예외로직이 무의미한 것으로 판단함 (따라서, 데이터 정제 대상임) 개선 후 (개선 결과) 암호화고객실명번호 컬럼 조건절 및 접촉전화번호 컬럼 조건절이 인덱스 access 조건이 되면서 액세스량이  개선 전에 비해 현저히 감소함

조회 요건과 데이터 특성을 고려하여 데이터 유무 확인을 위한 1건 찾는 로직을 개선한다. 개선 전 (문제 상황) 상각내역_PK, 입력기본_PK 인덱스를 과다하게 액세스함 기준일자가 입력되지 않거나, 입력되더라도 넓은 범위인 경우 SKIP SCAN이 성능에 도움이 될 수 있으나, 상각내역_PK,  입력기본_PK 인덱스 모두 1건 또는 0건을 찾기 위해 너무 많은 인덱스 블록을 읽음(각각 7983블록, 4281블록 읽기) (문제 원인) 데이터 유무 확인 로직이 비효율적임 개선 방안 (개선 방안) 기준일자, 계좌번호 컬럼 순으로 구성된 상각내역_PK를 사용하여 사용자 입력 계좌로 데이터를 1건을 찾을 때,  과거부터 찾는 것 보다 최근부터 찾는게 평균적으로 더 빨리 찾을 수 있음을 전제로, 과거->최근 탐색(순방향 탐색,  index_ss힌트 사용) 대신 최근->과거 탐색(역방향 탐색, index_ss_desc힌트 사용)하도록 함(사실 계좌번호+기준일자로  구성된 신규 인덱스를 만드는게 더 확실한 개선 방안임) (개선 방안) 입력기본_PK 사용 유도한 index 힌트를 삭제하여, 자연스럽게 계좌번호 선두인 IX02 인덱스가 사용되도록 함 개선 후 (개선 결과) 2개 인덱스 액세스량이 개선 전 보다 현저히 감소함

옵티마이저가 잘못된 조인 방법을 선택한 경우, USE_NL, USE_HASH, USE_MERGE 등의 힌트를 사용하여 조인 방법을 바꿀 수 있다. 개선 전 (문제 상황) 온라인 처리 SQL문인데, 송금기본_IX04 인덱스를 과다하게 액세스함 (문제 원인) 온라인 처리에 부적합한 해시조인(HASH JOIN)이 수행됨 일반적으로 해시조인이 대량 배치 처리에 적합한 조인 방법이나, 온라인 처리이더라도 예외적인 경우  해시조인이 NL 조인보다 유리할 수 있음 개선 후 (개선 방안) "USE_NL(A B)" 힌트 사용하여 해시조인 대신 NL조인 수행되도록 함 (개선 결과) 송금기본 데이터 액세스량이 크게 감소함

옵티마이저가 잘못된 액세스 방법을 선택한 경우 INDEX, INDEX_DESC,  INDEX_SS, INDEX_FFS, FULL 힌트 등을 사용하여 액세스 방법을 바꿀 수 있다. 참고로, 인덱스 신규 생성이나 컬럼 추가는 인덱스 설계 변경으로 구분하고, 단순히 힌트를 통해, 있는 인덱스를 사용하도록 하는 것은 액세스 방법 변경으로 구분한다. 개선 전 (문제 상황) 고객관계 데이터를 과다하게 액세스함 (문제 원인) 주고객번호 컬럼 조건절이 액세스 범위를 크게 줄일 수 있고, 그 컬럼이 선두인 PK 인덱스가 있음에도  불구하고, 상대적으로 비효율적인 액세스 방법인 IX02 인덱스 사용 실행계획이 수립되었음 참고로, PK인덱스 사용하는 것과 IX02 인덱스 사용하는 것 모두 비용이 4로 동일하게 나오는데, (힌트를 주지 않는다면)  옵티마이저는 이런 경우 알파벳 정렬 순으로 앞에 있는 인덱스를 선택함 (비용계산에 중요한 요소인 컬럼 distinct 값은  아래와 같음)      개선 후 (개선 방안) PK 인덱스가 사용되도록 ‚INDEX(고객관계(주고객번호))‛ 힌트를 추가함 (개선 결과) 주고객번호 컬럼 조건절이 인덱스 access 조건이 되면서 인덱스 및 테이블 액세스량이 현저히 감소함

주요 조건절 컬럼으로 구성된 인덱스는 온라인 SQL 최적화에 가장 기본적이고 필수적인 도구이다. 만약, 인덱스 액세스 범위를 크게 줄일 수 있는 어떤 컬럼 조건절이 인덱스 filter 조건이라면, 인덱스를 신규 생성하거나, 현 인덱스에 컬럼을 추가하여 인덱스 access 조건이 되도록 하면 액세스 량이 크게 감소한다. 개선 전 (문제 상황) 결과 건수에 비해 거래요약_IX01 인덱스 액세스 량이 과다하고, 처리에 많은 시간이 소요됨 (문제 원인) 인덱스 정보를 확인해 보면, 인덱스 액세스 범위를 크게 줄일 수 있는 처리기준일자 조건이 인덱스 filter  조건으로 사용되었음을 알 수 있음(인덱스 구성 상 처리기준일자 앞 컬럼 인 재판매유형코드 조건절이 없기 때문임) 개선 후 (개선 방안) 처리기준일자 조건절을 인덱스 filter 조건 대신 access 조건이 되도록 하기 위해‚ "INDEX(T 거래요약_IX08)"   힌트를 추가함 (개선 결과) IX01 인덱스 대신 IX08 인덱스를 사용하면서 거래요약 인덱스 액세스량이 개선 전 57396개 블록에서, 개선 후  2199개 블록으로 크게 감소함

주요 조건절 컬럼으로 구성된 인덱스는 온라인 SQL 최적화에 가장 기본적이고 필수적인 도구이다. 만약, 액세스 범위를 크게 줄일 수 있는 어떤 컬럼 조건절이 테이블 filter 조건이라면, 인덱스를 신규 생성하거나, 현 인덱스에 컬럼을 추가하여 인덱스 access 또는 filter 조건이 되도록 하면 액세스 량이 크게 감소한다.  개선 전 (문제 상황) 거래내역 테이블을 15513회 만큼 액세스 후 결과 건수가 4362로 감소하였는데, 테이블 액세스에 많은 시간이  소요됨 (문제 원인) 데이터 확인을 통해 구분코드 컬럼 조건절이 15513에서 4361건으로 감소하게 된 결정적인 요인임을 확인함 개선 후 (개선 방안) 구분코드 조건절이 인덱스 access 조건이 되도록 현 IX05 인덱스에 구분코드 컬럼 추가함    변경 전 : 고객번호, 거래일자    변경 후 : 고객번호, 거래일자, 구분코드 (개선 결과) 인덱스 변경 반영함에 따라 구분코드 컬럼 조건절이 인덱스 access 조건이 되었고, 그 결과 테이블 랜덤 액세스 횟수가 15513회에서 4735회로 감소하였으며, 소요시간도 크게 감소함