데이터이야기

DB 노하우, 데이터직무, 다양한 인터뷰를 만나보세요.

조시형의 ‘친절한 SQL 튜닝’ : 중간값 검색(양쪽 % LIKE 조건)에 마법을 걸다.

데이터 이야기
작성자
dataonair
작성일
2019-07-11 00:00
조회
10939


조시형의 ‘친절한 SQL 튜닝’

중간값 검색(양쪽 % LIKE 조건)에 마법을 걸다.

기고자 조시형 증명사진
기고자 조시형

- 現) 디비안 대표이사 / 前) 엔코아, 비투엔

- 제1회 우수DB人상 수상 / 국가공인 SQLP, DAP 자격검정 전문위원

- 저서 : 친절한 SQL 튜닝, 오라클 성능 고도화 원리와 해법, 비용기반의 오라클

원리(공역)



‘친절한 SQL 튜닝’, ‘오라클 성능 고도화’ 저자로 유명한 조시형은 엔코아, 비투엔

을 거쳐 지금은 디비안 대표이사를 맡고 있다. 제1회 우수DB人상을 수상하기도 한 그는 20년 가까이 컨설팅을 수행하면서 터득한 성능관리 방법론을 솔루션화하는 일에 관심이 많고, DB 전문서적을 꾸준히 집필하면서 교육과 후배 양성에도 더 매진하려고 노력 중이다. 요즘은 인터넷 카페를 통한 지식공유 활동에도 열심인데, 그가 운영하는 ‘디비안 포럼(www.dbian.net)’을 방문하면 DB 관련 고급 기술자료, SQLP/DAP 자격증에 관한 정보를 습득할 수 있고, 직접 저술한 책에 대한 Q&A 서비스도 받을 수 있다.



가장 어려운 튜닝 중 하나가 중간값 검색입니다.

SELECT 고객번호, 고객명, 로그인ID, 주민등록번호, 전화번호

FROM 고객

WHERE 로그인ID LIKE '%' || :keyword || '%'

OR 주민등록번호 LIKE '%' || :keyword || '%'


Full Scan을 피할 수 없기 때문입니다. FullText Search 기능을 이용하거나 별도 검색 엔진을 활용하는 것 외에 방법이 거의 없습니다. Full Scan을 빠르게 하기 위해 Index Fast Full Scan을 활용하는 정도를 생각해 볼 수 있으나 테이블이 매우 클 때는 극적인 성능 개선을 기대하기 어렵습니다.

※ 중간값 검색을 위한 Index Fast Full Scan 활용 : 오라클 성능 고도화 2권 54~56페이지 참조

늘 중간값으로 검색하는 업무라면 어쩔 수 없지만, 대부분 시작 값으로 검색하고 가끔 중간값으로 검색하는 경우라면 방법이 없진 않습니다. 가끔(1%) 필요한 중간값 검색을 위해 일반적인(99%) 시작 값 검색에도 아래와 같이 양쪽 % LIKE 조건을 사용한다면 너무 억울한 일이죠.


시작값 검색 99%, 중간값 검색 1%로 걸린 조건문



고객_X01 : 로그인ID + 주민등록번호 결과 값


현업 담당자를 찾아가 우선 "프로그램이 느려서 불편하셨죠"라며 위로의 말을 건넵니다. 그리고 (눈물 흘리는) 현업 담당자에게 아래와 같이 제안합니다.


"중간값 검색이 필요할 때는 키워드 왼쪽에 %를 붙여서 검색하실 수 있을까요 익숙해지면 어렵지 않아요. 그렇게만 해 주시면 시작 값으로 검색할 때(99%) 항상 0.1초 만에 조회되게 튜닝해 드릴게요."

프로그램이 느려서 고생했던 현업 담당자는 흔쾌히 그렇게 튜닝해 달라고 부탁합니다. 얼른 자리로 돌아와 SQL을 아래와 같이 구현해 주면 끝!!!!!!!!!!!!





고객_X01 : 로그인ID + 주민등록번호 결과 값, 고객_X02 : 주민등록번호 결과 값


아래 조건절은 :keyword 변수에 입력된 값이 '%'로 시작하는 경우를 의미합니다.

where :keyword like '\%%' escape '\'

아래 조건절은 :keyword 변수에 입력된 값이 '%'로 시작하지 않는 경우를 의미합니다.

where :keyword not like '\%%' escape '\'

키워드 왼쪽에 % 조건을 붙여서 검색한 경우(UNION ALL 위쪽 브랜치), 위에서 언급한 '중간값 검색을 위한 Index Fast Full Scan 활용' 기법을 적용하면 금상첨화, 화룡점정이겠네요.

오늘도 힘찬 하루 보내세요.


위 글은 무단 복사 및 링크는 금지합니다.
글과 관련한 문의사항이 있으면, 아래로 연락 주시기 바랍니다.

biz@dbian.co.kr

070-4806-8246

www.dbian.co.kr

www.dbian.net




출처 : 한국데이터산업진흥원

제공 : 데이터 전문가 지식포털 DBguide.net