데이터이야기
DB 노하우, 데이터직무, 다양한 인터뷰를 만나보세요.
조시형의 ‘친절한 SQL 튜닝’ 중간값 검색(양쪽 % LIKE 조건)에 마법을 걸다.
위 글은 무단 복사 및 링크는 금지합니다.조시형의 ‘친절한 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%) 항상 0.1초 만에 조회되게 튜닝해 드릴게요."
프로그램이 느려서 고생했던 현업 담당자는 흔쾌히 그렇게 튜닝해 달라고 부탁합니다. 얼른 자리로 돌아와 SQL을 아래와 같이 구현해 주면 끝!!!!!!!!!!!!
아래 조건절은 :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