전문가칼럼

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

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

전문가칼럼
DBMS별 분류
Oracle
작성자
dataonair
작성일
2016-01-27 00:00
조회
9642




◎ 연재기사 ◎


물탱크 구조로 알아본 오라클의 블록 옵션 ‘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 이야기: 쿼리(25회)

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



성공과 실패의 경험을 나누자, 용기와 희망을 나누자

개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB엔지니어로서 활동하면서 얻은 경험과 지식을나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서DB 분야에 입문한지 1~2년 된 기입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다. 물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.

전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등 개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다. 그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다. 이 연재는 시리즈로 나갈 것이다. 연재를 끝까지 읽는 독자라면 준전문가 수준의 DB 원리를 아는 것을 목표로 한다.



오라클 CBO 방식과 통계정보

이번 연재 내용은 공정쿼리와 인덱스 생성도에 관한 것이다. 예전 연재에서 이미 한번씩 언급한 내용이지만, 많은 분들이 재차 궁금한 점을 물어 오기도 하고 높은 관심을 보여 주었던 부분이었다. 따라서 이번에는 이전 연재의 내용에서 빠진 부분을 추가하고 내용 보완을 거쳐서 더 자세하고 쉽게 설명하고자 한다.

연재에 앞서 오라클 CBO 방식과 통계정보에 대해서 간단히 소개한다. 우리가 알고 있는 오라클 CBO(Cost Based Optimizer) 방식은 통계정보를 이용해 비용을 계산한 후 SQL을 수행한다. 통계정보가 존재하고 비교적 정확하다면, 오라클은 대부분의 쿼리에서 올바르고 적절한 플랜을 우리에게 제공하고 쿼리를 수행할 것이다.

만약 통계정보가 존재하지 않는다면 혹은 존재하지만 실제와는 차이가 있는 통계정보를 가지고 있다면, 오라클은 우리에게 정확한 플랜을 제공하지도 않을뿐더러 쿼리 수행도 최적이지 않다. 실제로 운영을 하다 보면, 이런 경우가 빈번히 발생함을 알 수 있을 것이다. 설사 통계정보가 존재하고 정확하다고 해도 항상 올바른 플랜을 제공한다고 보장하지는 못한다.

오라클 CBO 방식에서 비용이란 물리적인 비용이 아니라 논리적인 비용을 의미한다. 여기서 논리적인 비용이란 어떤 근거로 비용이 산출됐는지 명확하게 알 수 없다는 말과 동일할 수도 있다. 혹은 오라클에서 명시적으로 비용 산출 계산 방식을 공개하지 않는다는 말과도 동일하다.

이와 같은 여러 가지 이유로 인하여 우리는 알고 있는 업무 지식을 바탕으로 스스로 비용을 예측하고 플랜을 예상할 수 있어야 한다. 오늘 연재의 주된 내용은 테이블 접근 순서를 정하고 인덱스 생성도를 그리고 공정 쿼리를 작성하는 것에 있다.



철수네의 재래시장에서 장보기: 무엇을 살 것인가

철수는 곧 다가오는 설날 차례를 준비하기 위해 장을 보는 어머니를 따라 재래시장에 왔다. 시장 이곳 저곳을 돌아 다니면서 많은 물건을 샀다. 그런데 너무 많이 걸어 다녀서 다리가 무척 아팠다. 어머니께서 같은 곳을 계속해서 돌아 다니신다. 빨리 물건을 샀으면 좋으련만 어머니는 여전히 가게를 찾아 다닌다. 사탕 사준다는 어머니 말씀에 따라 나오기는 했지만 너무 힘들다. 맨 처음에 샀던 쌀 포대 때문에 어깨가 너무 아프다. 내년에는 절대로 따라 오지 않을 것이다. 사탕 2개로는 어림도 없다. ㅠㅠ

(사온 것: 쌀 1포대, 콩나물, 금복주 1병, 사탕2개, 쇠고기 1근, 사과, 가래떡, 고등어) - 철수의 일기 -

column_img_2313.jpg

[그림 1] 철수가 방문한 재래시장에서의 이동 경로

철수의 일기를 읽어 보니 무척 힘든 하루였음을 짐작할 수 있다. 철수 어머니는 사야 할 물건은 알고 있었지만, 재래시장의 이동 경로에 대해서는 고민을 하지 않은 것 같다. 또한 쌀 포대처럼 무거운 것은 제일 마지막에 샀으면 철수가 덜 힘들었을 것이다. 아마도 철수는 내년에도 어머니를 따라 재래 시장에 올 것이다. 그리고 힘든 경험을 또 할 것이다. 우리는 그것을 충분히 짐작 할 수 있다.



영희네의 재래시장에서 장보기: 무엇을 살 것인가 + 어떻게 살 것인가

다가오는 설날을 맞이하기 위하여 영희는 어머니와 함께 재래시장에 왔다. 시장 이곳 저곳을 돌아 다니면서 많은 물건을 샀다. 어머니는 사탕도 2개나 사주었다. 하나는 내가 먹었고 나머지 하나는 집에서 기다리는 귀여운 동생에게 주었다. 시장에는 많은 사람들이 있었고 가게도 무지 많았다. 신기한 것도 많이 구경했다. 더 구경하고 싶었으나, 옆집에 맡겨 놓은 동생 때문에 어머니는 발 걸음을 재촉하신다. 내년에는 동생도 같이 데리고 와야겠다고 생각하니 발걸음이 가벼웠다.

(사온 것: 가래떡, 사탕 2개, 금복주 1병, 쇠고기1근, 사과, 콩나물, 고등어, 쌀 1포대) - 영희의 일기 -

column_img_2314.jpg

[그림 2] 영희가 방문한 재래시장에서의 이동 경로

영희의 일기를 읽어 보니 무척 즐거운 하루였음을 짐작할 수 있다. 영희 어머니는 사야 할 물건도 정확히 알고 있었으며, 재래시장의 이동 경로에 대해서도 생각을 많이 하고 온 것 같다. 또한 쌀 포대처럼 무거운 것을 제일 마지막에 사는 센스도 갖고 계신다. 아마도 영희는 내년에도 어머니를 따라 재래시장에 올 것이다. 물론 동생도 함께 올 것이다. 우리는 그것을 충분히 짐작할 수 있다.



장바구니 = 무엇을(What) + 어떻게(How)

column_img_2315.jpg

동일한 장바구니 목록이지만 철수네는 무엇을 사야 하는지에 대한 고민만 있었고, 영희네는 어떻게 사야 하는지에 대한 고민도 있었다. 영희네 장바구니 목록에는 구입해야 할 목록이 가게 방문 순으로 적혀 있다.

영희네의 장보기 과정을 아래와 같이 쿼리로 표현해 보았다. SELECT 절에는 사야 할 목록이 있다. 또한 FROM 절에는 방문해야 할 가게가 순서대로 나열돼 있고 조인 절에는 접근 경로에 대한 조인 정보가 순서대로 표시돼 있다. 조건절에는 각각의 가게에서 사야 할 물품들이 적혀 있다.



SELECT 가래떡, 사탕2개, 금복주 1병, 쇠고기1근, 사과, 콩나물, 고등어, 쌀 1포대
FROM 떡집, 슈퍼, 정육점, 과일가게, 채소가게, 생선가게, 쌀가게
WHERE 떡집 ? 슈퍼
AND 슈퍼 → 정육점
AND 정육점 → 과일가게
AND 과일가게 → 채소가게
AND 채소가게 → 생선가게
AND 생선가게 → 쌀가게
AND 떡집 → 가래떡
AND 슈퍼 → 사탕 2개, 금복주 1병
AND 정육점 → 쇠고기 1근
AND 과일가게 → 사과
AND 채소가게 → 콩나물
AND 생선가게 → 고등어
AND 쌀가게 → 쌀 1포대



개발자들이 작성하는 쿼리도 장보기의 과정과 별반 다르지 않다. 여러분은 장보기를 효과적으로 하는가 쿼리를 올바르게 작성하고 있는가 스스로에게법으로 구현한 쿼리를 공정쿼리라 부른다. 우리는 쿼리 작성에 있어서 영희네의 장보기 메모지처럼 무엇을(What) 조회할지에 대한 쿼리 결과뿐만 아니라 어떻게(How) 조회할지에 대한 쿼리 과정도 포함돼야 할 것이다. 잘 작성된 공정쿼리에서 우리는 플랜을 알 수 있고 인덱스 생성 포인트도 알 수 있다.

공정무역(Fair Trade)과 더불어 최근에는 공정여행(Fair Travel)이란 말까지도 자주 회자되고 있다. 그렇다면 공정쿼리란 무엇인가 공정쿼리란 한마디로 개발자 간 의사소통이 되는 쿼리다. A 개발자가 작성한 쿼리를 B 개발자가 충분히 이해해야 한다.

다른 사람이 작성한 쿼리를 여러분이 쉽게 이해를 못한다면 이것은 공정쿼리가 아니다. 또한 여러분이 작성한 쿼리를 다른 사람이 쉽게 이해 못한다면 이 또한 공정쿼리가 아니다. 공정쿼리는 서로가 쉽게 이해할 수 있도록 약속된 규칙에 의해서 쿼리를 작성해야 한다.



공정쿼리 = 무엇을(What) + 어떻게(How)

아마 상당수 개발자들은 쿼리를 작성할 때, 무엇을(What) 조회해야 할 지에 대한 사실과 결과만을 중시하고 어떻게(How) 조회할 것인가에 대한 고민과 과정을 무시한 채 쿼리를 작성하였을 것이다. 아래 그림을 보면서 우리가 작성해야 할 공정쿼리(Fair Query)의 모습에 대해서 고민해 보자.

column_img_2316.jpg

무엇을 조회 할지에 대한 쿼리 결과도 중요하지만 어떻게 조회 할지에 대한 쿼리 과정도 중요하다. 한마디로 공정쿼리는 무엇을(What) 어떻게(How) 조회할지에 대한 내용이 모두 포함돼야 하는 것이다. 공정쿼리로 작성한 쿼리에서는 쿼리의 결과뿐만 아니라 쿼리의 과정 즉 생성해야 할 인덱스 정보와 접근해야 할 플랜(Plan) 정보를 모두 알 수 있다. 믿기 어렵겠지만 사실이다.

우리는 공정쿼리를 통하여 플랜 정보를 알 수 있고 인덱스 생성 포인트를 알 수 있다. 아래 그림은 공정 쿼리로 작성한 쿼리가 우리에게 어떠한 정보를 주는지를 분명하게 보여준다.

column_img_2317.jpg

공정쿼리에서 우리는 무엇을(What) 조회하는지 알 수 있고 어떻게(How) 플랜을 하는지, 어떻게(How) 인덱스를 만들어야 하는지 알 수 있다. 공정쿼리는 모든 개발자가 동일한 방법으로 쿼리를 작성함으로써 무엇을(What) 어떻게(How) 할 건지에 대한 내용을 서로 공유 할 수 있게 해준다.

공정쿼리를 작성하기 위한 가장 기본적인 규칙은 다음과 같다.

SELECT 절에 나열할 컬럼들은 테이블 접근 순서대로 나열한다.
FROM 절에 나열할 테이블은 접근 순서대로 나열한다.
조인절의 순서는 테이블 접근 순서대로 나열한다.
조건절의 순서는 테이블 접근 순서대로 나열한다.

참 단순하다. 결국 규칙은 딱 한가지다. 테이블 접근 순서다. 그렇다면 테이블 접근 순서는 어떻게 정할 것인가 테이블 접근 순서는 다음과 같은 규칙을 적용해야 한다.



테이블 접근 순서 규칙1 - 진입형 테이블을 결정하라!

column_img_2318.jpg

쿼리의 조건 중에서 가장 선택도가 좋은 컬럼의 테이블을 최초 진입형 테이블로 결정한다. 위의 그림에서 주문일자 컬럼의 선택도가 좋다면 주문 테이블을 먼저 접근 해야 하며, 고객명 컬럼의 선택도가 좋다면 고객 테이블을 먼저 접근해야 한다.



테이블 접근 순서 규칙2 - OUTER JOIN 보다 INNER 조인을 우선하라!

column_img_2319.jpg

위의 그림에서 INNER 테이블인 주문 테이블을 먼저 접근한다(주문 → 고객).

column_img_2320.jpg

위 그림에서 OUTER 테이블인 테이블3을 마지막에 접근한다(테이블1-> 테이블2 -> 테이블3).



테이블 접근 순서 규칙3 - 연결 확장형 보다는 연결 축소형 테이블을 우선하라!

column_img_2321.jpg

조인절 연결 시 레코드 확장형보다는 레코드 축소형을 우선한다. 위 그림에서 테이블2와의 조인이 레코드 축소형이고 테이블3과의 조인이 레코드 확장형 이라면 테이블3 보다 테이블2을 우선하여 접근한다(테이블1 -> 테이블2 -> 테이블3)

이와 같이 테이블 접근 순서를 정하는 규칙은 아래 3가지 규칙을 기본으로 한다.

테이블 접근 순서 규칙1 ? 진입형 테이블을 결정하라!
테이블 접근 순서 규칙2 ? OUTER JOIN 보다 INNER 조인을 우선하라!
테이블 접근 순서 규칙3 ? 연결 확장형 보다는 연결 축소형 테이블을 우선하라!

이러한 테이블 접근 순서를 그림으로 그린 것을 인덱스 생성도라 한다. 인덱스 생성도를 그리는 기본 규칙은 2가지가 있다.



인덱스 생성도 규칙1 - 왼쪽에서부터 오른쪽으로 접근한다. (A -> B -> C)

column_img_2322.jpg

인덱스 생성도 규칙2 - 위에서부터 아래로 접근한다. (A -> B -> C -> D)

column_img_2323.jpg

아래 그림처럼 인덱스 생성도 규칙1, 규칙2가 복합적으로 이뤄진 경우가 있다.

column_img_2324.jpg

테이블 접근 순서는 A -> B -> C -> D -> E 혹은 A -> B -> C -> E -> D이다. 규칙1과 규칙2가 공존하는 위치인 테이블 C에서는 규칙1과 규칙2 중에서 어떤 것을 우선시 할지는 판단해야 한다.

column_img_2325.jpg

만약 위의 그림처럼 두 가지 경우가 모두 가능한 경우라면 A -> B 혹은 A -> C로 접근 가능하다는 의미이므로 아래와 같이 구성 하여야 한다. 물론 우선 순위가 높은 것이 위로 온다.

column_img_2326.jpg



인덱스 생성도에 대한 이해

CBO(Cost Based Optimizer) 방식에서 플랜의 결정은 오라클이 주도적으로 하는 것처럼 보이지만 실제로는 수동적인 역할만 할 뿐이다. 단지 현재 시점에서 알고 있는 통계정보의 범위 내에서, 최소의 비용이 소요되는 플랜을 보여주는 것뿐이다.

일부 개발자들은 쿼리를 만들기만 하면 오라클이 가장 최선의 플랜을 알아서 척척 제공할 것으로 생각하지만 그렇지 않다. 오라클은 인덱스가 없으면 FULL SCAN 플랜을 보여줄 것이고, 잘못된 인덱스가 있다면 잘못된 플랜을 보여주는 수동적인 역할만 한다. FULL SCAN 발생 시 필요한 인덱스를 적시해 주거나, 잘못된 인덱스 사용시 최선의 인덱스를 조언해 주는 그런 능동적인 역할은 전혀 하지 못한다. 결국 개발자가 인덱스 생성도를 통해 최적의 인덱스를 생성하여 주도적으로 플랜을 결정해야 한다.

인덱스 생성도는 여러분이 제작한 복잡한 쿼리를 간단하게 도식화한 그림이다. 또한 인덱스를 생성할 위치를 알기 쉽게 해주는 그림이다. 아무리 복잡한 쿼리라도 인덱스 생성도를 이용한다면 인덱스 생성 위치를 쉽게 알 수 있다. 복잡한 쿼리에 대해서 인덱스 생성 포인트를 알고자 한다면 우선 테이블 접근 순서 규칙과 인덱스 생성도 규칙에 따라서 아래 그림과 같이 인덱스 생성도를 그린다.

column_img_2327.jpg

이때 화살표가 있는 부분이 인덱스가 있어야 하는 부분이다. 만약 그 위치에 인덱스가 존재하지 않는다면 생성해야 한다. 아래 그림의 화살표 부분 즉 빨간색 부분이 인덱스가 있어야 하는 부분이다.

column_img_2328.jpg

지금까지 인덱스 생성도에 대해서 그림을 통하여 자세히 설명을 했다. 인덱스 생성도는 복잡한 쿼리를 단순하게 도식화할 수 있고 또한 인덱스 생성 포인트에 대해서 정확하게 이해할 수 있는 좋은 방법이다. 많은 개발자들이 적극적으로 활용해 좋은 결과를 얻기를 희망한다.

인덱스 생성도는 개발자가 흔히 접하는 OLTP 쿼리의 대부분에 적용 가능하다. 하지만 배치성 쿼리, OLTP High concurrency Table, Creitical SQLs 들에 빈번히 사용되는 Table의 Index 생성 시에는 충분한 영향도 검증을 해야 됨을 명심하자.

오늘 연재를 통하여 우리는 오라클 CBO 방식에서 테이블 접근 순서에 대한 기본 규칙을 배웠다. 그리고 인덱스 생성도와 공정 쿼리를 통하여 플랜 정보를 알 수 있었고 인덱스 생성 포인트를 알 수 있게 되었다. 더불어 오라클에서 우리에게 일방적으로 제공하는 플랜 정보가 아닌 우리 스스로 비용을 예측하고 플랜을 예상하는 방식에 대해서 배웠다.

다음 연재의 내용은 오라클 파라메터에 대한 내용이다. DBA가 아닌 개발자로서 최소한 알아야 하는 오라클 파라메터에 대해서 설명한다.



[지난 문제의 정답과 풀이] 원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제

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



column_img_2329.jpg

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

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

column_img_2330.jpg