전문가칼럼

DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.

누구도 알려주지 않았던 ‘오라클 인덱스 생성도’의 비밀

전문가칼럼
DBMS별 분류
Oracle
작성자
dataonair
작성일
2014-10-20 00:00
조회
27273




◎ 연재기사 ◎


물탱크 구조로 알아본 오라클의 블록 옵션 ‘PCTFREE와 PCTUSED’


이산가족 찾기 생방송을 통해 배우는 DB 원리


개발자에게 맞는 DB 공부방법 찾기: 물리적 분류와 논리적 분류 그리고 인덱스


데이터베이스 인덱스의 오해와 진실


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (상)


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (하)


누구도 알려주지 않았던 ‘오라클 인덱스 생성도’의 비밀


누구도 알려주지 않았던 ‘오라클 쿼리 작성의 비법’


퀴리 최적화 및 튜닝을 위한 오라클 공정쿼리 작성법


만능 쿼리와 한 방 쿼리


오라클 옵티마이저 ‘CBO와 RBO’ 이해하기


재미있는 DB 이야기 ‘60갑자와 쿼리’


그림으로 배우는 ‘오라클 조인의 방식’ 이야기


반드시 알아야 하는 오라클 힌트절 7가지


오라클 플랜을 보는 법


개발자들의 영원한 숙제 ‘NULL 이야기’


알면 유용한 오라클 기능 ‘GATHER_PLAN_STATISTICS’


알면 유용한 오라클 기능들


오라클 DICTIONARY를 활용한 DB툴 프로그램 ‘FreeSQL’


이제는 말할 수 있다: 주식 자동매매 프로그램(상)


이제는 말할 수 있다: 주식 자동매매 프로그램(하)


개발자들이 자주 접하는 오라클 에러 메세지


재미있는 DB 이야기 ‘사라진 날짜를 찾아라’


오라클 랜덤 함수와 사용자 정의 함수


그림으로 배우는 ‘공정쿼리와 인덱스 생성도’


이병국의 개발자를 위한 DB 이야기: 디폴트 세팅의 함정과 오라클 파라미터


재미있는 DB 이야기 ‘놀라운 마방진의 세계’


오라클 운반 최소 단위 BLOCK


이병국의 개발자를 위한 DB 이야기: 이세돌과 알파고의 세기의 대결


이병국의 개발자를 위한 DB 이야기(30회) : DB 엔지니어의 가볍게 읽는 세상 이야기


이병국의 개발자를 위한 DB 이야기: 튜닝(31회) : 개발자를 위한 DB 튜닝 실전(1편)


이병국의 개발자를 위한 DB 이야기: 튜닝(32회) : 개발자를 위한 튜닝 실전(2편)


이병국의 개발자를 위한 DB 이야기: 튜닝(33회) : 개발자를 위한 튜닝 실전(3편)


이병국의 개발자를 위한 DB 이야기: 튜닝(34회) : 개발자를 위한 DB 튜닝 실전(4편)


이병국의 개발자를 위한 DB 이야기: 튜닝(35회) : 개발자를 위한 튜닝 실전(5편)


이병국의 개발자를 위한 DB 이야기: 페이징 처리에 대한 이해 (36회)


보기 좋은 떡이 먹기도 좋다 - 좋은 쿼리 좋은 성능


테이블의 수직분할과 수평분할에 대한 이해


DB 성능 제고를 위한 채번의 이해와 방식별 장단점 비교


이병국의 개발자를 위한 DB 이야기: 마지막회 : ‘개발자를 위한 DB 이야기’ 연재를 마치며



이병국의 개발자를 위한 DB 이야기: 인덱스 (7회)

누구도 알려주지 않았던 ‘오라클 인덱스 생성도’의 비밀



이병국 andongcn@dreamwiz.com 프리랜서 DB 엔지니어로서 동아제약 전산실에서 SW 개발 업무를 시작으로 프리랜서 개발자로 독립해 활동하던 중 우연한 기회에 DB와 인연을 맺게 됐다. 현재 삼성생명 전산 운영팀에서 쿼리 성능을 개선하는 DB 튜닝과 IOA 업무를 맡고 있다. 개발자 출신의 DB 엔지니어로 활동하면서 개발자에게 DB 관련 지식이 꼭 필요함을 절감했다. ‘정보의 불균형이 시장 왜곡을 가져온다’는 ‘레몬시장이론’은 중고차 거래에서 흔히 나타나기 쉽다. 좋은 차와 나쁜 차를 아는 중개인과 모르는 구매자 사이에는 정보의 비대칭 때문에 구매자가 손해를 볼 수 있다. 구매자도 차에 대해 기본적인 지식을 알고 있어야, 정보의 균형이 맞으므로 서로 손해를 보지 않고 합리적인 가격에 차를 거래할 수 있다. 마찬가지로 개발자들도 DB에 대해 기본적인 지식을 습득하여 정보의 균형을 맞추면, DB 엔지니어와 협업이 더 쉬워지고 한 단계 더 발전한 자신의 모습을 발견할 것이다.



성공과 실패의 경험을 나누자, 용기와 희망을 나누자
개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB 엔지니어로서 활동하면서
얻은 경험과 지식을 나누고자 한다.
DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서 DB 분야에 입문한 지
1~2년 된 기 입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다.
물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.
전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등
개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나
번역서보다는 조금 더 부드럽게 접근할 계획이다.
그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다.
이 연재는 시리즈로 나갈 것이다.
연재를 끝까지 읽는 독자라면, 준전문가 수준의 DB 원리를 아는 것을 목표로 한다.

이번 연재에서는 인덱스 생성도에 관한 내용이다. 아무리 복잡한 쿼리라도 간단하게 도식화할 수 있고, 인덱스 생성도를 통해 최적의 인덱스 포인트를 찾을 수 있다.



어머니의 심부름: 두부가게와 쌀가게

어릴 때 누구나 어머니의 심부름을 해 본 적이 있을 것이다. 필자도 바쁜 어머니를 대신해 많은 심부름을 하곤 했다. 어느날 어머니는 나에게 두부 한 모와 쌀 한 포대를 사오라고 심부름을 시켰다. 만약 여러분이 두부 한 모와 쌀 한 포대를 사오는 심부름을 하게 된다면, 어떤 가게부터 방문하겠는가 아마 대부분은 두부가게부터 갈 것이다. 필자도 당연히 두부가게에 먼저 방문하여 두부 한 모를 산 후에 쌀가게에서 쌀 한 포대를 사서 집으로 돌아왔었다.

column_img_1502.jpg
<그림 1> 어머니 심부름 하기: 두부가게와 쌀가게

필자가 DB 관련 교육을 여러 번 진행하면서 위와 같은 질문을 했을 때, 대부분의 사람이 1초의 망설임도 없이 두부 가게부터 방문한다고 대답했다. 쌀가게부터 방문한다는 사람을 지금까지 단 한 명도 본적이 없다. 어느 누구라도 쌀가게를 먼저 방문하여 무거운 쌀 한 포대를 사서 힘들게 어깨에 메고 두부 가게로 향하지는 않을 것이다. 누구나 본능적으로 최소한의 힘을 소모하는 방법으로 심부름을 할 것이다. 이것은 교육 받은 것이 아니라, 경험의 기억을 바탕으로 본능적으로 판단하는 거라고 필자는 생각한다.

우리가 최소한의 힘으로 심부름을 하듯이 오라클도 우리와 동일한 방식으로 쿼리를 분석해 가장 적은 비용으로 결과 데이터를 가져온다. 만약 이 글을 읽는 여러분도 두부 가게를 먼저 방문한다고 생각하였다면, 당신은 이미 타고난 DB 전문가가 될 소질을 갖췄다고 생각한다. 여러분은 아무리 어려운 심부름을 하더라도 최소한의 힘(비용)을 소모하는 방식으로 완벽하게 심부름을 할 수 있다. 이미 그런 능력을 가지고 있다. 이것이 인간의 능력이고 창조자가 우리에게 준 능력이다. 하지만 대부분의 개발자들은 이런 능력을 갖고 있음에도 안타깝게도 DB 쿼리에서는 그 능력을 충분히 발휘하고 않고 있다. 그 이유는 무엇 때문일까 필자는 그 이유를 다음과 같이 생각한다.

타인(혹은 사물)을 배려하는 마음이 부족하기 때문이다. 여러분은 자신을 사랑한다. 더 정확하게 말하자면, 절대로 자신을 고생시키려 하지 않는다. 그것은 본능이다. 자기 자신에 대해서 배려하는 만큼 오라클 서버에 대해서도 약간의 배려하는 마음을 가진다면, 훌륭한 DB 전문가가 될 수 있다. 여러분이 세심한 배려(생각) 없이 작성한 쿼리가 오라클 서버를 힘들게 한다. 때로는 오라클 서버를 죽게 한다. 여러분은 아무리 어려운 심부름을 맡게 되더라도 찰나적인 시간에 완벽하게 성공적인 계획을 세울 수 있다. 이와 같은 능력을 가진 여러분이 DB 전문가가 되지 못할 이유는 전혀 없다. 우리는 오라클에게 올바르고 쉬운 방법으로 심부름을 시켜야 한다. 올바르고 쉬운 방법으로 명령을 내려야 한다.

그렇다면, 올바르고 쉬운 방법이란 무엇인가 필자는 그에 대한 해법으로 인덱스 생성도를 제시한다. 인덱스 생성도는 여러분이 제작한 복잡한 쿼리를 간단하게 도식화한 그림이다. 또한 인덱스를 생성할 위치를 알기 쉽게 해주는 그림이다. 아무리 복잡한 쿼리라도 인덱스 생성도를 이용한다면 인덱스 생성 위치를 쉽게 알 수 있다. 앞서 살펴봤듯이 본능적으로 심부름을 잘 할 수 있지만, 오라클은 스스로 그러하지 못하다. 인덱스 생성도를 이용해 최적의 올바른 인덱스를 생성해 주어야만 오라클은 비로소 심부름을 잘 할 수 있다.



오라클의 RBO 방식과 CBO 방식

구체적으로 인덱스 생성도를 알아 보기 전에 먼저 오라클의 RBO(Rule Based Optimizer)와 CBO(Cost Based Optimizer)를 이해할 필요가 있다. RBO는 순위가 있는 규칙을 적용하여 SQL에 대한 실행 계획을 결정한다. 반면에 CBO는 SQL에 대한 최소한의 비용이 소요되는 플랜을 선택한다. 9i 버전까지는 함께 지원했지만, 10g부터는 CBO만 지원한다. 향후의 진행 방향으로 봤을 때는 CBO가 필수적이고 대세인 거 같다. CBO 방식은 주기적으로 통계정보(Analyze Object)를 갱신해 주어야 한다. 통계정보를 근거로 하여 여러 경우의 플랜 중에서 가장 비용이 적게 드는 플랜을 결정한다.

RBO 방식과 CBO 방식에 대한 더 자세한 내용은 다음 연재에서 구체적으로 다룰 예정이다. 일단은 아래의 <그림 2>를 보면서 CBO 방식에 대해서 간단하게나마 이해해 보자.

column_img_1503.jpg
<그림 2> 전국 여행 계획

위의 <그림 2> 에서 여러분은 최소한의 비용과 일정으로 여행하기 위한 계획을 수립한다면, 어떻게 하는가(방문할 도시는 6곳이며, 교통수단은 버스, 자전거, 도보 3가지만 가능하다고 하자).

아마도 여러분은 서해안에서 남해안으로, 이어서 동해안으로 여행하는 U자형 코스를 계획하거나 혹은 그 반대의 경로를 선택할 것이다. 여행 코스는 기껏해야 몇 가지에 불과할 것이다. 하지만 모든 케이스를 검토 한다면, 여행 코스는 아래와 같이 계산된다.

6곳의 도시를 방문하는 여행 코스의 수는 6팩토리얼 = 6! = 1*2*3*4*5*6 = 720가지
각 도시 간에 교통 수단으로 버스, 자전거, 도보 3가지를 선택 할 수 있으므로 3의 5승
결국 여행 코스는 720 * 3 * 3 * 3 * 3 * 3 = 174,960이다.

여행 코스가 17만 가지가 넘는다. 놀랍지 않은가 단지 도시 방문 순서와 교통 수단만 달리 했을 뿐이다. 실제 여러분은 17만 이상의 모든 경우를 검토하여 여행 계획을 수립하지는 않을 것이다. 여러분은 도시와 도시간의 위치정보 및 거리 정보를 이미 알고 있으며, 축적된 지식과 경험을 바탕으로 순식간에 정확한 계획을 수립할 수 있을 것이다. 그래도 헷갈린다면, 단 몇 가지 후보 코스로 줄인 다음에 생각을 거듭해 최종 결정할 것이다.

그렇다면 오라클에서는 어떠할까 위와 유사하게 테이블 6개의 조인 쿼리는 흔히 발생할 수 있는 쿼리다. 또한 테이블간 조인 방식은 Nested Loop Join, Sort Merge Join, Hash Join 3가지가 있다. 만약 통계정보에 대한 어떠한 정보도 제공되지 않는다면, 오라클은 CBO 방식으로 최선의 플랜을 선택하려면 17만 가지의 플랜을 모두 검토하여야 한다. 그 중에서 최소 비용의 플랜을 결정할 것이다. 물론 이것은 극단적인 경우를 설명한 것이다. 실제로 오라클 엔지니어가 그렇게 허술하게 CBO 알고리즘을 구현했으리라고는 생각하지 않는다.

필자가 생각하기엔 RBO 방식이든 CBO 방식이든 두 가지 모두 완벽한 방식은 아닌 것이 확실하다. 우리가 정확한 통계정보를 정기적으로 제공한다고 해도 오라클은 완벽한 플랜을 우리에게 제공하지 못한다. 향후 미래에는 가능할지 모르나 지금은 아니다(가까운 미래는 절대 아니다).

현재 상황에서 우리가 할 수 있는 최선의 방법은 정확한 통계정보를 정기적으로 제공하는 것이다. 또한 이번 연재의 주제이기도 한 인덱스 생성도에 따른 최선의 인덱스를 생성하는 것뿐이다. 오라클이 알아서 좋은 플랜을 우리에게 제공하기를 기대하면 안된다. 오라클은 우리가 기대하는 만큼의 알고리즘을 갖고 있지 않다. 우리 스스로 인덱스 생성도를 통해 최적의 인덱스 생성 포인트를 찾아야 하며, 그에 따라서 인덱스도 생성해야 한다.



인덱스 생성도의 기본 규칙

이제 본격적으로 인덱스 생성도에 대해 알아보자. 이번 연재를 정확하게 이해 하려면, 이전 연재인 ‘인덱스 끝장리뷰’를 먼저 읽어 보기 바란다. 인덱스 생성도는 지금까지 어떤 책이나 블로그에 소개된 적은 없다. 필자가 지금까지 수많은 프로젝트를 통해 시행 착오를 거치면서 터득한 나름대로의 방법이다. 지금까지 진행한 프로젝트에서 인덱스 생성도를 이용하여 많은 효과를 보았다. 그 효용성은 충분히 입증되었기에 자신있게 여러분에게 소개한다. 먼저 인덱스 생성도를 이해하기 위해서는 아래의 세 가지 기본 규칙을 알아야 한다.



column_img_1504.jpg

인덱스 생성도에 대한 이해

CBO(Cost Based Optimizer) 방식에서 플랜의 결정은 오라클이 주도적으로 하는 것처럼 보이지만, 실제로는 수동적인 역할만 할 뿐이다. 단지 현재 시점에서 알고 있는 통계정보의 범위 안에서, 최소의 비용이 소요되는 플랜을 보여주는 것뿐이다. 일부 개발자들은 쿼리를 만들기만 하면, 오라클이 최선의 플랜을 알아서 척척 제공할 것으로 생각하지만 그렇지 않다. 오라클은 인덱스가 없으면 FULL SCAN 플랜을 보여줄 것이고, 잘못된 인덱스가 있다면 잘못된 플랜을 보여주는 수동적인 역할만 한다. FULL SCAN 발생시 필요한 인덱스를 적시해 주거나, 잘못된 인덱스 사용시 최선의 인덱스를 조언해 주는 그런 능동적인 역할은 전혀 하지 못한다. 결국 개발자가 인덱스 생성도를 통하여 최적의 인덱스를 생성하여 주도적으로 플랜을 결정해야 한다.



column_img_1505.jpg

column_img_1506.jpg

column_img_1507.jpg

column_img_1508.jpg

column_img_1509.jpg

지금까지 인덱스 생성도에 대해 그림을 통하여 자세히 설명하였다. 인덱스 생성도는 복잡한 쿼리를 단순하게 도식화할 수 있고, 또한 인덱스 생성 포인트에 대해 정확하게 이해할 수 있는 좋은 방법이다. 많은 개발자가 적극적으로 활용해 좋은 결과를 얻기를 바란다. 인덱스 생성도는 개발자가 흔히 접하는 OLTP 쿼리의 대부분에 적용할 수 있다. 하지만 배치성 쿼리, OLTP High Concurrency Table, Critical SQLs 등에 빈번히 사용되는 Table의 Index 생성시에는 충분한 영향도 검증을 해야 함을 명심하자.

이것으로 인덱스 생성도에 대한 설명을 마무리한다. 다음 연재에서는 쿼리를 잘 작성하는 방법에 대해 소개할 것이다. 서점에 가면 일반적이고 기능적인 데이터베이스 지식을 제공하는 책은 많다. 다음 연재의 내용은 그러한 기능적인 쿼리 제작 기법에 관한 내용이 아니다. 쿼리 제작에 있어서 철학을 갖고 전략적으로 접근하는 쿼리 제작 방법에 대해서 설명하고자 한다.



용기를 갖자
오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 어느 날 ‘하늘에서 뚝 떨어져 새로 만들어진 것’이
아니라 실생활에서 이용되는 혹은 이미 상식 수준에서 인지되는 그런 보편적인 원리를 바탕으로
만들어졌으므로 쉽게 접근하고 이해할 수 있다.
서두에서 말했듯이 ‘레몬시장이론’을 상기하며 DB를 지레짐작으로 어려워하지 말고
용기를 내고 하나씩 터득해 나가기를 바란다.
이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서 더 친숙하고 더 쉽게 이해하고
접근하길 바랄 뿐이다. 이 글을 읽으면서 궁금하거나 의문 나는 점이 있으면, 댓글을 달아주실 것을 적극 바란다.
아무리 어렵고 힘든 일이더라고 ‘관계’와 ‘소통’으로 풀어나갈 수 있음을 다시 한 번 믿으며...



[지난 문제의 정답과 풀이]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제



지난 연재에 출제한 ‘원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제’에 대한 정답과 해설은 아래와 같다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다.



column_img_1510.jpg

정답 : 보편적으로 비용이 가장 적게 드는 경우 1번 < 2번 < 3번

최소한의 비용이 발생할 수 있는 경우 3번, 홍길동이 첫 번째 블록에 있는 경우
최대한의 비용이 발생할 수 있는 경우 3번, 홍길동이 마지막 블록에 있는 경우

해설 : 1번의 경우는 분포도가 50% 이므로 테이블에서 200건의 레코드를 읽는다.
2번의 경우는 인덱스 접근 및 테이블 Random Access 100번 발생
3번의 경우는 홍길동이가 첫 번째에 있을 수도 있고 극단적으로 마지막에 있을 수도 있음



[이번 호 문제]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제


각 연재의 말미에 간단하면서도 재미있고 생각해 보는 문제를 출제하려 한다. 모든 문제는 DB의 원리를 이해할 수 있는 문제로 출제할 예정이다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다. 정답과 그에 대한 설명은 다음 연재에서 한다.



column_img_1511.jpg