전문가칼럼

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

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

전문가칼럼
DBMS별 분류
Oracle
작성자
dataonair
작성일
2014-11-04 00:00
조회
20231




◎ 연재기사 ◎


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

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



이병국 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 원리를 아는 것을 목표로 한다.

이번 연재에서는 쿼리 작성법에 대해서 소개한다. 서점에 가면 일반적이고 기능적인 지식을 제공하는 책은 많지만, 그러한 기능적인 쿼리 제작 기법에 관한 내용이 아니다. 쿼리 제작에 있어서 철학을 갖고 전략적으로 접근하는 쿼리 제작 방법에 대해 설명하고자 한다.



공정무역과 공정여행 그리고 공정쿼리

여러분들은 신문이나 방송을 통해 공정무역(Fair Trade)에 대한 내용을 이미 많이 접했을 것이다. 공정무역(Fair Trade)이란 국제 무역의 무한 경쟁에서 밀려난 소규모 생산자들과 노동자들의 삶을 개선하는 것을 목적으로 출발하였다. 지금은 그 의미가 더 확대되어 생산자도 행복하고 소비자도 행복한 무역을 의미하기도 한다.

대표적인 공정무역의 예로는 커피를 들 수 있다. 우리가 자주 마시는 커피 원두를 생산하는 곳은 세계적으로 최빈국들이다. 다국적 기업들이 최빈국 노동자들의 값싼 노동력을 발판으로 수급한 커피를 우리는 마셨다. 이 구조에서 커피 재배 농가에 돌아가는 이윤은 겨우 우리가 마시는 커피 한잔 값의 0.5% 밖에 안된다고 한다. 이러한 불평등과 노동 착취를 해결하고자 공정무역 커피가 생겨났다.

공정무역 커피는 다국적 기업이나 중간상인을 거치지 않고 생산자와 소비자 간 직거래를 기본으로 한다. 이 구조를 통해 원두 생산자에게는 노동한 만큼의 최저 가격을 공정하게 보장하고, 소비자에게는 인체에 해로운 성분이 섞이지 않은 품질 좋은 커피를 마시게 하는 것이다.

공정무역과 더블어 최근에는 ‘공정여행(Fair Travel)’이란 용어도 유행이다. 공정여행은 공정무역에서 가져온 말로서 ‘착한여행’이라고도 한다. 여행을 하면서 현지인의 삶에 도움이 되는 방식으로 소비를 하고, 자연 환경까지 생각하는 아름다운 여행을 의미한다. 공정여행의 실천 지침으로는 아래와 같은 내용이 있다.

1. 여행지의 생활 방식과 문화를 존중하고 예의를 갖춘다.
2. 문화유산을 훼손하지 않는다.
3. 멸종 위기에 놓인 동식물로 만든 기념품을 사지 않는다.
4. 음식을 남기지 않는다.
5. 일회용품 사용을 줄이고 쓰레기를 버리지 않는다.
6. 현지 주민의 노동력을 혹사하지 않고 공정한 요금을 지불한다.
7. 아동노동착취 혹은 성매매 투어를 하지 않는다.
8. 여행 후에는 후기 작성 등 적극적인 피드백으로 지속 가능한 여행이 되도록 돕는다.
9. 현지인의 인권을 존중하며, 약속한 사진이나 물건은 반드시 보낸다.

공정여행 지침은 위에 나열한 것보다 훨씬 많으나 그중에서도 필자가 중요하다고 생각되는 것만 골라 보았다.

필자는 주말마다 캠핑을 열심히 다니는 캠핑 마니아다. 캠핑이 지금처럼 유행하기 전부터 캠핑을 다녔었고 지금도 다니고 있다. 근래에 캠핑 문화가 유행하면서 캠핑 인구가 폭발적으로 증가하였는데, 이에 따른 부작용도 많아 보인다.

예전에는 조용한 캠핑 문화였으나, 요즘은 밤늦게까지 주변을 소란스럽게 하는 경우를 적지 않게만난다. 또한 캠핑장 관련 법규 미비로 인하여 전국에 수많은 불법적인() 캠핑장 개설로 아름다운 자연 환경이 많이 훼손되었다. 게다가 캠핑장에서 먹을 음식도 대부분 도시에서 사와서 요리를 하므로 캠핑장 주변의 수많은 펜션과 매점 및 식당들은 장사가 안돼서 많은 고통을 겪고 있다고 한다. 그렇다고 캠핑을 하지 말자는 이야기는 아니다. 될 수 있으면 해당 지역의 특산물이나 유명 음식도 먹어보고, 돌아 오는 길에 지역 농산물도 구매했으면 좋겠다. 이것이 공정 여행의 실천이 아닐까 싶다.



공정쿼리

‘공정무역(Fair Trade)과 공정여행(Fair Travel)에 대해 이야기가 왜 이리 길어!’ 했을 독자를 위해 지금부터 시작이다. 필자가 오늘 소개할 주제는 공정쿼리(Fair Query)다. 공정쿼리란 누구나 쉽게 쿼리를 작성하고 이해하는 것을 목표로 한다. 각각의 개발자는 본인이 작성한 쿼리를 다른 개발자가 쉽게 이해할 수 있도록 공정하게 쿼리를 작성하여야 한다. 공정하게 작성한 쿼리는 어느 누가 보더라도 쉽게 빠르게 쿼리의 의미를 전달할 수 있다.

다른 사람이 작성한 쿼리를 여러분이 쉽게 이해하지 못한다면 이것은 공정쿼리가 아니다. 또한 여러분이 작성한 쿼리를 다른 사람이 쉽게 이해 못한다면 이 또한 공정쿼리가 아니다. 공정쿼리에서는 서로가 쉽게 이해할 수 있도록 약속된 규칙에 따라 쿼리를 작성해야 한다. 오늘의 연재 내용은 바로 그러한 규칙들에 대한 설명이다.



무엇을 + 어떻게 = 공정쿼리

개발자 초년생 시절, 필자는 쿼리를 작성할 때 무엇을(What) 조회해야 한다는 한 가지 사실만을 중시하고 어떻게(How) 조회할 것인가에 대한 고민을 하지 않았다. 그렇게 수년간 작성한 쿼리에 대한 죄책감(원죄)를 아직도 가지고 있다.

그 당시 어느 누구도 쿼리 작성법에 대한 방법을 알려 주지 않았다. 또한 지금까지 어떠한 도서에도 쿼리 작성 방법론에 대한 내용을 본적이 없었다. 후일 어느 정도의 경력과 전문성이 쌓이고 나름대로 쿼리 작성법에 대한 규칙과 방법을 정립하게 되었는데 그 내용으로 사내 개발자들에게 여러 차례 교육을 진행하였다. 이번에 뜻하지 않은 좋은 기회를 만나 약간의 내용을 보완하여 여러분에게 소개하는 기회를 얻게 되었다.

column_img_1519.jpg

<그림 1> 공정쿼리= 무엇을(What) + 어떻게(How)

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



나막사 주부의 심부름 메모지

<그림 2>를 보면서 공정쿼리에 대한 설명을 구체화하려 한다. <그림 2>는 맞벌이를 하는 ‘나막사’ 주부가 일찍 퇴근하는 남편에게 마트에 가서 사야 할 것들을 적은 메모지다. <그림 3>은 메모지에 적힌 순서에 따라 남편이 실제로 매장을 방문한 경로를 표시한 것이다.

column_img_1520.jpg

<그림 2> 나막사 주부가 남편에게 남겨 놓은 메모

column_img_1521.jpg

<그림 3> 메모지에 적힌 순서에 따라 나막사 주부의 남편이 매장을 방문한 경로



나계획 주부의 심부름 메모지

<그림 4>를 보면서 공정쿼리에 대한 설명을 구체화하려 한다. <그림 4>는 맞벌이 주부인 나계획 주부가 일찍 퇴근하는 남편에게 마트에 가서 사야 할 물건을 적은 메모지다. <그림 5>는 메모지에 적힌 순서에 따라 남편이 매장을 방문했던 실제 경로를 표시했다.

column_img_1522.jpg

<그림 4> 나계획 주부가 남편에게 남겨 놓은 메모

column_img_1523.jpg

<그림 5> 메모지에 적힌 순서에 따라 나계획 주부의 남편이 매장을 방문한 경로

우리는 나막사 주부의 심부름 메모지와 나계획 주부의 심부름 메모지를 보면서 중요한 차이점을 발견할 수 있다. 그것은 바로 매장방문 접근경로 순서 정보의 존재여부다. 두 주부의 메모지는 얼핏 같아 보이지만, 나계획 주부의 메모지에는 구매할 물건(What) 리스트뿐 아니라, 매장을 어떤 경로(How) 접근해야 하는지에 대한 정보까지 포함되어 있다.

우리는 쿼리 작성에 있어서 나계획 주부의 메모지처럼 무엇을(What) 조회할지에 대한 쿼리 결과 뿐만 아니라 어떻게(How) 조회할지에 대한 쿼리 과정도 포함해야 할 것이다. 잘 작성된 공정쿼리에서 인덱스 생성 포인트뿐 아니라 플랜 정보도 알 수 있다.



공정쿼리! 반드시 이렇게 작성하라!

공정쿼리 작성법을 이해하려면 앞서 소개했던 ‘누구도 알려주지 않았던 오라클 인덱스 생성도의 비밀’을 반드시 먼저 숙지해야 한다. 우리는 지난 연재에서 아래 <그림 6>과 같은 인덱스 생성도에 대해 살펴봤다. 아래 쿼리에 대한 인덱스 생성도를 다시 한 번 되짚어 보자.

column_img_1524.jpg

<그림 6>인덱스 생성도

지난 연재의 내용인 인덱스 생성도에 대해 다시 한 번 짚어 보았다. 이제 공정쿼리에 대해 본격적으로 알아보자. 아래 <그림 7>은 나신입 사원이 작성한 쿼리다. 일반적인 쿼리로서 무난해 보인다. 특별히 잘못된 부분은 찾을 수 없으며, 나신입 사원이 신경을 써서 열심히 작성한 것같다.

과연 그럴까
최선의 쿼리인가
잘못된 부분이 없다면 괜찮은 쿼리인가

필자는 그렇지 않다고 생각한다. 물론 쿼리 자체에는 전혀 문제가 없지만 공정쿼리는 아니다.

column_img_1525.jpg

<그림 7> 나신입 사원이 작성한 쿼리

나신입 사원이 작성한 <그림 7> 쿼리를 보면서 인덱스 생성도를 그려보자. 그리고 다시 공정쿼리를 작성해 보자.

column_img_1526.jpg

<그림 8> 나신입 사원이 작성한 쿼리와 인덱스 생성도

나신입 사원이 작성한 쿼리를 보면서 우리는 <그림 8>과 같이 인덱스 생성도를 그렸다. 우리는 이를 바탕으로 다음과 같은 정보를 알 수 있다.

테이블 접근 순서: 주문 → 고객 → 부서
조인절 접근 방향: 2번컬럼 → 1번컬럼, 3번컬럼 → 4번컬럼
인덱스 생성 위치: 5번컬럼(상품코드 + 주문일자), 1번컬럼(고객번호), 4번컬럼(부서번호)

공정쿼리를 작성하기에 앞서 다음 3가지 공정쿼리 작성규칙을 이해하자.

첫째, FROM 절에 나열할 테이블 순서는 인덱스 생성도의 테이블 접근 순서와 동일하다.
둘째, 조인절의 접근 방향은 인덱스 생성도의 조인절 접근 방향과 동일하다.
셋째, 조건절의 순서는 인덱스 생성도의 테이블 접근 순서에 따른 조건절 순서와 동일하다.

column_img_1527.jpg

<그림 9> 나신입 사원이 작성한 쿼리를 공정쿼리로 재작성

우리는 재작성된 <그림 9>의 공정쿼리를 통하여 플랜 정보를 알 수 있고, 인덱스 생성 포인트를 알 수 있게 되었다. 아래 <그림 10>은 동일한 결과를 조회하는 두 개의 쿼리지만 공정쿼리로 작성한 쿼리가 우리에게 어떠한 정보를 추가적으로 주는지를 분명하게 보여준다.

column_img_1528.jpg

<그림 10> 나신입 사원이 작성한 쿼리와 재작성한 공정쿼리의 비교

공정쿼리에서 우리는 무엇을(What) 조회하는지와 어떻게(How) 플랜을 하는지, 어떻게(How) 인덱스를 만들어야 하는지까지 알 수 있다. 여러분이 작성한 쿼리 때문에 다른 사람들이 고통을 받는 일이 없어야 하며, 또한 다른 사람이 작성한 쿼리로 인하여 여러분이 고통 받는 일도 없어야 한다. 공정쿼리는 모든 개발자가 동일한 방법으로 쿼리를 작성함으로써 무엇을(What), 어떻게(How) 할 건지에 대한 내용을 서로 공유할 수 있게 해준다.

지금까지 쿼리 작성법을 그림을 통해 자세히 설명했다. 이것으로 공정쿼리에 대한 설명을 마무리한다. 다음 연재에서는 쿼리에 대해 구체적이고 직접적인 내용으로 여러분을 다시 찾아 뵙겠다.



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

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

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

column_img_1529.jpg

해설 : 다수의 블로그에서 주장하는 ‘IN절과 EXISTS 중에서 어떤 것이 더 좋은가’ 라는 논쟁은 무의미하다고 생각한다. 기존에 이미 인덱스가 존재한다면, 그 인덱스 상황에 따라 IN절과 EXISTS 중에서 적합한 것을 선택하여 쿼리를 작성하면 된다. 만약 인덱스가 없는 상태라면 작성한 쿼리에 따라서 IN절에 적합한 인덱스를 생성하든지 혹은 EXISTS에 적합한 인덱스를 생성하면 된다. IN절과 EXISTS 중에서 어떤 것이 더 좋다라는 논쟁보다는 그때 그때 상황에 따라서 최적의 방법을 결정하여 사용하면 된다.



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

column_img_1530.jpg

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