부서-사원 모델에 대한 단상

조회관계란 무엇인가
전달관계와 조회관계의 차이점


거의 모든 프로젝트 현장에서 위와 같은 잘못된 모델이 등장한다. 위의 부서-사원 모델에서 틀린 곳을 발견할 수 있는가? 잘못된 점을 발견하지 못했다면, 여러분도 잘못된 모델링을 하고 있을 가능성이 높다. 이 글은 부서와 사원 사이에 존재하는 관계선이 잘못되었다는 것을 이야기 하고자 한다.

 

어느 설계자가 논리모델링을 하고 있었다. 그가 작업중인 위의 모델을 보는 순간 기대를 했지만 역시나 실망했다. 왜냐하면 부서엔티티에서 사원엔티티로 관계선을 긋는 것을 보았기 때문이다. 부서와 사원은 가장 기본적인 엔티티 아닌가? 핵심엔티티임에도 불구하고 관계선을 마음 내키는 대로 긋고 있는 것이다. 그래서 내가 그 설계자에게 다음과 같이 질문을 하였다.

 

필자: 사원엔티티와 부서엔티티의 관계는 사원이 현재 소속된 부서를 의미합니까?

설계자: 네 당연히 사원의 현 소속부서를 의미합니다.

필자: 그렇다면 부서와 사원 사이에 관계선을 긋지 마시고, 그냥 부서코드속성을 추가하세요.

설계자: 왜요? 부서와 사원은 1:N 관계이므로, 당연히 그어야 되는 것 아닌가요?

 

설계자의 잘못이 아니다

그렇다. 당연히 그렇게 생각할 것이다. 모델링공부를 많이 한 사람일수록 반사적으로 부서와 사원 사이에 관계선을 그어 버린다. 잘못된 관행이 온 세상을 덮고 있다. 왜냐하면, 영문원서를 포함하여 거의 모든 국내의 모델링 책에 부서-사원관계를 그어버리는 잘못을 범했기 때문이다. 교과서가 잘못되어 있는데 학생이 제대로 된 사고를 할 수 있을까? 부서와 사원간에는 이런 식으로 관계를 맺으면 안 된다.

 

위의 모델이 잘못되었다는 것을 증명하려면 모델링에 대한 약간의 배경지식이 필요하다. 지금부터 설명되는 내용은 모델링의 기본이므로 반드시 알고 있어야 하는 것들이다.

 

관계의 방향

엔티티간의 관계선은 Role, Cardinality, 관계속성의null 여부, 그리고 데이터의 전달방향을 나타낸다. 물론 관계선에는 이런 개념 이외에도 중요한 개념이 더 있지만, 오늘은 모델링 책에 자주 언급되고 있지 않는 데이터의 흐름(전달)과 그 방향에 관해 논의해보자.

 

물은 중력의 법칙 때문에 위에서 밑으로 흐른다. 물의 흐름과 데이터의 흐름도 비슷하다. 집합끼리의 관계에 의해서 최상위 부모로부터 최하위 자식까지 데이터는 순차적으로 물 흐르듯 흘러간다. 다시 말해, 데이터의 방향은 부모로부터 자식으로 흘러가는 것이다. 이런 일이 가능한 이유는 관계속성 때문이다.

 

관계속성의 정의

관계선을 긋는 순간 자식엔티티에 부모의 식별자가 상속된다. ER-WIN이나 파워디자이너 등의 모델링 툴을 써본 사람이라면 관계선을 긋는 순간 자식엔티티에 부모식별자에 해당하는 속성이 생성됨을 알 것이다. 이를 관계속성이라 한다. 관계선이 데이터의 이동 통로라고 비유한다면, 관계속성은 이동된 데이터의 도착장소이다. 부모집합은 관계속성이 존재함으로써 자식에 데이터를 전달(연결)할 수 있다. 쉬운 말로 표현하면, 관계속성은 부모식별자의 데이터를 자식의 공간에 집어넣어, 두 집합간의 관계를 완성한다.

 

고객, 상품, 주문, 주문상품을 통하여 데이터가 어떻게 흘러가는지 살펴보자. 아래의 그림에서 FK라고 표시된 것이 관계속성이다. 각 엔티티의 상세한 속성이 더 존재하지만 편의상 몇 개씩만 나타내었다.




우리의 예상대로 고객-->주문 , 주문-->주문상품 , 상품-->주문상품으로 데이터가 흘러감을 직관적으로 알 수 있다. 모델링을 해본 사람이라면 지극히 당연하다고 생각 할 것이다. 관계속성이 존재함으로 데이터가 흘러간다는 규칙은 업무에 무관하게 적용되는 모델링의 기본 법칙이다

 

관계선을 그으면 안 되는 경우

데이터가 전달되지 않을 때는 관계선을 그으면 안 된다. 관계속성이 생성되기 때문이다. 관계속성이 있다는 것은 데이터를 전달하겠다는 의미인데, 데이터가 전달되지 않는 경우에는 관계속성이 필요 없다.

 

 "부모 식별자의 데이터가 자식으로 전달(이행)되는 경우만 관계선을 그어라."

 

위에서 보는 것처럼 이 글에서 언급한 관계선의 규칙은 매우 간단하다. 하지만 아주 쉽고, 당연한 규칙이라도 실무에서는 지켜지지 않는 경우가 많다. 도대체, 언제쯤 대부분의 설계자들이 모델링의 기본규칙을 지키는 날이 올까? 만약 그랬다면 이 글의 서두에서 언급한 대화는 일어나지 않을 것이다.

 

여기까지가 배경지식이다. 지금부터 이미 언급된 부서-사원간의 관계가 왜 잘못된 것인지 알아보자. 결론부터 이야기 하자면 위에서 언급한 관계선의 규칙에서 벗어나기 때문이다.

 

현 소속부서의 함정

내가 본 많은 수의 부서-사원 모델은 관계명에현 소속부서로서가 명시되어 있다. 현 소속부서로서? 현 소속부서라는 것은 논리적으로 존재할 수 없다. 현 소속부서가 존재한다면 그것은 발령의 마지막 값을 물리적으로 역정규화 한 것이다.

 

관계선의 검증방법

관계선은 업무규칙을 나타내기도 하지만 데이터집합의 원천을 표시하는 용도가 있다. 즉 부서와 사원 사이에 관계선을 그렸다면, 사원엔티티의 부서코드 값들은 부서엔티티로부터 온 것인가? 라고 검증해보아야 한다. 실제로 부서와 사원의 관계선을 검증해보면, 사원의 부서코드는 부서집합으로부터 온 것이 아니다.. 인사시스템의 발령에서 사원의 부서코드로 데이터가 insert 혹은 update된다. 인사시스템의 발령이 데이터의 원천이므로 부서와 사원은 아무런 관계가 없다. 따라서 관계선을 삭제해야 하며 아래와 같이 나타내야 한다.

 



위의 그림은 인사시스템의 모델이다. 물론 발령에는 직무코드, 직위코드와 관련된 추가적인 관계가 존재한다. 하지만 부서와 사원에 집중하기 위해 생략하였다. 위의 모델에서는 당연하게도, 부서와 사원은 관계선이 없다. 즉 현 소속부서라는 집합은 인사발령에서 사원 별로 가장 최근 값만 추출한 것이다. 그럼 인사시스템이 아닌 다른 시스템이라면 어떻게 될까? 아래의 모델을 보자.

 

인사시스템이 아닌 타 시스템이라면 인사시스템의 부서와 사원테이블을 1:1로 중복시켜 놓을 것이다. 관계선에 D라고 표시한 것은 추출관계를 나타낸 것이다. 부서코드, 직위코드, 직무코드는 인사시스템의-발령엔티티에서 역정규화 된 것임을 알 수 있다. 가끔 인사시스템에서 부서코드, 직위코드, 직무코드를 미리 역정규화 해놓는 경우도 있다. 미리 역정규화 해놓은 것을 타시스템으로 1:1 Copy 해도 역정규화 되었다는 사실은 변하지 않는다위의 모델을 자세히 보아도 부서와 사원은 아무런 관계가 없음을 알 수 있다.

 

부서와 사원간의 FK는 필요 없다

부서와 사원간에 FK를 생성하려는 사람이 있다. 부서와 사원간에 FK에 의한 참조무결성은 쓸모가 없다. 데이터가 부모에서 자식으로 전달될 때, 올바른 값으로 전달(insert 혹은 update) 되었는지 체크하는 것이 참조무결성이다. 그런데 부서-사원간의 관계에서는 부모인 부서로부터 자식으로 데이터가 이행되지 않는다. 오히려 사원의 자식인 인사발령에서 데이터가 거꾸로 전달된다. 따라서 FK는 의미가 없다.

 

현소속부서라는 집합의 정합성을 검증하려면, 인사시스템의 발령에서 사원 별로 가장 최근 값만 추출하여 정합성을 검증해야 한다. 사원의 부서코드뿐만 아니라 직위코드, 직무코드도 마찬가지 방법으로 검증해야 한다. 실제로도 정합성 체크는 이렇게 하고 있다.

 

조회관계(Read Only Relationship)란 무엇인가?

조회관계란 데이터의 부모자식간의 데이터 전달이 목적이 아니라, 오직 두 집합을 연결하여 조회하려는 목적으로 탄생된 관계이다. 그래서 조회관계를 Read Only Relationship으로 부를 수 있다. 부서와 사원간의 관계 역시 역정규화에 의한 조회관계이다. 오직 사원의 자식인 인사-발령에서 현소속부서라는 데이터가 전달되므로, 부모인 부서집합에서의 전달관계는 없다. 하지만, 사원의 입장에서 현소속부서명과 부서의 위치를 알려고 하면, 부서와 사원을 연결할 수 있어야 조회가 가능하다. 특정 사원의 부서명을 조회하려면 부서와 조인하라.”는 정보가 필요하다. 따라서 조회관계라 하더라도 모델상에 어떤 식으로든 나타내어야 한다.

 
역정규화는 조회관계를 발생시킨다

조회관계는 역정규화를 하는 경우에 나타난다. 예를 들어 영국 프리미엄 리그를 보면, 리그와 팀을 배정해야 경기를 할 수 있다. 만약, 팀 엔티티의 속성에 리그코드가 있다면 그것 또한 역정규화에 의한 조회관계이다. 왜냐하면, 새로운 리그가 시작되기 전에, 지난 리그의 팀 성적에 따라서 각 팀들을 리그에 배정하는 작업이 존재할 것이기 때문이다. (예를 들면, 맨유는 1부 리그에 배정되었다.) 각 팀들을 리그에 배정하는 엔티티는 인사시스템의 발령에 해당한다. 물리모델단계에서 배정 엔티티의 리그코드는 역정규화 되어 팀 엔티티로 들어갈 수 있다. 하지만, 역정규화는 성능과 개발생산성을 위한 작업이므로 개념이나 논리모델에서 보다는 물리설계단계에서 나타내는 것이 적합하다. 

 

전달관계와 조회관계의 차이

일반적으로 우리가 알고 있는 관계는 전달관계이다. 전달관계는 부모식별자의 데이터를 자식에 전달하는 역할과, 두 집합을 조인하여 조회하는 역할을 모두 수행한다. 따라서 전달관계는 조회관계의 기능을 포함한다. 전달관계와 반대로 조회관계는 오직 두 집합을 연결하여 데이터를 조회하는 목적 밖에 없다.

 

조회관계를 어떻게 표현할 것인가

조회관계는 관계속성으로 데이터가 전달되지 않으므로 의미가 없다고 생각 할 수도 있다. 하지만 위에서 설명 한 것처럼 “특정 사원의 부서명을 조회하려면 부서와 조인하라.”는 정보를 인식할 수 있어야 한다. 따라서 아래와 같이 나타내는 것을 권장한다.

 

부서와 사원 사이의 관계선에 P를 표시하여 가상의 관계임을 나타내었다. 가상관계는 관계속성을 만들지 않는다. 또한 관계명에 조회관계라는 것을 명시해주어, 데이터가 부서에서 사원으로 전달되지 않음을 나타내었다. 그리고 부서코드와, 직위코드, 직무코드는 인사시스템의 발령테이블에서 역정규화된 된 속성이라는 것을 속성의 정의란에 나타내 주어야 한다. 회색부분은 외부(External) 엔티티를 나타낸 것이다. 외부엔티티를 사용하여 데이터의 원천을 나타내주면, 개발자가 데이터를 이행할 때 쉽게 참조할 수 있다. 즉 위의 모델을 그려놓으면인사시스템에서 데이터가 바뀌면 내 시스템의 부서와 사원 데이터를 동기화 해야 하겠군하고 명확히 판단할 수 있다.

 

위의 모델을 그림으로써 얻을 수 있는 정보는 세 가지이며, 다음과 같다.

1. 부서와 사원간의 관계는 전달관계가 아니라 조회관계이다.

2. 부서와 사원엔티티는 인사시스템이 원천이다.

3. 부서코드와, 직위코드, 직무코드에 해당하는 데이터는 인사시스템의 발령데이터가 원천이다.

 

또한 위의 세가지 정보로 다음과 같이 두 가지 장점을 얻을 수 있다.

1. 차세대 프로젝트의 데이터를 이행하는 사람은 데이터의 원천이 인사시스템의 부서, 사원, 발령 모델임을 인식하므로 매핑정의서를 쉽게 작성할 수 있다.

2. 개발자는 인사시스템의 부서나 사원, 발령의 데이터가 변경되면, 인사시스템이 아닌 타시스템의 부서와 사원도 동기화 해야 한다는 사실을 쉽게 알 수 있다. 물론, 동기화 프로그램의 작성도 모델을 참조할 수 있으므로 그만 큼 쉬워진다.

 

하지만 현실은……

실무에서는 거의 100% 아래와 같이 모델링 한다.



위의 모델에서는 어떤 정보와 어떤 장점을 얻을 수 있나? 거의 없다. 한가지 얻을 수 있는 것은 부서집합의 부서코드 데이터가 사원에 전달된다는 거짓정보이다. 이 정보에 의해서 사원의 부서코드는 부서로부터 상속되었다는 잘못된 생각을 하는 사람이 있다. 이에 따라 불필요한 FK를 생성하는 사람도 있다. 다시 한번 말하지만 사원의 부서코드는 부서 엔티티로부터 상속된 것이 아니라 인사시스템의 발령엔티티로부터 온 것이다. 

 

조회관계와 전달관계는 확실히 구분하기 바란다. 그렇게 하면, 모든 것이 드러나고 명확해진다.

 

반박의 논리

이 글을 몇몇 모델러에게 보여주었더니 반발이 있었다. 그런데 이상한 것은 대부분 반대입장만 표현하고, 반대의 적절한 이유가 없다는 것이다. 예를 들면, “내가 지금까지 이렇게 사용했어도 문제가 없었다혹은 “~책에 그렇게 하라고 되어있다가 대표적인 이유였다. 그런 것들은 이유가 될 수 없다. 반박을 하려면 이유가 있어야 한다. 이 글의 논리 중에 어느 부분이, 어떻게 잘못되었다고 지적 할 수 있는 능력이 필요하다. 그나마 이유를 댄 사람들은 아래와 같다.

 

전달관계로 표현해도 문제가 발생하지 않는다는 의견에 대해

그렇다. 전달관계로 표현해도 FK만 생성하지 않는다면 성능저하와 같은 문제는 발생하지 않는다. 하지만 프로젝트의 개발과정에서 여러 사람이 불편을 겪을 것이다. 조회관계의 개념을 모른다면 역정규화 되었다는 사실을 인식하기가 어렵다. 그러므로 인사시스템의 발령데이터가 변경되면, 트리거 성으로 타시스템에 동기화 해야 된다는 사실을 발견하는데 시간이 더 걸릴 것이다. 데이터를 이행하기 위한 매핑정의서를 작성할 때도 마찬가지로 어려움이 예상된다. 만약 모델러가 이런 모든 정보들을 안다고 해도 다른 사람들까지 모두 안다고 생각하면 안 된다. 모델은 정확히, 그리고 자세히 표현할수록 여러 사람이 얻는 이익이 많다.

 

우리회사는 발령이 없다는 의견에 대해

영세한 업체라면 발령이라는 엔티티가 없을 것이다. 인정한다. 그런 경우에는 부서-사원은 1:N로 직접적인 관계가 있으므로 관계선을 그어야 한다. 하지만 인사발령이 없는 영세한 업체라면 SI 프로젝트를 하지도 않을 것이며, 설계자나 모델러를 쓰지도 않을 것이다. 따라서 대부분의 경우 부서-사원의 현소속부서 전달관계가 존재한다면 잘못된 것이다.

 

우리 시스템에는 발령이 필요 없다는 의견에 대해

맞는 말이다. 인사시스템을 제외하면, 발령이라는 엔티티는 필요 없을 것이다. 하지만, 그 이유로 부서-사원간에 관계선을 긋는 것은 말이 안 된다. 발령이 필요 없다는 이유로, 존재하지도 않는 부서-사원간의 전달관계와 그에 따른 관계속성을 만든다는 것은 적절하지 않다. 사원의 부서코드는 전달관계속성이 아니라 역정규화된 추출속성이라는 엄연한 진실을 가리는 것이다.

 

모델링툴에서 조회관계를 표현하지 못한다는 의견에 대해

가장 그럴 듯한 반박논리다. 조회관계를 표현하는 기능이 없으므로 전달관계선을 그어야 한다는 것이다. 모델링툴 때문에 전달관계선을 긋는 것 보다는, 관계를 맺지 말고 누구나 볼 수 있도록 코멘트(Text Box)로 조회관계임을 기술하는 것이 더 나아 보인다. 어쩔 수 없이 전달관계선을 그을 수도 있다. 하지만, 개념을 알고 행동하는 것과 개념을 모르고 행동하는 것은 큰 차이가 있다. 다시 말해, 조회관계란 개념을 알고 있지만, 모델링툴에 기능이 없기 때문에 눈물을 머금고 관계선을 그은 것이라면 정당한 이유가 된다. 만약 그것이 아니라 부서-사원의 관계를 습관적으로 그은 것이거나 부서-사원의 관계가 1:N이라고 잘못 인식하고 관계를 맺었다면 옳지 않은 행동을 한 것이다.

 

현재 가장 대중적으로 사용하고 있는 ER-WIN이나 파워디자이너 같은 모델링툴은 조회관계를 나타낼 수 없다. 툴의 설계자가 조회관계라는 개념을 것을 모르니 당연한 것이다. 따라서 관계를 맺으면 무조건 전달관계와 전달관계속성을 만들어 버린다. 그에 따라 툴에서 제공하는 테이블 생성용 스크립트를 받아보면 예외 없이 FK를 생성해 버린다. 부모로부터 데이터가 전달되지 않으므로 참조무결성을 보장할 필요가 없는 데이터임에도 쓸데없이 FK를 생성하여 속도만 저하시킨다.

 

조회관계를 표현하는데 가장 유리한 모델링툴은 DA#이다. 필자가 가장 애용하는 툴이기도 하다. 이 툴에서 가상관계를 이용하면 관계속성을 만들지 않는다. 물론 가상관계가 아니라 조회관계를 표현할 수 있으면 좋겠지만, 그런 기능은 없으므로 현재로써는 가상관계로 처리하는 것이 최선이다. 위에서 그린 ERDDA#으로 표현한 것이다.

 

관계의 분류

관계는 여러 가지로 분류할 수 있다. 흔히 통용되는 관계분류의 예제는, 직접/간접 관계와 식별/비식별 관계이다. 직접관계란 1촌끼리의 관계를 의미한다. 즉 나와 아버지의 관계(1)이다. 관계형 데이터 모델에서는 직접관계만 표현하면 된다. 하지만 가끔 물리설계시에 SQL의 성능을 높이기 위해 할아버지가 직접 손자와 관계를 맺는 일도 있다. 이를 간접관계라고 한다. 식별관계는 부모의 식별자가 자식식별자의 일부로 상속되는 경우이다. 이와는 반대로 비식별관계는 부모의 식별자가 자식에 일반속성으로 상속된다. 이상으로 일반적인 관계의 분류방법을 알아보았다.

 

이 글에서 언급한 관계분류방법은 일반적으로 통용되는 분류방법과 다르다. 분류의 초점을 데이터의 전달유무에 맞추었다. 즉 전달/조회 관계로 새롭게 분류해 보았다. 직접/간접관계 분류법과 전달/조회관계 분류법은 많이 다르지만, 관계의 역정규화라는 점에서는 유사하다. 하지만, 데이터의 전달유무에서는 차이가 있다. 간접관계는 부모식별자의 데이터가 직접 자식으로 상속되는 전달관계이다. 이와는 반대로 조회관계는 부모로부터 데이터를 받지 않는다.  

개념이나 논리모델에서 조회관계 표현방법
될 수 있으면, 역정규화된 관계는 개념/논리모델단계에서 나타내지 말고, 물리설계단계에서 나타내기 바란다. 물리설계단계에서는 조회관계임을 명시하거나, 간접관계임을 나타내어 관계가 역정규화 되었음을 나타낼 수 있다. '그럼 개념이나 논리에서는 어떻게 나타내야 하는가?' 라고 질문할 수 있다. 개념모델단계에서는 부서와 사원 사이의 관계를 M:N으로 나타내면 되고, 논리모델단계라면 M:N 관계를 풀어서 외부엔티티인 인사발령을 표현해주면 된다. 논리모델에서 외부엔티티를 사용하는 것은, 물리모델링시에 조회관계(역정규화)로 나타낼 것임을 예고하는 것이다.  ( 2011.02.28 추가 )

 

결론

조회관계는 부모로부터 자식으로 데이터를 전달하지 못하고, 오직 조회를 목적으로 두 집합을 연결한다.

전달관계는 부모로부터 자식으로 데이터를 전달하고, 조회를 목적으로 두 집합을 연결도 한다.

부모로부터 자식으로 데이터가 이행되는 경우만 전달관계선을 그어야 한다.

부모로부터 자식으로 데이터의 전달이 끊기면 조회관계이다.

조회관계는 관계속성이나 FK를 생성하지 않는다.

역정규화에 의해서 조회관계가 발생된다.

부서--<사원은 조회관계이다.

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://rainlethe.tistory.com BlogIcon 레인레테 2011.02.25 06:01 신고  댓글주소  수정/삭제  댓글쓰기

    FK에 대한 새로운 관점이군요. 찬찬히 생각을 해봐야 할 듯 합니다..

  2. 어느모델러 2011.02.25 08:23 신고  댓글주소  수정/삭제  댓글쓰기

    부끄럽습니다. 관계선을 그었던 1인
    동규님의 글은 공통점이 있네요.
    서론, 본론, 결론에 따라 내 생각이 아래처럼 바뀝니다.
    항상 이런 패턴으로 글을 쓰시는것 같습니다.

    서론 : 말도 안되는 글인데 어떤 이야기를 하는지 들어보자.
    본문 : 일리가 있는 글이군
    결론 : 앞으로 그렇게 하면 안되겠네

    감사합니다.

  3. 혈기린 2011.02.25 10:46 신고  댓글주소  수정/삭제  댓글쓰기

    김기창 수석님 블로그에서 댓글로 언급하셨던 내용에 대한 상세한 설명이시네요
    그 댓글보고 엔티티에 대해서 아무 생각없이 당연한 관계라고했던 것에 대한 생각을 고치는 계기가 되었네요 좋은글 계속 감사 드립니다 ~~
    모델링에 대한 글도 자주좀 올려주세요 눈이 번쩍 뜨이게 ㅎㅎ

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.25 11:06 신고  댓글주소  수정/삭제

      네 김수석님 블로그에 제가 이와 관련된 댓글을 작성한 적이 있습니다. 제가 링크를 함부로 달지 않는데, 김수석님 블로그는 너무 좋은 내용이 많아서 안달수가 없었습니다.
      그럼 수고하세요

  4. Favicon of http://xenerdo.com BlogIcon 제너시스템즈 2011.02.25 11:29 신고  댓글주소  수정/삭제  댓글쓰기

    엄청 어려운 일이네요. 쭈욱 두 번 읽어봤는데 사실 제 머리로는 아직 이해가 안가는 부분이 많아요^^;;; 다시 한번 더 읽어봐야겠어요^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.25 16:55 신고  댓글주소  수정/삭제

      두번 읽으셨는데 이해가 안가신다면 그건 제 잘못입니다.^^
      어느 부분이 이해가 안가시는지 설명해 주시면 설명을 추가하도록 하겠습니다.

      이 글의 핵심은 부모의 식별자 값이 자식의 관계속성으로 들어오면(insert 되면) 전달관계이고 들어오지 않으면 조회관계라는 것입니다. 부서(부모) 사원(자식)의 경우는 데이터가 부모로 부터 오는것이 아나라 오히려 자식격인 인사시스템의 발령에서 오는 것입니다. 따라서 조회관계라고 할 수 있습니다.

  5. 혈기린 2011.02.25 13:36 신고  댓글주소  수정/삭제  댓글쓰기

    한가지 궁금한게 있는데요 조회관계 전달관계의 명칭이 관계형 이론에 있는 관계의 종류인가요 아니면 오수석님께서 임의로 만든 용어인가요?
    몇권은 안되지만 제가 읽은 모델링 서적에는 없는 용어이기에 질문을 드립니다.
    모델러 마다 엔티티의 분류도 조금 다르더군요

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.25 13:58 신고  댓글주소  수정/삭제

      전달관계/조회관계는 제가 만든것 입니다. 용어를 만들었다기 보다는 개념을 만든것 입니다.

      EF 코드 박사가 제시한 관계형이론에는 조회/전달 관계라는 용어는 없습니다. 이 용어뿐만 아니라 직접/간접 관계라는 용어도 없습니다. 관계형이론은 아주 짧은 글이므로 그 이론이 만들어진 후에 여러 개인(전문가)이 추가적인 용어를 만든것 입니다. 혈기린님의 말에 의하면 관계형이론에 없는 것은 모두 임의로 만든거라 할 수 있습니다.^^ 하지만 임의로 만든것이라도 그것이 옳은 개념이고, 그 개념으로 더 나은 모델을 만들수 있다면 더 이상 개인적이고 임의적이라고 볼 수는 없겠지요. E사의 이화식 사장님과 오픈메이드의 김기창 수석도 마찬가지로 이론에 없는 여러가지 용어를 사용하고 있습니다.

      전달관계, 조회관계라는 용어는 제가 최초로 만든것이므로 그 어떤 모델링책에도 언급되지 않습니다. 부서-사원같은 잘못된 모델을 그대로 두고 볼 수 없기에 만든것 입니다. 하지만, 앞으로 나올 모델링 책에서는 이 개념을 사용하여 실수를 방지하는 것이 좋을 것입니다.

  6. finecomp 2011.03.01 01:38 신고  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 글 감사합니다...;
    전달관계/조회관계...개념만큼이나 선택하신 용어도 맘에 딱 듭니다...^^;

    저는 이전부터 잘못된 원인을 약간 다른 관점에서 보고 있었습니다.
    부서-사원의 관계를 전달관계로 설정한 후, 필요에 의해 사원의 소속조직 이력관리의 개념으로 발령엔티티가 나타났고,
    발령을 관계맺은 후에도 처음 맺어졌던 부서-사원관계를 그대로 두었고,
    이 후 이런 모델을 만들었던 노련한 모델러 일수록 습관적으로 관계선을 그렸고 이것이 굳어져 현재는 오류로 보이는 듯 합니다.

    이와 유사하게 아직은 담당관리센터에 대한 이력관리가 필요치 않은 고객-센터(관리센터)의 예와 비슷할 듯 합니다.
    고객과 센터가 처음에는 전달관계로 설정되겠지요.
    이 후 필요에 의해 고객의 관리센터이력을 추가해야 할 필요성이 생기는 순간, 부서-사원과 동일한 문제가 발생하게 되겠지요.

    즉, 많은 경우의 전달관계에 대해서, 이런 유형의 이력이 생기는 순간 원래의 관계가 현행화 될 필요가 생길 수 있을 것 같습니다.

    결론적으로, 최초설계/변경 시에 좀 더 신중히 관계를 검토하고 조회관계 시엔 코멘트라도 잘 달아야겠다는 반성이 생기는군요...;

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.03.02 09:08 신고  댓글주소  수정/삭제

      저도 몇년 전 까지는 이런 오류를 범했습니다.
      그런데 finecomp님은 이미 잘못된 원인을 다른 관점에서 보고 있었네요. 그리고 개념과 용어가 마음에 드신다니 다행입니다.

  7. 에너자이져 2011.03.10 19:31 신고  댓글주소  수정/삭제  댓글쓰기

    전달관계=직접관계, 조회관계=간접관계 의 등식이 성립되는거 같습니다.
    직접/간접관계 보다는 전달/조회관계 라는 용어가 이해를 빠르게 하네요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.03.11 10:07 신고  댓글주소  수정/삭제

      이해가 빠르다고 하시니 다행입니다.
      전달관계=직접관계, 조회관계=간접관계가 성립하듯이
      직접관계=전달관계, 간접관계=전달관계도 성립합니다.
      감사합니다.

  8. 김상래 2011.06.12 11:42 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 데이터 모델링을 하면서 항상 관계(Relationship)에 대해 많이 고민하게 되는데,
    상당히 의미있고 중요한 부분을 집어내어서 명확히 풀어내신것 같습니다. 저도 개인적으로
    머리속에 두서없이 비슷한 생각을 하고 있었는데 오늘 이 글을 통해 어느정도 자연스레 정리된 느낌이네요.

    저는 저의 언어로 이렇게 정리를 했습니다. 확인차 몇가지 질문도 함께 드려봅니다

    1. 저는 관계를 크게 엔터티간에 종속적인 관계와 참조적인 관계가 있다라고 보는데,
    블로그에 쓰신 전달관계와 조회관계라는 용어의 맥락에서 볼때
    종속적인 관계는 분명 부모 식별자의 데이터가 (관계속성으로) 자식으로 전달되니
    부모 자식의 관계가 명확한 종속적인 관계 = 전달관계로, 당연히 관계선이 표현되어야 하고
    RI 제약 생성의 대상이 된다고 볼수 있습니다.
    즉, 종속관계는 전달관계로 관계가 표현되어야 한다는 것은 의심의 여지가 없습니다.

    2. 문제는 조회관계와 참조관계인데.. 종속관계가 아닌 참조관계는 블로그에 언급된
    전달관계가 있는 참조관계와 단순 조회관계인 참조관계를 포함하는 개념으로 보아야 할것 같습니다.
    예를들어 고객의 직업에 있어서, 직업코드와 직업명을 관리하는 직업엔터티가 코드성으로 존재할 때
    고객 엔터티에 직업코드가 일반 속성으로 존재한다면 이 경우는 전달관계가 있는 일반적인 참조관계로 보고
    RI를 적용하여 FK Constraint를 생성하여 직업엔터티에 존재하는 직업코드만이
    고객의 직업코드로 들어올수 있게 한다는 개념으로 정리해도 될까요?

    3. 그렇다면 블로그에 언급하신 조회관계는 모두 역정규화에 의해 발생한 속성으로부터 발생된 것을
    의미하는 것인가요? 이 글에서 언급하신 사원의 부서코드와 같이 외부시스템(외부테이블)과의 관계에 의해
    나온, 즉 데이터 중복인 역정규화 속성으로 발생한 참조관계인 경우,
    이를 조회관계라는 용어로 언급하신게 맞는지 궁금합니다.

    정리하면 블로그에서 언급된 전달관계/조회관계라는 용어와 제가 생각하는
    종속관계/참조관계 용어의 추상화정도와 관점을 맞춘다고 봤을 때, 아래와 같이 귀결시킬수 있을지 의견 부탁드려봅니다.

    전달관계 : 종속관계 전체 및 역정규화된 속성에 의해 발생하는 단순 조회관계를 제외한
    참조관계(일반적인 Non-identifying)까지를 포함
    조회관계 : 외부테이블과의 관계에 의해 발생한 속성에 따른 참조관계로, Relationship을 관리할 필요가 없다.
    다만 Join의 대상이 되므로 모델링 툴에서 표현을 할 필요는 있다.

    아.. 그런데 이렇게 글로 정리하다보니 제가 생각하는 참조관계란 결국 애매하게 조회관계까지를 포함하는
    개념이었듯 한데, 엄밀하게 정리를 하자면 전달관계가 있는 종속관계와 전달관계가 있는 참조관계, 그리고 전달관계가 없는
    반정규화에 따른 조회관계가 남게 되는군요...

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2013.05.31 18:24 신고  댓글주소  수정/삭제

      2년이 지났지만 답변드리겠습니다.
      전달관계는 식별관계이든 아니든 상관없이 상위 엔티티의 식별자가
      하위 엔티티의 속성으로 상속되는 것입니다.
      위 쪽의 고객과 주문간의 관계는 일반 속성으로 상속되었으므로 비식별 관계이면서 전달 관계입니다. 이와는 반대로 상품과 주문상품의 관계는 식별자로 상속되었으므로 식별 관계이면서 전달 관계입니다.
      조회관계는 외부테이블 이든 아니든 역정규화에 의한 관계입니다. 위의 예제에서는 max(마지막) 값을 가져다 놓은 것을 언급했습니다.
      감사합니다.

  9. 김시준 2013.05.31 12:24 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 감사드립니다.

  10. 주지영 2013.09.26 17:32 신고  댓글주소  수정/삭제  댓글쓰기

    모델링 접한지 얼마안된 DB관련경력자입니다. 책을 보면서도 속시원하게 뭔가 풀리지 않고 답답한 구석이 있었는데 ...
    심지어 모델링 공부하는 사람에게 물어봐도 '이건 당연하게 사용했던거라...'라는 답변을 꼭 들어야만 했습니다.
    앞으로 종종 들러 많은 가르침 배우고 싶네요.
    감사합니다(__)~

  11. Favicon of http://oracledo.tistory.com BlogIcon #도상 2016.11.28 10:01 신고  댓글주소  수정/삭제  댓글쓰기

    잘 읽었습니다. 관계선에 대해 다시 한번 생각하고 정리 할 수 있는 기회였습니다.

분석함수의 비효율을 찾는 방법
분석함수의 성능개선 원리 

분석함수의 실행계획 3부 - 심화과정의 모범답안

이번 글이 분석함수의 실행계획 시리즈의 마지막이다. 1
2에서 분석함수의 실행계획에 대해서 알아보았다
이 글(3부)은 새로 작성되었으며
, 이전 글( 문제 )은 삭제하지 않고 그대로 두었음을 밝혀둔다. 나중에라도 문제를 풀어볼 사람은 이 글(답안)을 보지 말고, 이전 글을 보기 바란다. 각각의 답안은 오렌지색으로 표시해 두었으므로 쉽게 채점할 수 있을 것이다.


분석함수의 다섯 가지 원리
많은 수의 개발자, DBA, 튜너들은 분석함수를 만나면 식은 땀을 흘린다. 왜냐하면, 분석함수의 비효율을 어떻게 찾을 수 있는지, 또 그 비효율은 어떻게 제거 할 수 있는지 알 수 없기 때문이다. 기껏해야 분석함수의 over절을 만족하는 적절한 인덱스를 생성하는 정도이다. 어쩌면 이런 결과들은 당연하다고 볼 수 있다. 매뉴얼이나 튜닝 책에 분석함수의 비효율을 발견하는 방법과 개선방법에 대한 언급이 별로 없기 때문이다. 이런 어려움을 겪고 있는 여러 사람들의 요구에 의해서 이 글이 만들어졌다. 분석함수의 비효율을 찾고, 성능을 향상시킬 수 있는 다섯 가지 방법을 공개하니, 많은 사람들이 적용하여 더 이상 식은 땀은 흘리지 않기 바란다. 

SQL 두 개에 5가지 비효율이 있다. 이것들을 제거하라
이 글에서 설명되는 두 개의 SQL은 길이가 매우 짧다그 중 하나는 비효율을 찾아내기 쉽고나머지 하나는 어렵다. 두 개의 SQL에는 총 5가지의 튜닝 포인트가 있다. 각각의 포인트는 20점이며 5가지를 모두 맞추는 경우는 100점이 된다. 채점 시 중요한 점은, 독자들이 직접 튜닝한 SQL이 모범답안과 같은지 비교하는 것이 아니라는 것이다. 그것 보다는 비효율을 몇 개 잡아내었는지, 또한 각각의 비효율을 개선시킬 수 있는 방법은 몇 개나 생각했는지를 채점하는 것이다. 이렇게 채점하면, 내가 아는 것은 무엇이고, 모르는 것은 무엇인지 명확히 드러난다, 무엇을 더 공부해야 하는지 알 수 있다.


다른 사람들은 몇 점 정도 받았나?
몇몇 지인들에게 퀴즈를 내본 결과 100점은 아무도 없었다. 80점이 평균적인 튜너의 수준이라는 이야기 이다. 개발자의 경우는 60점이면 상위 수준이다. 점수에 대해 오해는 하지 말기 바란다. 이 퀴즈로 받은 점수는 분석함수의 성능과 관련된 것일 뿐, 전반적인 SQL 튜닝능력에 대한 점수가 아니다.

이제 실습을 위한 테이블과 인덱스를 생성하자. 실습환경은 Oracle 10g R2, Oracle 11g R1, Oracle 11g R2로 한정한다.


CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;

 

ALTER TABLE SALES_T ADD CONSTRAINT PK_SALES_T

PRIMARY KEY (PROD_ID, CUST_ID, CHANNEL_ID, TIME_ID) USING INDEX;

 

개발자가 작성한 문제의 SQL 1

 

SELECT *

  FROM ( SELECT /*+  INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                MAX(TIME_ID) OVER( PARTITION BY CUST_ID ,CHANNEL_ID ) AS MAX_TIME

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE TIME_ID = MAX_TIME;

 

업무설명 : 상품번호 30인 데이터에 대하여 고객별, 채널 별로 가장 최근의 판매량을 나타내시오.

             단 고객별, 채널별로 가장 최근의 데이터는 2건 이상일 수 있음.

 

-----------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | A-Rows |   A-Time   | Buffers | Used-Mem |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |  12649 |00:00:00.31 |   28337 |          |

|*  1 |  VIEW                         |            |  12649 |00:00:00.31 |   28337 |          |

|   2 |   WINDOW BUFFER               |            |  29282 |00:00:00.27 |   28337 | 1495K (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T    |  29282 |00:00:00.17 |   28337 |          |

|*  4 |     INDEX RANGE SCAN          | PK_SALES_T |  29282 |00:00:00.03 |     118 |          |

-----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("TIME_ID"="MAX_TIME")

   4 - access("PROD_ID"=30)

 

WHERE 절에 TIME_ID = MAX_TIME 이 존재하는 이유는 고객별, 채널별로 MAX(TIME_ID)에 해당하는 데이터가 두건 이상일 수 있기 때문이다. 이런 경우는 자주 발생한다. 이해를 돕기 위해 비슷한 예를 들어보자. 부서별로 최대급여를 받는 사람들을 출력하고자 할 때, 1번 부서의 최대급여는 1억 원이라고 하자. 그런데 그 부서에서 1억 원(최대급여)을 받는 사람은 강호동, 유재석 둘이라는 이야기 이다.

 

위의 SQL은 답이 정확하다. 하지만 성능은 비효율이 있어서 별로 신통치 못하다. 이제 문제를 보자. SQL에 여전히 남아있는 비효율을 개선하는 것이 문제이다.

 

문제1) 위의 SQL 에서 성능상 문제점을 발견하고 개선하시오. 문제점은 두 가지(분석함수의 관점, 일반적인 SQL튜닝의 관점) 이다. 단 인덱스를 추가로 생성하거나 변경해서는 안 된다.

 

문제 1의 답안

비효율 1 Sample 답안이다. 점수에 반영되지 않는다.

 

문제1의 비효율 1 : SALES_T 테이블에 Random Access가 불필요하게 많이 발생한다. 12649 번만 테이블로 액세스 하면 되지만 실제로는 29282 번 액세스함으로써 비효율이 발생되었다.

 

문제1의 비효율 1의 해결방법 : PK인덱스에 AMOUNT_SOLD가 포함되어 있지 않으므로 Select 절에서 AMOUNT_SOLD를 빼면 인덱스만 액세스 하게 됨. 이때 Rowid를 추가로 Select 해야 한다. 이후에 이미 인덱스에서 Filter Rowid로 테이블을 액세스하면 Random Access는 정확히 12649번 만 시도한다. 아래에 SQL이 있으므로 참조하시오.

 

문제1의 비효율1이 해결된 SQL과 실행계획 제시:

 

SELECT /*+ LEADING(S) USE_NL(S1) */

        S.PROD_ID, S.CUST_ID, S.TIME_ID, S.CHANNEL_ID, S1.AMOUNT_SOLD

  FROM ( SELECT /*+  INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID,

                MAX(TIME_ID) OVER( PARTITION BY CUST_ID ,CHANNEL_ID ) AS MAX_TIME,

                ROWID AS RID

           FROM SALES_T T

          WHERE PROD_ID = 30 ) S,

       SALES_T S1

 WHERE S.TIME_ID = S.MAX_TIME

   AND S1.ROWID = S.RID;

  

------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |      1 |  12649 |00:00:02.72 |   11237 |          |

|   1 |  NESTED LOOPS               |            |      1 |  12649 |00:00:02.72 |   11237 |          |

|*  2 |   VIEW                      |            |      1 |  12649 |00:00:00.18 |     118 |          |

|   3 |    WINDOW BUFFER            |            |      1 |  29282 |00:00:00.13 |     118 | 1432K (0)|

|*  4 |     INDEX RANGE SCAN        | PK_SALES_T |      1 |  29282 |00:00:00.03 |     118 |          |

|   5 |   TABLE ACCESS BY USER ROWID| SALES_T    |  12649 |  12649 |00:00:02.48 |   11119 |          |

------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("S"."TIME_ID"="S"."MAX_TIME")

   4 - access("PROD_ID"=30)

 

이렇게 해서 Sample 답안을 작성해 보았다. 이 방법은 분석함수의 튜닝방법이 아니라 예외적으로 SQL 튜닝방법론에서 언급되었던 방법을 사용하였다. 물론 여기서 페이징처리를 한다면 추가적인 성능향상이 가능하지만 Sample이므로 여기서 멈추기로 한다위의 답안은 Sample 이므로 점수에서 빠진다. 또한 이 부분은 분석함수의 튜닝 방법이 아니다. 하지만 앞으로 풀게 될 문제들은 모두 분석함수와 관련된 문제들이다.

 

 

문제1의 비효율 2

비효율 1을 제거하니 Random Access는 최소화 되었다. 하지만 실행계획을 보면 쓸데없이 PGA 1432K만큼 사용하고 있다. 즉 불필요한 WINDOW BUFFER Operation 때문에 sort가 발생하였다.

 

문제1의 비효율 2 해결방법 

고객별 채널별로 최종일자의 데이터를 구하는 것이므로, Rank를 사용하면 적절한 인덱스가 존재하므로 sort를 하지 않는다. Rank Row_number와는 다르게 1등이 두 명 이상일 수 있으므로 업무조건을 만족한다.

 

문제1의 비효율 2이 해결된 SQL과 실행계획 제시.

 

SELECT /*+ LEADING(A) USE_NL(B) */ A.*, B.AMOUNT_SOLD

  FROM ( SELECT /*+  INDEX_DESC(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID,

                RANK() OVER(PARTITION BY CUST_ID, CHANNEL_ID ORDER BY TIME_ID DESC) RNK,

                ROWID AS RID

           FROM SALES_T T

          WHERE PROD_ID = 30 ) A,

       SALES_T B

 WHERE A.RNK = 1

   AND A.RID = B.ROWID;

 

---------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |            |      1 |  12649 |00:00:00.32 |   11240 |          |

|   1 |  NESTED LOOPS                  |            |      1 |  12649 |00:00:00.32 |   11240 |          |

|*  2 |   VIEW                         |            |      1 |  12649 |00:00:00.20 |     120 |          |

|*  3 |    WINDOW NOSORT               |            |      1 |  29282 |00:00:00.16 |     120 |          |

|*  4 |     INDEX RANGE SCAN DESCENDING| PK_SALES_T |      1 |  29282 |00:00:00.06 |     120 |          |

|   5 |   TABLE ACCESS BY USER ROWID   | SALES_T    |  12649 |  12649 |00:00:00.05 |   11120 |          |

---------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("A"."RNK"=1)

   3 - filter(RANK() OVER ( PARTITION BY "CUST_ID","CHANNEL_ID" ORDER BY INTERNAL_FUNCTION("TIME_ID") DESC )<=1)

   4 - access("PROD_ID"=30)

 

이제 문제2를 풀어보자.

 

개발자가 작성한 문제의 SQL 2

 

SELECT *

  FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER()     OVER ( PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID NULLS FIRST) AS RN,

                SUM(AMOUNT_SOLD) OVER ( ORDER BY CUST_ID,CHANNEL_ID,TIME_ID,PROMO_ID   ) AS SUM_AMT

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10 ;

 

---------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |            |      1 |     10 |00:00:00.43 |   28337 |          |

|*  1 |  VIEW                          |            |      1 |     10 |00:00:00.43 |   28337 |          |

|*  2 |   WINDOW SORT PUSHED RANK      |            |      1 |  29282 |00:00:00.40 |   28337 | 1999K (0)|

|   3 |    WINDOW SORT                 |            |      1 |  29282 |00:00:00.30 |   28337 | 1684K (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.18 |   28337 |          |

|*  5 |      INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.04 |     118 |          |

---------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PROD_ID" ORDER BY "CUST_ID","CHANNEL_ID",INTERNAL_FUNCTION("TIME_ID")

              NULLS FIRST)<=10)

   5 - access("PROD_ID"=30)

 

문제2) 위의 SQL 에서 분석함수의 성능상 문제점을 발견하고 개선하시오. 분석함수와 관련된 성능상 문제점은 4가지 이다. 비효율들을 발견하고 이를 모두 개선해야 한다. 단 인덱스를 추가로 생성하거나 변경하면 안 된다.

튜닝의 결과부터 이야기 하자면, Sort량은 3683 K 에서 0 K 로 줄어들었고 Block I/O량은 28337 에서 15 로 줄어들었다. 성능이 몇 배나 개선되었는지 계산하기도 힘들 정도이다. 결과건수가 많아 질수록 이 차이는 점점 더 벌어질 것이다. 성능이 어떻게 개선된 것일까? 지금부터 비효율을 하나씩 벗겨내고 개선방법을 도출해보자.
 
 

문제 2의 답안

문제2의 비효율 1

NULLS FIRST를 사용하면 인덱스를 Ascending으로 사용하면 null 값은 가장 마지막에 출력된다. null 값이 마치 가장 큰 값인 것처럼 저장 되는 것이다. 하지만 NULLS FIRST를 명시하는 순간 sort가 뒤바뀌어 가장 먼저 출력해야 한다. 즉 인덱스를 사용했지만 NULLS FIRST 때문에 추가적인 sort가 필요하다는 이야기 이다. 이러한 문제는 인덱스를 Descending 하게 사용할 때, NULLS LAST를 명시해도 똑같이 발생한다.

 

문제2의 비효율 1 해결방법

문제의 SQL을 보면 PARTITION BY ORDER BY의 컬럼들은 PK 컬럼들이므로 null 일수 없다. 따라서 NULLS FIRST삭제해도 무방하다. 다시 작성한 SQL은 다음과 같다.

 

문제2의 비효율 1이 해결된 SQL과 실행계획 제시

 

SELECT *

  FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER()     OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN,

                SUM(AMOUNT_SOLD) OVER ( ORDER BY CUST_ID,CHANNEL_ID,TIME_ID,PROMO_ID  ) AS SUM_AMT

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10 ;

 

---------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |            |      1 |     10 |00:00:00.45 |   28337 |          |

|*  1 |  VIEW                          |            |      1 |     10 |00:00:00.45 |   28337 |          |

|*  2 |   WINDOW NOSORT                |            |      1 |  29282 |00:00:00.42 |   28337 |          |

|   3 |    WINDOW SORT                 |            |      1 |  29282 |00:00:00.32 |   28337 | 1684K (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.17 |   28337 |          |

|*  5 |      INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.03 |     118 |          |

---------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PROD_ID" ORDER BY "CUST_ID","CHANNEL_ID","TIME_ID")<=10)

   5 - access("PROD_ID"=30)

 

ROW_NUMBER에 해당하는 Operation WINDOW SORT PUSHED RANK에서 WINDOW NOSORT로 바뀌었다. 그 영향으로 ROW_NUMBER Sort가 전혀 발생하지 않는다.

 

 

문제2의 비효율 2

비효율 1의 튜닝의 결과를 놓고 보면, ROW_NUMBER SUM 분석함수의 Operation이 실행계획에 각각 존재한다. 즉 분석함수가 따로 따로 두 번 실행되는 것이다. 분석함수의 종류는 두 개(row_number sum)라 하더라도, 분석함수와 관련된 Operation이 한번만 나와야 한다는 말이다. 또한 분석함수 SUM order by절에 PROMO_ID가 존재하여 불필요한 sort가 발생하였다.

 

문제2의 비효율 2 해결방법

이 비효율을 없애면 마치 분석함수가 하나뿐인 것처럼 만들 수 있다. Partition 절과 Order 절을 일치시키면 된다. 아래의 SQL Partition 절과 Order 절을 일치시키기 위해 SUM 분석함수에 PARTITION BY 절을 추가해야 한다. 물론 ROW_NUMBER함수에서 Partition By절을 삭제할 수 있지만, 그와 관련된 이야기는 이후 단계에서 논의 되므로, 여기서는 그냥 Partition By절을 추가하기로 한다. 그리고 WINDOW SORT를 유발하는 PROMO_ID도 삭제해야 한다.

 

문제2의 비효율 2가 해결된 SQL과 실행계획 제시

 

SELECT *

  FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER()     OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN,

                SUM(AMOUNT_SOLD) OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS SUM_AMT

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10 ;  

 

--------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |      1 |     10 |00:00:00.26 |   28337 |          |

|*  1 |  VIEW                         |            |      1 |     10 |00:00:00.26 |   28337 |          |

|   2 |   WINDOW BUFFER               |            |      1 |  29282 |00:00:00.23 |   28337 | 1495K (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.11 |   28337 |          |

|*  4 |     INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.03 |     118 |          |

--------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   4 - access("PROD_ID"=30)

 

만약 해결방법 두 가지(Partition By 추가, PROMO_ID 삭제)중 한가지만 적용했다면 10점으로 처리하면 되므로 채점은 가능하다. 아래의 설명부분과 SQL은 답안이 아니며, 원리를 설명하는 부분이다.

 

Operation 통합의 원리
분석함수 SUM
Partition By절을 추가하고, order by절에 PROMO_ID를 삭제하니, 예상대로 분석함수 두 개의 Plan이 하나로 합쳐졌다. 여기서 알 수 있는 점은 두 가지 이다. 첫 번째, Partition By Order By를 통일 시키면 Operation이 통합되어 분석함수 하나만 실행시키는 결과를 얻는다. 두 번째, WINDOW NOSORT(ROW_NUMBER) + WINDOW BUFFER(SUM) 가 합쳐져서 WINDOW BUFFER가 되었다는 점이다. 좋지 않은 Operation으로 통합된다는 것을 알 수 있다. 기준은 다음과 같다.

 

 WINDOW NOSORT + WINDOW SORT    => WINDOW SORT

 WINDOW NOSORT + WINDOW BUFFER  => WINDOW BUFFER

 WINDOW BUFFER + WINDOW SORT    => WINDOW SORT


PROMO_ID
를 삭제해도 되는 이유는 ORDER BY에서 모든 PK 컬럼을 사용하였기 때문이다. PK 컬럼들로 ORDER BY절에서 모두 SORT 되었으므로, 나머지 컬럼으로 아무리 SORT 해보아야 결과는 동일하다는 것이다. Order By절에서 PROMO_ID를 삭제하자 WINDOW SORT WINDOW BUFFER 로 바뀌었다. 이에 따라 PGA사용량이 1747K에서 1495K 약간 줄어들었다
 


만약 Partition By만 추가하고 PROMO_ID를 삭제하지 않으면 아래와 같이 sort의 비효율이 발생한다.

 

SELECT *

  FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER()     OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN,

                SUM(AMOUNT_SOLD) OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID,PROMO_ID  ) AS SUM_AMT

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10 ; 

 

--------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |      1 |     10 |00:00:00.33 |   28337 |          |

|*  1 |  VIEW                         |            |      1 |     10 |00:00:00.33 |   28337 |          |

|   2 |   WINDOW SORT                 |            |      1 |  29282 |00:00:00.30 |   28337 | 1747K (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.17 |   28337 |          |

|*  4 |     INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.03 |     118 |          |

--------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("RN"<=10)

   4 - access("PROD_ID"=30)

  

Order By절이 다르지만, 통합이 가능한 이유
PROMO_ID
를 추가하자, WINDOW SORT가 발생하여 PGA 사용량이 늘어났다. 분석함수 SUM ORDER BY절을 보면 PROMO_ID가 있는데 ROW_NUMBER 분석함수에는 PROMO_ID가 없다. ORDER BY는 완벽히 같지 않아도 Operation이 통합 될 수 있다는 점을 알 수 있다. ORDER BY의 마지막 항목이 달라도 Operation이 통합 될 수 있는 원리는 간단하다. Sort 작업을 A + B + C로 이미 했다면 Sort A + B 로 다시 해야 할 필요가 없기 때문이다. A + B + C Sort 하는 분석함수 기준으로 A + B Sort 하는 분석함수가 통합된다는 의미이다.  

 

오해하지 말 것

서로 다른 종류의 분석함수를 실행했을 때, Partition By의 컬럼과 Order By의 컬럼을 각각 똑같이 맞추어야 하는 것은 아니다. Partition ByOrder By의 컬럼을 각각 맞추는 것이 아니라, OVER절 전체의 컬럼을 일치시키면 된다. 아래의 SQL이 좋은 예제이다.

 

SELECT *

  FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER()     OVER (PARTITION BY PROD_ID  ORDER BY CUST_ID, CHANNEL_ID,TIME_ID ) AS RN,

                SUM(AMOUNT_SOLD) OVER (PARTITION BY PROD_ID, CUST_ID  ORDER BY CHANNEL_ID,TIME_ID ) AS SUM_AMT

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10 ;

 

--------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |      1 |     10 |00:00:00.32 |   28337 |          |

|*  1 |  VIEW                         |            |      1 |     10 |00:00:00.32 |   28337 |          |

|   2 |   WINDOW BUFFER               |            |      1 |  29282 |00:00:00.28 |   28337 | 1495K (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.14 |   28337 |          |

|*  4 |     INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.04 |     118 |          |

--------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   4 - access("PROD_ID"=30)

 

위의 SQL을 보면 분석함수 두 개의 Partition By의 컬럼과 Order By 컬럼이 모두 다르다. 하지만 실행계획상의 operation은 하나로 통합되었다. OVER절을 바라볼 때 Partition ByOrder By를 삭제하고 컬럼들만 남긴다면, operation이 통합이 될지, 아닐지 판단할 수 있다. 예컨대, over절의 컬럼은 두 개의 분석함수 모두 PROD_ID + CUST_ID + CHANNEL_ID + TIME_ID 로 똑같으므로 operation이 통합된다.

 

중요한 것은 컬럼의 순서이다. Partition By Order By를 삭제하고 순수하게 over절의 컬럼들만 남겼을 때, 컬럼의 순서가 같아야 operation이 통합된다. 컬럼의 순서가 다르면 통합되지 않는다. 위의 분석함수 둘은 over절의 컬럼순서가 PROD_ID + CUST_ID + CHANNEL_ID + TIME_ID로 같음을 알 수 있다. OVER절 전체의 컬럼순서가 같다면, 컬럼의 개수와는 상관없이 통합이 가능하다. 예를 들어, 분석함수를 두 개 사용하고 컬럼순서가 하나는 A+B+C 이고 다른 하나는 A+B 라면, 컬럼순서가 동일하므로 통합이 가능하다하지만 A+B+C A+C의 조합은 컬럼순서가 다르므로 통합이 불가능하다.

 

문제2의 비효율 3

비효율 2의 튜닝결과를 보면 Ranking family를 사용하고, 인라인뷰 외부에서 분석함수를 Filter로 처리했음에도 불구하고 STOPKEY가 작동하지 않는다. 이에 따라 부분범위처리가 되지 못하고 불필요한 데이터를 모두 Scan해야 한다.

 

문제2의 비효율 3의 해결방법

STOPKEY 작동되도록 하려면 ROW_NUMBER OVER절에서 불필요한 PARTITION BY PROD_ID를 제거하면 된다. PROD_ID는 이미 WHERE 절의 Equal 조건에 의하여 상수화 되었기 때문에 PARTITION BY는 필요 없다.

 

문제2의 비효율 3이 해결된 SQL과 실행계획 제시

 

SELECT *

  FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER()     OVER (                     ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN,

                SUM(AMOUNT_SOLD) OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS SUM_AMT

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10 ; 

 

---------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |            |      1 |     10 |00:00:00.16 |   28337 |          |

|*  1 |  VIEW                          |            |      1 |     10 |00:00:00.16 |   28337 |          |

|*  2 |   WINDOW NOSORT STOPKEY        |            |      1 |     10 |00:00:00.16 |   28337 |          |

|   3 |    WINDOW BUFFER               |            |      1 |     11 |00:00:00.16 |   28337 | 1495K (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.11 |   28337 |          |

|*  5 |      INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.03 |     118 |          |

---------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID","CHANNEL_ID","TIME_ID")<=10)

   5 - access("PROD_ID"=30)

 

아래의 설명부분과 SQL은 답안이 아니며 원리를 설명하는 부분이다.

 

Ranking family에서 불필요한 Partition By를 삭제하자 WINDOW NOSORT STOPKEY가 발생되었다. 하지만 실행계획을 자세히 보면 비효율이 존재한다. WINDOW BUFFER 때문에 29282건을 모두 읽은 후에 STOPKEY가 발생되었다. 처음부터 11건만 읽는 방법은 없는 것일까? 그렇다고 위의 SQL에서 분석함수 SUM PARTITION BY도 삭제한다면 비효율 2의 실행계획으로 돌아가 버린다. 비효율 2에서 이미 설명했던 원칙(좋지 않은 Operation으로 통합되는 원칙) 때문에 WINDOW NOSORT STOPKEY WINDOW BUFFER에 묻혀버리는 것이다.

 

만약 WINDOW BUFFER(분석함수 SUM)가 없다면 WINDOW NOSORT STOPKEY가 완벽히 동작한다. 아래의 SQL로 증명할 수 있다.

 

SELECT *

  FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER() OVER ( ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10 ;  

 

--------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |      1 |     10 |00:00:00.01 |      15 |          |

|*  1 |  VIEW                         |            |      1 |     10 |00:00:00.01 |      15 |          |

|*  2 |   WINDOW NOSORT STOPKEY       |            |      1 |     10 |00:00:00.01 |      15 |          |

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |     11 |00:00:00.01 |      15 |          |

|*  4 |     INDEX RANGE SCAN          | PK_SALES_T |      1 |     11 |00:00:00.01 |       4 |          |

--------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID","CHANNEL_ID","TIME_ID")<=10)

   4 - access("PROD_ID"=30)

 

분석함수 SUM만 제거한다면 STOPKEY가 비효율 없이 작동하여 정확히 11건만 읽었다. 위의 실행통계와 비효율 3이 튜닝된 SQL의 실행통계를 비교해보라. Buffers Used-Mem 항목을 본다면 비교할 수 없을 정도이다.  또한 WINDOW STOPKEY의 활용범위는 ROWNUM 보다 넓다. 물론 ROW_NUMBER는 ROWNUM 처럼 사용 할 수도 있다. 하지만, RANK나 DENSE_RANK의 경우로 본다면 추가적인 활용법이 있다. 예를 들어, 학교에서 수학성적으로 전교석차 2등 까지 출력한다고 치자. 그러면 1등과 2등이 각각 두 명 이상일 수 있다. 이런 경우는  ROWNUM으로는 구현할 수 없다. RANK나 DENSE_RANK가 WINDOW NOSORT STOPKEY로 성능을 충족시켜면서, 업무요구사항을 만족시키는 유일한 방법이다.

Sort량 2K vs 1999K의 비밀

비효율 3에서는 Partition By를 삭제하여 Sort가 전혀 발생하지 않는 경우를 보았다. 만약 Sort를 동반하는 경우라면, Partition By를 삭제함에 따라 Sort의 일량에 어떤 영향을 끼칠까? 아래의 SQL을 보자.
 

SELECT *

  FROM (SELECT /*+ INDEX(T PK_SALES_T) */

               PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

               ROW_NUMBER ()     OVER (ORDER BY CUST_ID, CHANNEL_ID, TIME_ID NULLS FIRST) AS RN,

               SUM (AMOUNT_SOLD) OVER (ORDER BY CUST_ID, CHANNEL_ID, TIME_ID, PROMO_ID) AS SUM_AMT

          FROM SALES_T T

         WHERE PROD_ID = 30)

 WHERE RN <= 10 ;

 

---------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |            |      1 |     10 |00:00:00.34 |   28337 |          |

|*  1 |  VIEW                          |            |      1 |     10 |00:00:00.34 |   28337 |          |

|*  2 |   WINDOW SORT PUSHED RANK      |            |      1 |     11 |00:00:00.34 |   28337 | 2048  (0)|

|   3 |    WINDOW SORT                 |            |      1 |  29282 |00:00:00.30 |   28337 | 1684K (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.18 |   28337 |          |

|*  5 |      INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.04 |     118 |          |

---------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID","CHANNEL_ID",INTERNAL_FUNCTION("TIME_ID") NULLS FIRST)<=10)

   5 - access("PROD_ID"=30)

위의 SQL은 개발자가 작성한 원본 SQL에서 ROW_NUMBER 함수의 Partition By절을 삭제하고 실행한 것이다. 이 경우는 over절의 NULLS FIRST 때문에 Sort가 발생되는 경우이다. 이 때에도 Sort가 최소화 되어 PGA 사용량 2K에 불과하다. 개발자가 작성한 원본 실행통계의 Sort량이 1999K임을 감안하면 Partition By의 존재유무는 성능에 지대한 영향을 끼친다고 할 수 있다. Sort량이 이렇게 큰 차이가 나는 이유는 파티션을 삭제한 효과 + RN <= 10 조건 때문이다. 그 filter 조건에 의해서 10개의 배열만 관리하면 되므로 PGA 사용량은 최소화 된다. WINDOW SORT PUSHED RANK의 이러한 성격은 인덱스를 사용하지 않는 경우에도 동일하게 나타난다위의 SQL에서 힌트를 FULL(T)로 바꾸고 실행하여도 PGA 사용량은 동일하다. 

위의 SQL 처럼 ROW_NUMBER 함수의 NULLS FIRST 보다 Partition By절을 먼저 삭제한 사람은 이 원리를 답안으로 사용해도 무방하다하지만 비효율 3에서 설명되었던, Partition By 삭제에 의한 NOSORT STOPKEY 유도 원리는 WINDOW SORT PUSHED RANK와 성격이 다르므로 반드시 알아두어야 한다.    

 

문제2의 비효율 4

진퇴양란, 포기할 것인가?

비효율 3의 튜닝결과를 보면 STOPKEY를 발생시키려고 ROW_NUMBER Partition By를 제거하니 Operation이 두 개로 분리되어 다시 비효율이 발생하였다. 그렇다고 분석함수 SUM을 삭제하자니 업무요구사항이 만족되지 않는다. 어떻게든 STOPKEY를 발생시켜서 29282건을 모두 읽는 비효율을 제거해야 한다.

 

분석함수 SUM을 삭제하는 것은 답이 아니다

보통 이런 경우(진퇴양란)에서는 둘 중에 한가지를 선택한다. 튜닝을 포기하거나 장애물을 제거한다. 열정이 있는 개발자들은 장애물을 제거할 것이다. 분석함수 SUM을 삭제하고, 조회화면에서 프로그램 스크립트를 작성하여 분석함수 SUM의 로직을 대신한다. 물론 이렇게 하는 것은 목적을 달성하는 것이므로, 포기하는 것보다는 만족스럽다. 하지만 나는 포기하지도 말고, 프로그래밍 언어의 도움도 받지 말라고 주장한다. SQL이 아닌 다른 언어의 도움으로 비효율을 해결한 것은 이 문제의 정답이 아니며, SQL만으로 해결할 수 있다.

 

문제2의 비효율 4 해결방법

일부 개발자는 분석함수 SUM을 제거하고 프로그래밍 언어의 도움을 받아 해결하였다. 하지만 우리는 분석함수 SUM을 물리적으로 삭제하는 것이 아니라 실행계획상에서만 삭제하고자 한다. 방법은 SUM/MIN/MAX/AVG 등의 분석함수에 WINDOW의 범위를 명시적으로 작성하면 된다. 물론 범위를 명시적으로 작성해도 답은 동일하다.

 

문제2의 비효율 4가 해결된 SQL과 실행계획 제시

 

SELECT *

  FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER()     OVER (ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN,

                SUM(AMOUNT_SOLD) OVER (ORDER BY CUST_ID,CHANNEL_ID,TIME_ID

                                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS SUM_AMT

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10 ; 

 

--------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |      1 |     10 |00:00:00.01 |      15 |          |

|*  1 |  VIEW                         |            |      1 |     10 |00:00:00.01 |      15 |          |

|*  2 |   WINDOW NOSORT STOPKEY       |            |      1 |     10 |00:00:00.01 |      15 |          |

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |     11 |00:00:00.01 |      15 |          |

|*  4 |     INDEX RANGE SCAN          | PK_SALES_T |      1 |     11 |00:00:00.01 |       4 |          |

--------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID","CHANNEL_ID","TIME_ID")<=10)

   4 - access("PROD_ID"=30)

 

분석함수 SUM을 제거한 것은 아니지만, WINDOW의 범위를 주어 동일한 효과를 얻었다. 정확히 11건만 읽었으므로, 깔끔하게 모든 문제가 정리되었다. Window의 범위를 명시적으로 작성하는 것이 얼마나 중요한지 알 수 있다.

 

여기까지가 답안이다. 이제 어떻게 된 것인지 알아보기 위해 분석함수 ROW_NUMBER를 삭제해 보자.

 

SELECT *

  FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                SUM(AMOUNT_SOLD) OVER (ORDER BY CUST_ID,CHANNEL_ID,TIME_ID

                                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS SUM_AMT

           FROM SALES_T T

          WHERE PROD_ID = 30 ) ;

         

 

--------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |      1 |  29282 |00:00:00.36 |   28340 |          |

|   1 |  VIEW                         |            |      1 |  29282 |00:00:00.36 |   28340 |          |

|   2 |   WINDOW NOSORT               |            |      1 |  29282 |00:00:00.30 |   28340 |          |

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.20 |   28340 |          |

|*  4 |     INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.03 |     121 |          |

--------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("PROD_ID"=30)

 

그룹분석함수와 Ranking Family를 같이 사용해도 STOPKEY가 발생한다

그룹분석함수임에도 SORT가 전혀 발생하지 않았다. SUM/MIN/MAX/AVG 등의 분석함수에 WINDOW의 범위를 지정해주면 WINDOW BUFFERWINDOW NOSORT로 바뀐다는 이야기 이다. 이것은 의미 있는 발견이다. 왜냐하면 이때 까지는 SUM/MIN/MAX/AVG 등의 분석함수 때문에 적절한 인덱스를 사용하였지만 WINDOW BUFFER가 발생하여 STOPKEY를 발생시키지 못했다. 그런데 이제 WINDOW NOSORT가 가능해짐으로 Ranking Family STOPKEY가 정상적으로 작동하기 때문이다.

이제 적절한 인덱스가 있고, SUM등의 그룹분석함수를 사용한다면, 다음과 같은 결론을 내릴 수 있다

 

SUM등의 그룹분석함수를 사용한다면, WINDOW의 범위를 지정해주어야 한다. 그렇게 한다면 Sort가 전혀 발생하지 않을 뿐만 아니라, Ranking Family 분석함수를 rownum 처럼 사용할 때, STOPKEY를 발생시켜 성능이 개선된다.”


분석함수를 만날 때의 자세
2
번 문제의 원본 SQL 매우 짧은 코드이지만, 비효율은 4개나 된다튜닝을 완료했다고 자신하는 순간이라도마지막 남은 분석함수의 비효율 하나 때문에 SQL의 성능향상은 도루묵이 될 수 있다. 이런 사실은 SQL을 튜닝 할 때, 분석함수를 만나면 안테나를 세워야 하는 이유가 된다

미래지향

다섯 가지 비효율에 대해 80점 혹은 그 이상을 받은 사람은 분석함수에 관한 튜닝은 상당한 수준에 있다고 생각한다. 반대로, 80점이 안 된다고 실망하지 말자. 왜냐하면 퀴즈에서 실패했더라도, 실무튜닝에서 100점을 받으면 그만이다. 물론 이렇게 되려면, SQL과 실행계획을 보고 비효율을 읽어내는 능력이 필요하다

마지막으로 다섯 가지의 원리로 이 글 전부를 정리 해보자. 다섯 가지의 원리는 대부분 아래의 구조로 되어있다.

1) 소제목
2) ~ 를 하라.
3) 그렇게 하면 ~ 효과가 있다.
 

분석함수의 성능향상 원리


1. min/max 분석함수를 Ranking family로 대체하는 원리

적절한 인덱스를 사용하면서, 최종일자에 해당하는 데이터를 구할 때, MAX(최종일자) 분석함수를 사용하지 말고 Ranking family를 사용하라. 그렇게 하면 WINDOW BUFFER WINDOW NOSORT로 바꾸어 sort를 방지한다.

 

2. sort의 기준을 유지하는 원리
적절한 인덱스가 존재하는 경우, 분석함수의 ORDER BY절에 NULL FIRST LAST를 삭제할 수 있는지 검토하라그렇게 하면 인덱스 사용에 의한 Sort 기준이 유지되므로, 추가적인 sort를 방지한다주의사항은 NULL FIRST LAST를 사용한다고 해서 항상 Sort의 기준이 바뀌는 것은 아니며, 아래의 경우만 해당한다.

            ex1) Index ASC로 사용 + 분석함수의 ORDER BY절에 NULL FIRST 는 추가적인 sort발생

               ex2) Index DESC로 사용 + 분석함수의 ORDER BY절에 NULL LAST 는 추가적인 sort발생

 

3. 여러 개의 분석함수를 하나의 Operation으로 통합하는 원리

적절한 인덱스가 존재하고, 분석함수를 여러 개 사용할 때, 가능하면 OVER절의 Partition By Order By절을 일치시켜라. 그렇게 하면 여러 개의 OPERATION이 통합된다. 다시 말해, 분석함수를 하나만 실행하는 효과를 얻어서 실행시간을 단축시킨다이때 ORDER BY는 완전히 같지 않아도 Operation은 통합될 수 있다단 아래와 같이 좋지 않은 Operation 을 기준으로 통합된다.

         ex1) WINDOW NOSORT + WINDOW SORT   => WINDOW SORT

         ex2) WINDOW NOSORT + WINDOW BUFFER => WINDOW BUFFER

         ex3) WINDOW BUFFER + WINDOW SORT   => WINDOW SORT
 
ORDER BY가 다를 때, Operation이 통합될 수 있는 조건이 있다. 그 세부내용은 본문을 참조하라.

 

4. Ranking Family를 이용한 TOP SQL에서 Sort 최소화의 원리

Ranking Family 분석함수를 인라인뷰 외부에서 Rownum 처럼 Filter로 사용했을 때, 불필요한 Partition By절을 삭제하라. 그렇게 하면 두 가지 경우에 성능이 향상된다. 주의사항은 Partition By절 전체를 제거해야 한다는 것이다. 만약 Partition By절에 컬럼이 하나라도 있으면 Sort가 대량으로 발생된다. 각각의 파티션 값마다 sort를 해야 하므로 어쩔 수 없는 일이다.

첫 번째, 적절한 인덱스가 없어서 FULL SCAN을 하거나 혹은 OVER절의 NULL FIRST LAST등의 원인으로 sort가 발생될 때 이다. 이럴 경우 Partition By을 제거하면 필요한 개수 + 1 만큼만 sort가 발생하므로 성능이 향상된다. 하지만 Rownum과는 달리 STOPKEY가 발생되지 않으므로 비효율은 존재한다. 이 때 발생되는 OperationWINDOW SORT PUSHED RANK 이다.

 

두 번째, 적절한 인덱스가 있어서 sort가 발생되지 않는 경우이다. 이 경우는 첫 번째의 경우와는 달리 sort가 전혀 발생하지 않으며, Rownum 처럼 STOPKEY를 발생시켜 부분범위 처리의 효과를 얻는다. 다시 말해, 분석함수를 Rownum 처럼 사용할 수 있다. 이 때 발생되는 OperationWINDOW NOSORT STOPKEY 이다. 이 기능은 10g R2에서 추가 되었다

WINDOW NOSORT STOPKEY는 주의사항이 있다그룹분석함수의 OVER절과 Ranking Family OVER절의 컬럼순서가 다르면 Operation이 하나로 통합되지 못한다이 경우는 성능이 저하된다왜냐하면 WINDOW SORT 혹은 WINDOW BUFFER 가 먼저 실행되고 그 후에 WINDOW NOSORT STOPKEY가 발생하기 때문에 Block I/O sort량을 감소시키지 못한다. 이런 비효율 때문에 분석함수 여러 개를 하나의 Operation으로 통합하는 원리(원리 3) 역할이 중요한 것이다. 이와 관련된 예제는 비효율3이 해결된 SQL과 실행계획을 참조하기 바란다. 

 

5. 그룹분석함수의 NO-SORT 원리

이 원리는 장점이 두 가지 이다. Sort를 방지하고, STOPKEY를 작동시킨다.

 

적절한 인덱스가 존재하고, sum/min/max/avg등의 Group 분석함수를 사용할 경우 명시적으로 WINDOW의 범위를 지정하라. 그렇게 하면 WINDOW BUFFER Operation WINDOW NOSORT로 바꾸어 불필요한 sort를 방지한다. 물론 여기서 이야기 하는 그룹분석함수는 현재 row 까지의 누적집계(Cumulative total) 이다.

 

더욱 좋은 것은, Ranking family Rownum 처럼 사용하고 있다면, STOPKEY를 사용할 수 있게 한다. Group 분석함수를 사용하면, WINDOW BUFFER가 발생하고, sort가 발생되는데, 이 때문에 STOPKEY를 작동 시킬 수 없다. 그런데 WINDOW의 범위를 지정하여 WINDOW NOSORT로 바꾸면 sort가 발생하지 않기 때문에 STOPKEY가 작동한다. WINDOW의 범위를 지정하는 방법은 아래와 같다. 범위를 생략해도 결과는 같지만, 성능이 저하된다.

 ex) SUM(AMOUNT_SOLD) OVER(ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

참고: Ranking family를 사용하여 WINDOW STOPKEY가 발생하는 경우는 SORT가 발생하지 않는 경우(NOSORT)뿐이다그룹분석함수에 의해서 추가적인 SORT를 해야 한다면, 전체범위로 처리됨으로 STOPKEY가 발생하지 않는다.


위의 다섯 가지 원리가 적용되지 않은 것이 각각의 비효율이다. 즉 위의 다섯 가지 원리를 비효율의 해결방법으로 작성한 사람은 100점이다.


자동튜닝기능이 아쉬워

이 글의 결론을 보고 많은 이들이 두 가지를 아쉬워할 것이다. 예컨대, 분석함수 SUM WINDOW의 범위를 생략했더라도 자동으로 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW를 추가해 준다면 얼마나 좋을까? 불필요한 Partition By절을 수동으로 삭제해야 하는 것 또한 마찬가지이다. 아직까지 옵티마이저는 사람을 따라올 수 없다. 사람의 손길을 여전히 필요로 한다따라서 전문 개발자라면 Partition By Order By 혹은 Window의 범위지정 등의 코딩을 할 때 내부적으로 어떤 일이 발생하는지 알아야 한다. 그냥 코딩을 하는 사람과 그것이 성능상 어떤 의미를 지니는지 알고 개발하는 사람과는 차이가 크다

 

이상으로 분석함수의 내부에 대해 3회에 걸쳐 알아보았다. 1/2부의 내용과 위의 결론 다섯 가지를 완벽히 이해한다면, 분석함수를 사용한 SQL과 실행계획, 그리고 인덱스 구조만 보고도 순간적으로 비효율을 잡아낼 수 있다분석함수의 비효율을 만나면 번개가 치듯이 생각이 번쩍 들것이다실행계획을 보고 비효율의 냄새를 맡을 줄 아는 능력은 중요하다. 

마지막으로, 분석함수의 실행계획에 관한 글을 의뢰한 개발자에게 감사를 표한다. 아무래도 내가 그 개발자에게 맥주를 사는 것이 더 좋을 것 같다. 왜냐하면 그 사람 덕분에 좋은 것(Window의 범위 지정의 효과)을 발견할 수 있었기 때문이다. 두서 없는 긴 글을 읽어주신 독자 여러분들께 감사 드린다 

------------------------------------------------------------------------------------------------------------------------------------------------

이번 글은 여기까지 이다. 아래의 글은 이전에 올렸던 글의 정답보다 더 좋은 것이 발견되었음을 알리는 것이다. 

 

분석함수의 실행계획을 정리하면서 Group(SUM/MIN/MAX/AVG) 분석함수에 WINDOW의 범위를 명시적으로 작성하는 것이 얼마나 중요한지 필자도 깨닫게 되었다. 예컨대, Pagination 분석함수의 위험한 조합 글에서 도출된 정답보다 더 좋은 것은 WINDOW의 범위를 명시하는 것이다. 따라서 분석함수를 밖으로 빼는 것보다, 아래와 같이 window의 범위를 지정해 주는 것이 더 유리하다.

 

SELECT *

  FROM (SELECT s.*, ROWNUM rnum

          FROM (SELECT   /*+ INDEX(S PK_SALES_T) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold,

                         sum (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS sum_amt

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) s

         WHERE ROWNUM <= :v_max_row  --> 20 대입

       )

 WHERE rnum >= :v_min_row ;          --> 1 대입

 

----------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |            |      1 |     20 |00:00:00.01 |      24 |          |

|*  1 |  VIEW                           |            |      1 |     20 |00:00:00.01 |      24 |          |

|*  2 |   COUNT STOPKEY                 |            |      1 |     20 |00:00:00.01 |      24 |          |

|   3 |    VIEW                         |            |      1 |     20 |00:00:00.01 |      24 |          |

|   4 |     WINDOW NOSORT               |            |      1 |     20 |00:00:00.01 |      24 |          |

|   5 |      TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |     20 |00:00:00.01 |      24 |          |

|*  6 |       INDEX RANGE SCAN          | PK_SALES_T |      1 |     20 |00:00:00.01 |       4 |          |

----------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RNUM">=:V_MIN_ROW)

   2 - filter(ROWNUM<=:V_MAX_ROW)

   6 - access("S"."PROD_ID"=:V_PROD_ID)

 

20건에 대해서 조차 SORT가 전혀 발생하지 않는다. 따라서 Pagination 분석함수의 위험한 조합 이라는 글은 수정되어야만 한다. 내가 작성한 글을 내가 뒤집었다. 내 것이라도 비판적으로 바라보아야 한다. 최종이론이란 없는 것이므로
 

------------------------------------------------------------------------------------------------------------------------------------------------

PS: 몇분이 구두로 혹은 메일로 괴상한 질문을 해서 답글을 남긴다.

Question: 어떻게 이런 책과 글을 쓰며, 주제는 어떻게 잡으며, 연구는 어떻게 하는가?  

--> 이상한 질문이다. 내가 용빼는 재주라도 있나?

Answer:

필자의 머리가 좋아서 글을 쓸 수 있는 게 아니다오히려 그 반대이다. 똑똑한 다른 사람이라면, 내가 연구랍시고 소비한 시간의 절반만 투자해도 이 글을 썼을 것이다나는 다른 사람과 달리 영민하지 못하여 하찮은 문제에도 많은 시간을 소모하며, 실패하면 다시 조금씩 앞으로 전진 할 수 밖에 없다그렇다고 다른 재주가 있느냐 하면 그렇지 않다. 방향감각이 둔하여 연구가 막다른 길로 빠진 적이 여러 번일 뿐만 아니라 남들이 가지고 있는 창의성, 순발력, 영어실력문장력, 준수한 외모를 나는 가지지 못했고, 심지어 노래방에서는 박자를 못 맞추는 절대음감까지 그 어느 것도 잘하는 게 없다아무리 생각해도 세상은 불공평 한 듯 하다

내가 할 수 있는 일은 나의 유일한 단점이자 장점을 살리는 수뿐이다실수를 하더라도, 거북이 근성으로 시간이 나는 대로 조금씩 연구를 하여 이런 글을 쓰는 수 밖에 없다. 글의 주제와 관련된 것도 특별한 것은 없다. 소주한잔 마시면서 어떤 글을 쓸지 동료들과 소통하거나, 독자들이 필요한 글을 나에게 요청한다내가 직접 글감을 고른 적은 5번중의 한번 정도이다글의 주제가 정해지면 무작정 글을 쓴다. 글을 쓰다가 모르는 것이 있으면(모르는 게 많다) 그에 맞춰 test(연구)를 한다. 궁금증이 해결되면 다시 글을 쓴다. 이것이 끝이다.

 

이 글도 위에서 설명한 절차와 똑같이 진행되었다. 단언하건대, 분석함수의 성능에 대해 이 문서만큼 깊이 다룬 책이나 매뉴얼은 지금까지 없다이런 일이 가능한 이유는 내가 아둔하고, 모르는 게 많은 거북이라서 그런 것이 아닐까?  

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. 호아 2011.02.18 02:14 신고  댓글주소  수정/삭제  댓글쓰기

    글 잘봤습니다.

    내공 향상 됐습니당..

    그럼 오늘도 좋은하루..

  2. camela 2011.02.18 13:26 신고  댓글주소  수정/삭제  댓글쓰기

    문제1번만 답안을 봤는데 2개 다 맞았네요^^ 이럴때 예전에 비해 실력이 향상되었구나 하는 느낌이 오는데,
    문제2번은 한참 봤는데 아직 모르겠네요. 좀 더 생각해보고 답안을 봐야겠어요.
    안 그래도 분석함수에 대해선 어떻게 성능향상을 해야되는지 궁금했었는데 좋은 글 올려주셔서 감사합니다.^^

  3. salvationism 2011.02.18 22:41 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 감사합니다. ^^
    저도 거북이 근성으로...

  4. finecomp 2011.02.21 01:47 신고  댓글주소  수정/삭제  댓글쓰기

    분석함수 튜닝에 관해선 인덱스와 적절히 맞추는 수준 밖에 모르던 1인...;
    좋은 글 감사합니다...많이 배우고 갑니다...꾸벅...;

  5. feelie 2011.02.22 13:00 신고  댓글주소  수정/삭제  댓글쓰기

    무척 어렵네요...
    하지만 많은 부분을 생각하게 하네요.
    이전글과 같이 보니 혼돈된는 부분이 있는데요.
    Window Buffer 는 인덱스의 사용유무에 상관없이 결과집합에 대하여
    sort을 수행한다.
    맞는건가요?
    아무튼 매번 느끼는거지만 스스로 많은 노력이 필요할것 같습니다..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.23 09:55 신고  댓글주소  수정/삭제

      반갑습니다.
      feelie님 말씀이 맞습니다.
      Window Buffer 는 대부분의 경우 인덱스와 집합분석함수의 over절이 일치할 때 발생합니다.

      예외적으로 Full Table Scan을 할 때 집합분석함수의over절에 order by가 없는 경우 발생합니다.

      두경우 모두 sort가 발생합니다.

  6. Ejql 2011.03.02 13:19 신고  댓글주소  수정/삭제  댓글쓰기

    2번 답은 맞추지 못했지만. 많은 내공이 쌓였습니다. 숙지해서 많은 곳에 적용해 볼 수 있겠습니다.
    감사합니다.

  7. 아삽 2011.06.28 16:03 신고  댓글주소  수정/삭제  댓글쓰기

    정말 놀랍군요. 대단하세요.
    오늘도 좋은 내용 배우고 갑니다.^^

  8. 소영아빠 2014.04.18 16:19 신고  댓글주소  수정/삭제  댓글쓰기

    소중한 정보 감사드립니다.
    아무런 노력없이 습득만 하는 제가 부끄럽네요..

    아무튼 건승하십시오..

필자가 제안한 SQL 튜닝방법론을 읽어 보았다면 다음을 기억할 것이다.


“SQL
튜닝방법론은 온라인 SQL 90% 만 해결할 수 있으며,
나머지 10%는 그때 그때 마다 다르게(On the fly 모드) 처리된다. 또한 그것들은 책이나 매뉴얼에 나와있지 않기 때문에 경험치 이거나 실험과 연구의 결과로 알아내는 것들이다.”

이번 글은 방법론에서 다룰 수 없는, 나머지 10%에 해당하는 경험치와 연구의 결과이다.


분석함수의 나머지 10%

오늘은 SQL 튜닝방법론에서 언급한 나머지 10%를 만났을 때 어떻게 해야 하는지 알아보자. 이번 글이 분석함수의 실행계획 시리즈의 마지막이다. 12에서 분석함수의 실행계획에 대해서 알아보았다. 하지만 두 개의 글로도 분석함수와 관련된 성능이슈를 모두 알 수는 없다. 왜냐하면 분석함수와 관련된 세밀한 튜닝포인트가 더 있기 때문이다. 이번에 다루게 될 실전 튜닝은 단순히 적절한 인덱스를 생성하여 비효율을 없애는 것이 아니다. 적절한 인덱스를 생성하는 것은 1부와 2부만 이해해도 충분하며, 이 글에서는 1부와 2부에서 언급하지 않은 것들을 설명하게 된다. 따라서 이 글에서 제시된 문제를 해결하지 못하면 분석함수를 튜닝 할 수 없는 경우를 많이 만날 것이다.

 

퀴즈는 SQL 두 개이며, 5가지 비효율을 개선해야 한다

이번에는 실전에서 발생할 수 있는 비효율에 대해서 두 가지 관점으로 논의한다. 분석함수를 사용할 때, 실행계획을 보고 비효율을 잡아내는 능력을 향상시키고, 실제로 SQL튜닝을 해보는 것이다. 반드시 문제를 직접 풀어보기 바란다. 당신이 튜너의 입장이 되어, 성능이 좋지 않은 SQL을 두 개 받았다고 가정하고 튜닝문제를 풀어보기 바란다. 두 가지 SQL 중 하나는 쉽고 나머지 하나는 어렵다. 두 개의 SQL에는 5가지의 튜닝 포인트가 있다. 각각의 포인트는 20점이며 5가지를 모두 맞추는 경우는 100점이 된다. 채점 시 중요한 점은 SQL을 맞추는 것이 아니라 비효율을 몇 개 잡아내었는지, 또한 각각의 비효율을 개선시킬 수 있는 방법은 몇 개나 생각했는지를 채점하는 것이다. 

  

이제 실습을 위한 테이블과 인덱스를 생성하자.

CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;

 

ALTER TABLE SALES_T ADD CONSTRAINT PK_SALES_T

PRIMARY KEY (PROD_ID, CUST_ID, CHANNEL_ID, TIME_ID) USING INDEX;

 

개발자가 작성한 문제의 SQL 1

 

SELECT *

  FROM ( SELECT /*+  INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                MAX(TIME_ID) OVER( PARTITION BY CUST_ID ,CHANNEL_ID ) AS MAX_TIME

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE TIME_ID = MAX_TIME;

 

업무설명 : 상품번호 30인 데이터에 대하여 고객별, 채널 별로 가장 최근의 판매량을 나타내시오.

             단 고객별, 채널별로 가장 최근의 데이터는 2건 이상일 수 있음.

-----------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | A-Rows |   A-Time   | Buffers | Used-Mem |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |  12649 |00:00:00.31 |   28337 |          |

|*  1 |  VIEW                         |            |  12649 |00:00:00.31 |   28337 |          |

|   2 |   WINDOW BUFFER               |            |  29282 |00:00:00.27 |   28337 | 1495K (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T    |  29282 |00:00:00.17 |   28337 |          |

|*  4 |     INDEX RANGE SCAN          | PK_SALES_T |  29282 |00:00:00.03 |     118 |          |

-----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("TIME_ID"="MAX_TIME")

   4 - access("PROD_ID"=30)

 

WHERE 절에 TIME_ID = MAX_TIME 이 존재하는 이유는 고객별, 채널별로 MAX(TIME_ID)에 해당하는 데이터가 두건 이상일 수 있기 때문이다. 이런 경우는 자주 발생한다. 이해를 돕기 위해 비슷한 예를 들어보자. 부서별로 최대급여를 받는 사람들을 출력하고자 할 때, 1번 부서의 최대급여는 1억 원이라고 하자. 그런데 그 부서에서 1억 원(최대급여)을 받는 사람은 강호동, 유재석 둘이라는 이야기 이다.

 

위의 SQL은 답이 정확하다. 하지만 성능은 비효율이 있어서 별로 신통치 못하다. 이제 문제를 보자. SQL의 비효율을 개선하는 것이 문제이다.

 

문제1) 위의 SQL 에서 성능상 문제점을 발견하고 개선하시오. 문제점은 두 가지(분석함수의 관점, 일반적인 SQL튜닝의 관점) 이다. 단 인덱스를 추가로 생성하거나 변경해서는 안 된다.

아래처럼 정답을 제시하면 된다.

 

문제1의 비효율 1 : 구체적인 설명

문제1의 비효율 1의 해결방법 : 구체적인 설명 

문제1의 비효율 1이 해결된 SQL과 실행계획 제시

 

문제1의 비효율 2 : 구체적인 설명

문제1의 비효율 2의 해결방법 : 구체적인 설명 

문제1의 비효율 2이 해결된 SQL과 실행계획 제시

 

이제 실제로 답안을 작성해보자.(답안 Sample)

 

문제1의 비효율 1 : SALES_T 테이블에 Random Access가 불필요하게 많이 발생한다. 12649 번만 테이블로 액세스 하면 되지만 실제로는 29282 번 액세스함으로써 비효율이 발생되었다.

 

문제1의 비효율 1의 해결방법 : PK인덱스에 AMOUNT_SOLD가 포함되어 있지 않으므로 Select 절에서 AMOUNT_SOLD를 빼면 인덱스만 액세스 하게 됨. 이때 Rowid를 추가로 Select 해야 한다. 이후에 이미 인덱스에서 Filter Rowid로 테이블을 액세스하면 Random Access정확히 12649번 만 시도한다. 아래에 SQL이 있으므로 참조하시오.

 

문제1의 비효율1이 해결된 SQL과 실행계획 제시:

 

SELECT /*+ LEADING(S) USE_NL(S1) */

        S.PROD_ID, S.CUST_ID, S.TIME_ID, S.CHANNEL_ID, S1.AMOUNT_SOLD

  FROM ( SELECT /*+  INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID,

                MAX(TIME_ID) OVER( PARTITION BY CUST_ID ,CHANNEL_ID ) AS MAX_TIME,

                ROWID AS RID

           FROM SALES_T T

          WHERE PROD_ID = 30 ) S,

       SALES_T S1

 WHERE S.TIME_ID = S.MAX_TIME

   AND S1.ROWID = S.RID;

  

------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |      1 |  12649 |00:00:02.72 |   11237 |          |

|   1 |  NESTED LOOPS               |            |      1 |  12649 |00:00:02.72 |   11237 |          |

|*  2 |   VIEW                      |            |      1 |  12649 |00:00:00.18 |     118 |          |

|   3 |    WINDOW BUFFER            |            |      1 |  29282 |00:00:00.13 |     118 | 1432K (0)|

|*  4 |     INDEX RANGE SCAN        | PK_SALES_T |      1 |  29282 |00:00:00.03 |     118 |          |

|   5 |   TABLE ACCESS BY USER ROWID| SALES_T    |  12649 |  12649 |00:00:02.48 |   11119 |          |

------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("S"."TIME_ID"="S"."MAX_TIME")

   4 - access("PROD_ID"=30)

 

이렇게 해서 Sample 답안을 작성해 보았다. 이 방법은 분석함수의 튜닝방법이 아니라 예외적으로 SQL 튜닝방법론에서 언급되었던 방법을 사용하였다. 물론 여기서 페이징처리를 한다면 추가적인 성능향상이 가능하지만 Sample이므로 여기서 멈추기로 한다. 위의 답안은 Sample 이므로 점수에서 빠진다. 또한 이 부분은 분석함수의 튜닝 방법이 아니다. 하지만 앞으로 풀게 될 문제들은 모두 분석함수와 관련된 문제들이다.

 

이제 시작해보자.

 

문제1에서 분석함수와 관련된 비효율이 하나 있다. 이와 관련된 비효율, 해결방법, 그리고 해결된 SQL과 실행계획을 제시해야 한다.

 

문제1의 비효율 2 : 구체적인 설명

문제1의 비효율 2 해결방법 : 구체적인 설명 

문제1의 비효율 2가 해결된 SQL과 실행계획 제시

 

이제 좀더 어려운 문제를 풀어보자. 아래의 문제를 100% 맞춘 사람은 아무도 없었다.

 

문제2) 아래의 SQL 에서 성능상 문제점을 발견하고 개선하시오. 성능상 문제점은 4가지 이다. 4가지의 비효율을 발견하고 이를 모두 개선해야 한다. 단 인덱스를 추가로 생성하거나 변경하면 안 된다.

SELECT *

  FROM

  ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER()     OVER ( PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID NULLS FIRST) AS RN,

                SUM(AMOUNT_SOLD) OVER ( ORDER BY CUST_ID,CHANNEL_ID,TIME_ID,PROMO_ID   ) AS SUM_AMT

           FROM SALES_T T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10 ;

 

---------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |            |      1 |     10 |00:00:00.43 |   28337 |          |

|*  1 |  VIEW                          |            |      1 |     10 |00:00:00.43 |   28337 |          |

|*  2 |   WINDOW SORT PUSHED RANK      |            |      1 |  29282 |00:00:00.40 |   28337 | 1999K (0)|

|   3 |    WINDOW SORT                 |            |      1 |  29282 |00:00:00.30 |   28337 | 1684K (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.18 |   28337 |          |

|*  5 |      INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.04 |     118 |          |

---------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PROD_ID" ORDER BY "CUST_ID","CHANNEL_ID",INTERNAL_FUNCTION("TIME_ID")

              NULLS FIRST)<=10)

   5 - access("PROD_ID"=30)

 

아래처럼 정답을 제시하면 된다.

 

문제2의 비효율 1 : 구체적인 설명

문제2의 비효율 1의 해결방법 : 구체적인 설명 

문제2의 비효율 1이 해결된 SQL과 실행계획 제시

 

문제2의 비효율 2 : 구체적인 설명

문제2의 비효율 2의 해결방법 : 구체적인 설명 

문제2의 비효율 2가 해결된 SQL과 실행계획 제시

 

문제2의 비효율 3 : 구체적인 설명

문제2의 비효율 3의 해결방법 : 구체적인 설명 

문제2의 비효율 3이 해결된 SQL과 실행계획 제시

 

문제2의 비효율 4 : 구체적인 설명

문제2의 비효율 4의 해결방법 : 구체적인 설명 

문제2의 비효율 4가 해결된 SQL과 실행계획 제시

 

문제의 구성
이렇게 하여 총 다섯 문제가 출제되었다. 문제 1에는 비효율이 하나이고 문제 2에는 비효율이 넷이다. 다섯 가지 비효율 모두 분석함수와 관련된 것들이다. 실습환경은 Oracle 10g R2, Oracle 11g R1, Oracle 11g R2로 한정한다. 답안은 2011년 2 18일 공개될 예정이다. 답안을 바로 공개하지 않는 이유는 튜닝실습을 유도하기 위함이다. SQL 두개를 분석하는데 이틀이면 넉넉한 시간이므로 충분히 생각하고 답을 작성하기 바란다. 자신이 튜너가 되었다고 가정하고 비효율을 모두 찾아보길 바란다.


다른 사람들은 몇 점 정도 받았나?

몇몇 지인들에게 퀴즈를 내본 결과 100점은 아무도 없었다. 80점이 평균적인 튜너의 수준이라는 이야기 이다. 개발자의 경우는 60점을 받더라도 실망해서는 안 된다. 개발자가 60점이면 괜찮은 수준이다. 점수에 대해 오해는 하지 말기 바란다. 이 퀴즈로 받은 점수는 분석함수와 관련된 것일 뿐, 전반적인 SQL 튜닝능력에 대한 점수가 아니다. 또한 100 점 이상을 받을 수도 있다. 필자가 알고 있는 튜닝포인트는 5가지뿐이다. 여러분들이 6~7가지 이상의 비효율과 튜닝 포인트를 잡아내어 필자를 능가하는 점수를 받는 것도 가능하다.


행운을 빈다.

 

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. 2011.02.15 13:13  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.15 13:39 신고  댓글주소  수정/삭제

      비효율은 총 다섯가지 입니다. (문제 1에 한가지, 문제 2에 네가지 )
      될수 있으면 형식을 지켜주세요.
      아래처럼 작성하시면 됩니다.

      문제1의 비효율 2 : 구체적인 설명
      문제1의 비효율 2 해결방법 : 구체적인 설명
      문제1의 비효율 2가 해결된 SQL과 실행계획 제시


      문제2의 비효율 1 : 구체적인 설명
      문제2의 비효율 1의 해결방법 : 구체적인 설명
      문제2의 비효율 1이 해결된 SQL과 실행계획 제시

      문제2의 비효율 2 : 구체적인 설명
      문제2의 비효율 2의 해결방법 : 구체적인 설명
      문제2의 비효율 2가 해결된 SQL과 실행계획 제시


      문제2의 비효율 3 : 구체적인 설명
      문제2의 비효율 3의 해결방법 : 구체적인 설명
      문제2의 비효율 3이 해결된 SQL과 실행계획 제시

      문제2의 비효율 4 : 구체적인 설명
      문제2의 비효율 4의 해결방법 : 구체적인 설명
      문제2의 비효율 4가 해결된 SQL과 실행계획 제시

  2. Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.15 18:01 신고  댓글주소  수정/삭제  댓글쓰기

    제가 위의 형식으로 답글을 작성할 예정입니다.
    여러분들이 답을 위의 형식으로 작성해야 점수를 측정하기 쉽습니다.

  3. salvationism 2011.02.16 16:58 신고  댓글주소  수정/삭제  댓글쓰기

    문제1의 비효율 : 분석함수 측면에서의 비효율이라길래 Table을 Random Access하기전에
    29282건을 12649건으로 줄일 수 있는 방법이 없는지 고민을 많이 해봤는데 분석함수 측면에서는
    그러한 방법은 없는거 같습니다. 대신에 메모리쪽에서 1495K의 Sort를 사용하는 비효율이 보임.

    문제1의 비효율 해결방법 : WINDOW BUFFER에 의한 SORT 비효율을 해결하기 위해 rank함수를 사용함.

    문제1의 비효율 해결된 SQL과 실행계획 제시 :

    SELECT *
    FROM ( SELECT /*+ INDEX_DESC(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,
    rank() over(partition by CUST_ID ,CHANNEL_ID order by CUST_ID desc ,CHANNEL_ID desc, TIME_ID desc) as rn
    FROM SALES_T T
    WHERE PROD_ID = 30 )
    WHERE rn = 1;

    ----------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | 12649 |00:00:00.11 | 28286 | | | |
    |* 1 | VIEW | | 1 | 23706 | 12649 |00:00:00.11 | 28286 | | | |
    |* 2 | WINDOW NOSORT | | 1 | 23706 | 29282 |00:00:00.48 | 28286 | 1588K| 621K| |
    | 3 | TABLE ACCESS BY INDEX ROWID | SALES_T | 1 | 23706 | 29282 |00:00:00.35 | 28286 | | | |
    |* 4 | INDEX RANGE SCAN DESCENDING| PK_SALES_T | 1 | 23706 | 29282 |00:00:00.07 | 245 | | | |
    ----------------------------------------------------------------------------------------------------------------------------------

  4. salvationism 2011.02.16 17:00 신고  댓글주소  수정/삭제  댓글쓰기

    문제 2번과 같은경우는 업무요건 설명없이 비효율 찾아서 해결하면 되는 것인가요??
    문제는 항상 생각을 많이하게 되네요

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.16 17:02 신고  댓글주소  수정/삭제

      네 말씀하신대로 문제 2번은 업무설명이 없어도 됩니다.
      그냥 분석함수와 관련된 비효율을 네개 찾아내고 개선하면 되는 것이죠. 2월 18일날 채점을 해보시기 바랍니다.
      good luck
      감사합니다.

  5. salvationism 2011.02.16 17:04 신고  댓글주소  수정/삭제  댓글쓰기

    다시 자세히 보니 제가 문제 1번 푼거는 답이 틀린거 같네요.. 중복값이 있다는 것이죠?

  6. 2011.02.17 16:17  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.17 21:58 신고  댓글주소  수정/삭제

      안녕하세요. 답글을 아주 자세히 달아주셨습니다.
      결론 부터 말씀드리겠습니다.
      내부 원리를 알고 비효율을 작성한 관점으로는 한두가지를 놓치셨습니다. rownum을 row_number를 사용하는 것으로 대체하고, window의 범위만 지정하였다면 만점 인데 말입니다.^^

      하지만 SQL의 출력결과와 성능이 동일함으로 SQL 튜닝결과로만 본다면 만점입니다. ROWNUM을 사용한 것은 조금 위험하긴 합니다. http://scidb.tistory.com/entry/Indexdesc-힌트와-rownum-1-조합은-안전한가 를 참조하세요.

      전문 컨설턴트 들도 하나 혹은 둘 정도 놓치는 걸 보면, 분석함수에 관해서는 거의 대등한 실력을 가지고 있다고 생각합니다. 조금만 더 노력하신다면 좋은 결과가 있을 것으로 생각 됩니다.

      18일 00시에 답글이 올라 감으로, 직접 채점해 보시기 바랍니다. 내공이 향상될 것입니다.
      감사합니다.

  7. AsuraChaos 2011.02.18 18:56 신고  댓글주소  수정/삭제  댓글쓰기

    이런 좋은 기회를 만들어 주신 점 감사드립니다. ^^
    제가 놓친 부분을 답안을 보니 이해할 수 있었습니다.
    참조 블로그 글도 읽어 보니 그런 위험이 있었네요. 튜닝하는 과정에서 결과 값이 달라지는 것을 확인하였지만,
    그 부분을 정확히 어떻게 컨트롤을 해야하는지 내공이 부족하였습니다.
    이 부분이 저한테 참 인상적이네요. 거북이 정신. ~.~
    저에게도 뚜렷한 장점은 없는거 같습니다.
    튜닝이라는 분야가 하루 아침에 되는 것도 아니고, 천천히 한걸음 한걸음 열심히 기어가고 싶습니다.
    항상 건강하시기 바랍니다.
    수고하십시요.


빠른 이해를 위해서 이전 을 먼저 보고 이 글을 읽기 바란다. 이 글의 1부에서 WINDOW SORT, WINDOW SORT PUSHED RANK, WINDOW NOSORT, WINDOW NOSORT STOPKEY OPERATION들을 정리해 보았다. 이번에는 나머지 네 가지 Operation에 관해 알아볼 것이다. 이번 시간을 마치면 분석함수의 실행계획은 모두 정리된다.

 

WINDOW (SORT)  

WINDOW (SORT PUSHED RANK)

WINDOW (NOSORT)

WINDOW (NOSORT STOPKEY)   <-- 저번 시간에는 여기까지

WINDOW (BUFFER)

WINDOW (BUFFER PUSHED RANK)

WINDOW (CHILD PUSHED RANK)

WINDOW (IN SQL MODEL) SORT <-- 이번 시간에는 여기까지

 

5. WINDOW BUFFER

WINDOW BUFFER는 기본적으로 Ranking Family를 사용하지 않는 경우에 발생한다. 또한 인덱스가 적절하거나, 혹은 OVER절에 ORDER BY구문이 없는 경우에 발생한다. where절의 filter 조건이 있다면, 전체 건을 Sort 하지 않지만 최종 결과 건(Window의 범위)에 대해서는 sort가 필요하다.

 

SELECT PROD_ID,

       SUM(AMOUNT_SOLD) OVER() AS SUM_AVG

  FROM SALES_T

WHERE PROMO_ID = 33;

 

---------------------------------------------------------------------------------

| Id  | Operation          | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |   2074 |00:00:00.04 |    4440 |          |

|   1 |  WINDOW BUFFER     |         |   2074 |00:00:00.04 |    4440 |59392  (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |   2074 |00:00:00.03 |    4440 |          |

---------------------------------------------------------------------------------

 

위의 결과를 보면 2074건에 대하여 Sort가 발생하였다. 하지만 아래는 다르다.

 

SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

       SUM(AMOUNT_SOLD) OVER(ORDER BY NULL) AS RN

  FROM SALES_T ;

 

---------------------------------------------------------------------------------

| Id  | Operation          | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |    918K|00:00:03.69 |    4440 |          |

|   1 |  WINDOW BUFFER     |         |    918K|00:00:03.69 |    4440 |   35M (0)|

|   2 |   TABLE ACCESS FULL| SALES_T |    918K|00:00:00.96 |    4440 |          |

---------------------------------------------------------------------------------

 

WHERE 절이 없으므로 WINDOW의 범위는 전체이다. 전체 건( 92만건) SORT해야 하므로 PGA 35M나 사용하였다. OVER절에 ORDER BY가 없지만, 전체 건을 SORT 했으므로 WINDOW BUFFER WINDOW SORT Operation이 발생한 것과 마찬가지 이다.

 

이제 인덱스를 사용하면서 OVER절에 ORDER BY가 있는 경우를 살펴보자.

 

SELECT /*+ INDEX(S PK_SALES_T) */

         SUM(AMOUNT_SOLD) OVER(ORDER BY S.CUST_ID, S.CHANNEL_ID, S.TIME_ID ) AS "누적금액",

        S.*

  FROM SALES_T S

 WHERE S.PROD_ID = 22

 ORDER BY S.CUST_ID, S.CHANNEL_ID, S.TIME_ID ;

 

----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name       | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |            |   3441 |00:00:00.06 |    2911 |          |

|   1 |  WINDOW BUFFER               |            |   3441 |00:00:00.06 |    2911 |  206K (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID| SALES_T    |   3441 |00:00:00.05 |    2911 |          |

|*  3 |    INDEX RANGE SCAN          | PK_SALES_T |   3441 |00:00:00.04 |      17 |          |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("S"."PROD_ID"=22)

 

인덱스 (PROD_ID + CUST_ID + CHANNEL_ID + TIME_ID)컬럼과 OVER절의 ORDER BY구문의 컬럼순서가 동일하므로 WINDOW SORT 가 발생하지 않고 WINDOW BUFFER 가 발생하였다. SQL의 마지막에 위치한 ORDER BY 구문은 인덱스 컬럼과 일치하므로 별도의 SORT가 필요하지 않다.

 

6. WINDOW BUFFER PUSHED RANK

OPERATION RANK가 있으므로 Ranking Family를 사용한 것이다. OPERATION을 볼 수 있으면 좋겠지만 10g에서 사라졌다. Oracle 9i 까지는 적절한 인덱스가 있는 경우에 Ranking Family를 사용하고, /인라인 뷰 외부에서 분석함수를 Filter로 사용한다면 WINDOW BUFFER PUSHED RANK가 발생하였다. 하지만 10g부터는 더 효율적인 WINDOW NOSORT(10g R1) 혹은 WINDOW NOSORT STOPKEY(10g R2)로 대체되었다. 참고로 야함나르 독자님에 의하면 버전 9.2.0.7에서도 WINDOW NOSORT가 발생한다고 한다. ()

 

9i에서 직접 테스트 해보면 좋겠지만 환경이 그렇지 못하므로 Julian Dyke의 테스트 내용을 인용한다.

 

This example was developed using Oracle 9.2.0.1 on Windows 2000

 

This example requires the following table definition

 

    CREATE TABLE t1 (c1 NUMBER NOT NULL,c2 NUMBER);

 

    CREATE INDEX i1 ON t1 (c1);

The table must be analysed

 

    ANALYZE TABLE t1 COMPUTE STATISTICS;

The statement

 

    SELECT c2,r1

FROM ( SELECT c2,RANK () OVER (ORDER BY c1) AS r1

               FROM t1 )

    WHERE r1 < 10;

 

9i의 실행계획

0     SELECT STATEMENT Optimizer=CHOOSE

1   0   VIEW

2   1     WINDOW (BUFFER PUSHED RANK)

3   2       TABLE ACCESS (BY INDEX ROWID) OF 'T1'

4   3         INDEX (FULL SCAN) OF 'I1' (NON-UNIQUE)

 

테스트의 출처 http://www.juliandyke.com/Optimisation/Operations/WindowBufferPushedRank.html

 

 

10g R2의 실행계획

---------------------------------------------------------------------------

| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |      |     1 |    26 |     1   (0)|

|*  1 |  VIEW                         |      |     1 |    26 |     1   (0)|

|*  2 |   WINDOW NOSORT STOPKEY       |      |     1 |    26 |     1   (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |     1 |    26 |     1   (0)|

|   4 |     INDEX FULL SCAN           | I1   |     1 |       |     1   (0)|

---------------------------------------------------------------------------

 

10g R2에서는 WINDOW BUFFER PUSHED RANK 대신에 WINDOW NOSORT STOPKEY 가 발생하므로 SORT가 전혀 발생하지 않으며 STOPKEY가 발생하여 필요한 부분만 SCAN된다. 따라서 TOP SQL이나 페이징 처리시 상당한 성능개선 효과를 볼 수 있다.

 

7. WINDOW CHILD PUSHED RANK

Ranking Family를 사용하고 인라인 뷰 외부에서 분석함수를 Filter로 사용한다면 실행계획에 ~PUSHED RANK 혹은 ~STOPKEY가 발생한다고 하였다. WINDOW CHILD PUSHED RANK도 마찬가지이다. 단 하나의 차이점은 Parallel Query를 사용했다는 것이다.

 

SELECT *

  FROM (SELECT /*+ FULL(T) PARALLEL(T 4) */  PROMO_ID,

               RANK() OVER(ORDER BY PROD_ID) AS RN

          FROM SALES_T T)

WHERE RN = 1

 

--------------------------------------------------------------------------------------------

| Id  | Operation                    | Name     | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |          |   6002 |00:00:01.84 |       5 |          |

|*  1 |  VIEW                        |          |   6002 |00:00:01.84 |       5 |          |

|*  2 |   WINDOW SORT PUSHED RANK    |          |   6003 |00:00:01.83 |       5 |  142K (0)|

|   3 |    PX COORDINATOR            |          |   6006 |00:00:01.81 |       5 |          |

|   4 |     PX SEND QC (RANDOM)      | :TQ10000 |      0 |00:00:00.01 |       0 |          |

|*  5 |      WINDOW CHILD PUSHED RANK|          |      0 |00:00:00.01 |       0 |96256  (0)|

|   6 |       PX BLOCK ITERATOR      |          |      0 |00:00:00.01 |       0 |          |

|*  7 |        TABLE ACCESS FULL     | SALES_T  |      0 |00:00:00.01 |       0 |          |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"=1)

   2 - filter(RANK() OVER ( ORDER BY "PROD_ID")<=1)

   5 - filter(RANK() OVER ( ORDER BY "PROD_ID")<=1)

   7 - access(:Z>=:Z AND :Z<=:Z)

 

ID 5번에 WINDOW CHILD PUSHED RANK가 발생했음에도 ID 2번에 WINDOW SORT PUSHED RANK가 발생한 이유는 QUERY COORDINATOR가 개별 PARALLEL SLAVE들을 취합해야 하기 때문이다.

 

8. WINDOW IN SQL MODEL SORT

MODEL절에서 분석함수를 사용하는 경우에 발생한다.

 

SELECT PROD_ID,a1,a2

  FROM (SELECT PROD_ID,SUM(AMOUNT_SOLD) a1

          FROM SALES_T

         GROUP BY PROD_ID )

       MODEL DIMENSION BY (PROD_ID)

       MEASURES (a1,0 a2)

       RULES ( a2[any] = SUM (a1) OVER () ) ;  --> 분석함수 사용

 

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |         |     72 |00:00:01.91 |    4440 |          |

|   1 |  SQL MODEL ORDERED          |         |     72 |00:00:01.91 |    4440 |  405K (0)|

|   2 |   HASH GROUP BY             |         |     72 |00:00:01.91 |    4440 | 2516K (0)|

|   3 |    TABLE ACCESS FULL        | SALES_T |    918K|00:00:00.93 |    4440 |          |

|   4 |   WINDOW (IN SQL MODEL) SORT|         |     72 |00:00:00.01 |       0 | 2048  (0)|

------------------------------------------------------------------------------------------

 

위와 아래의 SQL은 같은 결과를 출력한다. 그리고 PGA 사용량도 비슷하다.

 

SELECT PROD_ID,SUM(AMOUNT_SOLD) a1, sum(SUM(AMOUNT_SOLD)) OVER() as a2

  FROM SALES_T

 GROUP BY PROD_ID ;

 

----------------------------------------------------------------------------------

| Id  | Operation           | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |         |     72 |00:00:01.91 |    4440 |          |

|   1 |  WINDOW BUFFER      |         |     72 |00:00:01.91 |    4440 | 2048  (0)|

|   2 |   HASH GROUP BY     |         |     72 |00:00:01.91 |    4440 | 2515K (0)|

|   3 |    TABLE ACCESS FULL| SALES_T |    918K|00:00:00.93 |    4440 |          |

----------------------------------------------------------------------------------

 

단순히 전체 합계를 출력하는 것이라면 MODEL절 보다는 분석함수를 사용하는 것이 조금 유리하다. SQL MODEL ORDERED OPERATION이 없기 때문이다. 참고로 MODEL절이 무엇인지 궁금한 사람은 SQL 포기하지마라 , Model 절에 대하여 라는 두 가지 글을 보기 바란다.

 

결론

 

WINDOW (SORT) :

-FTS를 사용하거나 인덱스가 적절하지 않은 경우 발생 

WINDOW (SORT PUSHED RANK) :

-위와 같으나 Ranking Family를 사용해야 하며, /인라인뷰 외부에서 분석함수를 filter로 사용하는 경우 발생

WINDOW (NOSORT) :

-인덱스가 적절한 경우나, OVER절에 ORDER BY가 없는 Ranking Family를 사용한 경우 발생

WINDOW (NOSORT STOPKEY) :

-위와 같으나 Ranking Family를 사용해야 하며, /인라인뷰 외부에서 분석함수를 filter로 사용하는 경우 발생

-10g R2 신기능

WINDOW (BUFFER) :

    - Ranking Family를 사용하지 않고 ORDER BY가 없는 경우이거나 적절한 인덱스를 사용하는 경우 발생

WINDOW (BUFFER PUSHED RANK) :

- Ranking Family를 사용하고, /인라인뷰 외부에서 분석함수를 filter로 사용해야 하며, 8i~9i 에서만 발생됨.

- 비효율을 개선하여 10g R1에서는 WINDOW (NOSORT)가 발생하며, 10g R2에서는 WINDOW (NOSORT STOPKEY)가  발생됨

WINDOW (CHILD PUSHED RANK) :

   - Ranking Family를 사용하고, /인라인뷰 외부에서 분석함수를 filter로 사용해야 하며, Parallel Query를 사용하는 경우 발생

WINDOW (IN SQL MODEL) SORT

   - Model 절에서 분석함수로 Rule을 정하는 경우 발생

 

심화학습이 필요한 이유

저번 글과 이번 글에서 분석함수를 사용할 때 나올 수 있는 실행계획을 정리해 보았다. 이제 분석함수의 실행계획을 보면 어떤 환경에서 실행되었는지, 어떤 비효율이 있는지 감을 잡을 수 있을 것이다. 다음 글에서는 위의 결론을 이용하여 좀더 고차원적인 심화과정에 도전할 것이다. 다음 글의 목적은 실전에서 분석함수를 사용할 때, 비효율을 잡아내는 능력을 향상시키고, 실제로 성능개선을 해보는 것이다.

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. 야함나르 2011.02.10 16:48 신고  댓글주소  수정/삭제  댓글쓰기

    같은 데이터 / 쿼리로 11R2 test 환경에서 실행해보니 원하는 실행계획으로 수행되는군요.

    언제나 좋은 글 감사합니다.

    (오늘에서야 The logical optimizer를 구매했습니다 ^^.책 보면서 쭉 정리를 해봐야겠습니다.)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.10 17:53 신고  댓글주소  수정/삭제

      반갑습니다.
      그이유로 10g R2 부터 성능이 획기적으로 좋아졌습니다.

      열심히 공부하셔서 logical optimizer를 정복하세요.
      감사합니다.

  2. salvationism 2011.02.10 18:50 신고  댓글주소  수정/삭제  댓글쓰기

    다음 글 기대됩니다. ^^

  3. 혈기린 2011.02.11 10:45 신고  댓글주소  수정/삭제  댓글쓰기

    window buffer 는 인덱스를 이용하여 sort가 일어나지 않는거나 인덱스를 사용하지 못해 sort가 일어나도 똑같은 용어를 사용하 보네요 전 당연히 window buffer 하고 window buffer nosort이렇게 나누어질줄 알았는데
    모델절은 아직 잘 이해가 안가네요 다시 공부해봐야 겠습니다 ^^
    좋은글 감사 드립니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.11 23:05 신고  댓글주소  수정/삭제

      말씀하신대로 window buffer는 결과건에 대해서는 sort가 발생합니다. where 절의 filter가 건수를 많이 줄여주면 sort의 부하는 줄어들 수 있습니다.
      감사합니다.

  4. feelie 2011.02.12 11:33 신고  댓글주소  수정/삭제  댓글쓰기

    분석함수 실행계획에 대한 정리 잘 봤습니다..
    다음 글이 올라오기전에 확실히 정리해서 직접튜닝에 도전해보고 싶네요...

  5. salvationism 2011.02.14 10:39 신고  댓글주소  수정/삭제  댓글쓰기

    WINDOW CHILD PUSHED RANK 실행계획에서
    Id 5번 WINDOW CHILD PUSHED RANK에서 왜 pga를 저렇게 사용하는지 의문입니다.
    어차피 PX SEND QC (RANDOM)으로 보내서 상위에서 Sort를 할거면서 말이죠.
    실행계획만 보면 굉장히 비효율적으로 보입니다.
    분석함수는 10053 떠도 특별한건 안나오네요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.14 13:43 신고  댓글주소  수정/삭제

      반갑습니다.
      비효율적일 수 밖에 없는 이유는 PARALLEL SLAVE를 여러개 뛰워야 하기 때문입니다. 댓글에서는 SLAVE가 3개라고 가정 하겠습니다. 그리고 PROD_ID가 5종류(1,2,3,4,5)뿐이라고 가정 합니다. 그러면 순위기준이 ORDER BY PROD_ID 라고 했을때 1위는 PROD_ID 1번 뿐입니다. 하지만 PARALLEL을 작동시키면 1위가 여러개 생깁니다. 아래의 예제를 보시죠.

      1번 SLAVE에서는 읽은 데이터가 3,4,2 이고 랭킹 1위는 PROD_ID 2번입니다.
      2번 SLAVE에서는 읽은 데이터가 3,1,5 를 읽었고 랭킹 1위는 PROD_ID 1번입니다.
      3번 SLAVE에서는 읽은 데이터가 2,5,3 를 읽었고 랭킹 1위는 PROD_ID 2번입니다.

      그런데 쿼리 코디네이터는 이 데이터들을 취합하여 랭킹 1위는 PROD_ID 1번이라는 것을 다시 계산해야만 합니다. 어쩔 수 없는 것이죠.

      하지만 비효율은 최소화 됩니다. parallel 을 사용하지 않고 rank를 적용하게 되면 12 MB가 필요합니다.

      SELECT /*+ FULL(T) */ PROD_ID,
      RANK() OVER(ORDER BY PROD_ID) AS RN
      FROM SALES_T T

      -------------------------------------------
      | Operation | Name | Used-Mem |
      -------------------------------------------
      | SELECT STATEMENT | | |
      | WINDOW SORT | | 12M (0)|
      | TABLE ACCESS FULL| SALES_T | |
      -------------------------------------------

      하지만 코디네이터는 12 MB가 아닌 142K만 사용하므로 비효율이 크지는 않습니다.

      감사합니다.

  6. salvationism 2011.02.14 13:47 신고  댓글주소  수정/삭제  댓글쓰기

    그렇군요. 감사합니다.
    코디네이터가 취합하기 이전에
    각각의 SLAVE 프로세스에서 먼저 RANK를 먹인다.
    그래서 이름이 "WINDOW CHILD PUSHED RANK" 인가요? ㅎㅎ

  7. 클락 2011.04.12 13:06 신고  댓글주소  수정/삭제  댓글쓰기

    좋은내용 잘 보고 갑니다. 퍼갈께욤 ^^


Oracle Data Access Pattern

Partition Access Pattern

Oracle 조인 방법 25가지

 

필자는 위의 글들을 이미 게시하였다. 그래서 Access Path Join Method는 정리가 되었고, 블로그로 배포되었으므로 그것으로 끝이라고 생각했다. 하지만 어느 개발자와 대화를 해보니 그런 생각이 짧았음을 알게 되었다.

 

 

개발자 : 위의 3가지 문서는 잘 보고 있습니다. 하지만 Access Path에 관해서는 큰 구멍이 있습니다.


필자 : ? 무슨 말인가요?


개발자 : 분석함수는 Access Path를 변경시킵니다. 즉 분석함수를 어떻게 사용하느냐에 따라 실행계획의 Operation이 변경되므로 여타의 함수와는 엄연히 다릅니다. 그러므로 수석님께서 분석함수와 관련된 Access Path를 정리해 주셨으면 합니다.


필자 : …. 프로젝트도 있고, 블로그도 관리해야 하고, 책도 써야 하고, 개인적인 공부도 해야 하는데요. 시간이 나질 않습니다. (! 말을 하자마자 실수였다는 것을 알아차렸다. 블로그 관리라니……)


개발자 : 지금 하시고 계신 네 가지 이외의 일을 해달라는 게 아닙니다. 분석함수의 실행계획을 정리하셔서 블로그에 올리시면, 그게 블로그 관리 아닌가요?


필자 : …..

 

 

이번에는 공짜가 아니다

이렇게 해서 이 글을 쓰게 되었다. 해당개발자는 언제 나에게 맥주 한잔 사기 바란다. 이런 식으로 말려들어서 글을 작성한 적이 한두번이 아니다. ^^

 

분석함수의 내부

분석함수를 이용하여 답을 잘 내었으므로 그것으로 만족하는 사람들이 있다. 답을 내는 것은 표면적인 것(문법)을 이해한 것이다. 하지만 그것만 알아서는 내부적인 매커니즘을 이해할 수 없다. 즉 분석함수를 사용함에 따라 Access Path(Operation)가 어떻게 변경되는지도 알아야 모두를 아는 것이다. 분석함수의 내부적인 Operation을 모른다면 SQL 작성은 할지 몰라도, 성능을 향상시킬 수는 없다. 오늘의 목표는 분석함수의 내부를 정복함으로써 튜닝을 가능하게 하는 것이다.

 

분석함수의 실행계획은 아래와 같이 총 8가지로 예상된다. 만약, 아래의 List 이외의 것을 발견하면 이 글을 보는 독자들을 위해 알려주기 바란다. 글이 너무 길어지므로, 이번 시간에는 8개중에 위의 4개를 알아보고자 한다. 어떤 경우에 아래의 4가지 실행계획이 발생하는지 알아보자.

 

WINDOW (SORT)  

WINDOW (SORT PUSHED RANK)

WINDOW (NOSORT)

WINDOW (NOSORT STOPKEY)   <-- 이번 시간에는 여기까지

WINDOW (BUFFER)

WINDOW (BUFFER PUSHED RANK)

WINDOW (CHILD PUSHED RANK)

WINDOW (IN SQL MODEL) SORT

 

환경: Oracle 11.2.0.1

테스트를 위해 테이블과 인덱스를 만든다.

 

CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;

 

CREATE INDEX IX_PROD ON SALES_T( PROD_ID );

 

1. WINDOW SORT

분석함수를 사용하면 기본적으로 SORT가 발생한다. 이때 SORT를 대체할 수 있는 적절한 인덱스가 없다면 WINDOW SORT가 발생한다. 아래의 경우가 대표적이다.

 

-인덱스와 분석함수 OVER절의 컬럼이 일치하지 않을 때 발생한다.

-분석함수를 사용하고 Full Table Scan을 사용할 때 발생한다.

 

먼저 인덱스와 OVER 절의 컬럼이 일치하지 않는 경우를 살펴보자.

 

SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

       ROW_NUMBER() OVER(ORDER BY CUST_ID) AS RN

  FROM SALES_T

 WHERE PROD_ID = 30;

 

-------------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |  29282 |00:00:00.18 |     424 |          |

|   1 |  WINDOW SORT                 |         |  29282 |00:00:00.18 |     424 | 1558K (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID| SALES_T |  29282 |00:00:00.09 |     424 |          |

|*  3 |    INDEX RANGE SCAN          | IX_PROD |  29282 |00:00:00.03 |      60 |          |

-------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("PROD_ID"=30)

 

 인덱스는 PROD_ID 로 구성되었지만, OVER절은 CUST_ID로 되어 서로 다르므로 실행계획의 Id 2번에 WINDOW SORT가 발생하였다.

 

이제 분석함수를 사용하면서 FTS(Full Table Scan)이 발생하는 경우를 보자.

 

SELECT PROD_ID,

       SUM(AMOUNT_SOLD) OVER() AS SUM_AVG

  FROM SALES_T

WHERE PROMO_ID = 33

ORDER BY CUST_ID;

 

---------------------------------------------------------------------------------

| Id  | Operation          | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |   2074 |00:00:00.04 |    4440 |          |

|   1 |  WINDOW SORT       |         |   2074 |00:00:00.04 |    4440 |  102K (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |   2074 |00:00:00.04 |    4440 |          |

---------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("PROMO_ID"=33)

 

WHERE 절이 PROD_ID가 아니라 PROMO_ID이므로 FTS가 발생하였다. 이때 분석함수를 사용하면 WINDOW SORT가 발생된다.

이때 ORDER BY절이 있더라도 별도의 SORT ORDER BY operation이 발생하지 않는다. 분석함수가 SORT를 대신하기 때문이다.  이때 SORT의 일량은 동일하다. 즉 분석함수와 ORDER BY절이 동시에 존재한다면, SORT의 일량은 분석함수가 없고 ORDER BY만 존재하는 SQL과 동일 하다. 아래의 SQL이 이 사실을 증명한다.

 

SELECT PROD_ID, AMOUNT_SOLD

  FROM SALES_T

WHERE PROMO_ID = 33

ORDER BY CUST_ID;

 

---------------------------------------------------------------------------------

| Id  | Operation          | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |   2074 |00:00:00.04 |    4440 |          |

|   1 |  SORT ORDER BY     |         |   2074 |00:00:00.04 |    4440 |  102K (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |   2074 |00:00:00.04 |    4440 |          |

---------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("PROMO_ID"=33)

 

위에서 설명된 SQL의 분석함수 + ORDER BY SORT 일량과 ORDER BY만 사용한 SORT의 일량은 102K로 동일함을 알 수 있다. 즉 분석함수와 ORDER BY를 같이 사용하던지, 아니면 ORDER BY만 사용하던지 간에 SORT의 부하는 동일하다는 이야기 이다.

 

2.WINDOW SORT PUSHED RANK

위에서 적당한 인덱스가 없을 때, 분석함수를 사용하면 WINDOW SORT가 발생한다고 하였다. 이번에는 WINDOW SORT에 대해 좀더 깊이 들어가 보자.

 

SELECT *

  FROM ( SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER() OVER(ORDER BY CUST_ID) AS RN

           FROM SALES_T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10;

 

--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |     10 |00:00:00.13 |     424 |          |

|*  1 |  VIEW                         |         |     10 |00:00:00.13 |     424 |          |

|*  2 |   WINDOW SORT PUSHED RANK     |         |     11 |00:00:00.13 |     424 | 2048  (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T |  29282 |00:00:00.10 |     424 |          |

|*  4 |     INDEX RANGE SCAN          | IX_PROD |  29282 |00:00:00.03 |      60 |          |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID")<=10)

   4 - access("PROD_ID"=30)

 

이 글에서 처음 소개된 SQL과 같지만, 인라인뷰 외부에서 분석함수 컬럼을 filter 조건으로 사용하였다. 즉 상위 10건만 보자고 한 것이다. 이런 경우 WINDOW SORT PUSHED RANK operation이 발생하며 SORT의 부하는 제한된다. 다시 말해 RN <= 10 조건에 의해 전체를 SORT할 필요가 없이 10건의 배열을 만들고, 그 배열만 SORT 하면 된다. 이 글에서 최초로 설명된 SQL SORT 일량과 WINDOW SORT PUSHED RANK의 일량을 비교해보면 1558K 2K 로 천지차이이다. 분석함수 중에 순위를 구하는 것(RANK, DENSE_RANK, ROW_NUMBER)을 인라인뷰 외수에서 filter로 사용하면 적절한 인덱스가 없더라도 Sort의 부하는 최소화 된다는 것을 기억하자.

 

이런 사실은 분석함수를 사용하지 않더라도 동일하게 발생한다. 즉 위에서 실행된 SQL SORT량과 아래의 SORT이 동일하다. 아래는 분석함수를 사용하지 않고 ROWNUM을 사용하였다.

 

SELECT *

  FROM ( SELECT /*+ INDEX(T IX_PROD) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD

           FROM SALES_T T

          WHERE PROD_ID = 30

          ORDER BY CUST_ID)

 WHERE ROWNUM <= 10;

 

---------------------------------------------------------------------------------------------

| Id  | Operation                      | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |         |     10 |00:00:00.13 |     424 |          |

|*  1 |  COUNT STOPKEY                 |         |     10 |00:00:00.13 |     424 |          |

|   2 |   VIEW                         |         |     10 |00:00:00.13 |     424 |          |

|*  3 |    SORT ORDER BY STOPKEY       |         |     10 |00:00:00.13 |     424 | 2048  (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| SALES_T |  29282 |00:00:00.09 |     424 |          |

|*  5 |      INDEX RANGE SCAN          | IX_PROD |  29282 |00:00:00.03 |      60 |          |

---------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=10)

   3 - filter(ROWNUM<=10)

   5 - access("PROD_ID"=30)

 

WINDOW SORT PUSHED RANK 대신에 SORT ORDER BY STOPKEY operation이 사용되었지만 하는 일과 원리는 동일하므로 SORT량도 동일하다.

 

3.WINDOW NOSORT

적절한 인덱스가 없을 때 분석함수를 사용하면 WINDOW SORT가 발생한다고 하였다. 이번에는 동일한 SQL을 실행하되 적절한 인덱스를 만들고 실행해보자.

 

CREATE INDEX PK_SALES_T ON SALES_T(PROD_ID, CUST_ID, CHANNEL_ID, TIME_ID);

 

SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

       ROW_NUMBER() OVER(ORDER BY CUST_ID) AS RN

  FROM SALES_T T

 WHERE PROD_ID = 30;

 

----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name       | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |            |  29282 |00:00:00.34 |   28352 |          |

|   1 |  WINDOW NOSORT             |            |  29282 |00:00:00.34 |   28352 |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| SALES_T    |  29282 |00:00:00.24 |   28352 |          |

|*  3 |    INDEX RANGE SCAN          | PK_SALES_T |  29282 |00:00:00.07 |     133 |          |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("PROD_ID"=30)

 

WHERE절과 OVER절의 ORDER BY에 만족하는 인덱스를 생성하였더니 SORT가 전혀 발생하지 않는다. 또한 OPERATIONWINDOW SORT에서 WINDOW NOSORT로 바뀌었다. 적절한 인덱스가 왜 필요한지 알 수 있는 대목이다. 참고로 OVER 절의 ORDER BY 구문은 PROD_ID , CUST_ID 로 바꾸어도 동일한 결과를 얻을 수 있다.

 

또 다른 경우를 보자. 분석함수를 사용하고, Full Table Scan을 사용하더라도 WINDOW NOSORT가 발생할 수 있다.

 

SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

       ROW_NUMBER() OVER(ORDER BY NULL) AS RN

  FROM SALES_T ;

 

---------------------------------------------------------------------------------

| Id  | Operation          | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |    918K|00:00:04.81 |    4898 |          |

|   1 |  WINDOW NOSORT   |         |    918K|00:00:04.81 |    4898 |          |

|   2 |   TABLE ACCESS FULL| SALES_T |    918K|00:00:01.99 |    4898 |          |

---------------------------------------------------------------------------------

 

FTS를 사용했지만 분석함수에 ORDER BY절이 없으므로 SORT는 발생하지 않는다. 무작위로 10건을 추출하는 경우에 사용할 수 있다. 물론 ROWNUM을 사용해도 동일한 효과를 낼 것이다.

 

4.WINDOW NOSORT STOPKEY

인덱스의 컬럼순서와 분석함수 OVER절에 존재하는 ORDER BY 컬럼순서가 동일하며, Ranking Family 함수를 인라인뷰 외부에서 filter 조건으로 사용할 때 발생한다.

 

SELECT *

  FROM ( SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER() OVER(ORDER BY PROD_ID) AS RN

           FROM SALES_T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10;

 

--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |     10 |00:00:00.01 |       6 |          |

|*  1 |  VIEW                         |         |     10 |00:00:00.01 |       6 |          |

|*  2 |   WINDOW NOSORT STOPKEY    |         |     10 |00:00:00.01 |       6 |          |

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T |     11 |00:00:00.01 |       6 |          |

|*  4 |     INDEX RANGE SCAN          | IX_PROD |     11 |00:00:00.01 |       4 |          |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( ORDER BY "PROD_ID")<=10)

   4 - access("PROD_ID"=30)

 

WINDOW NOSORT STOPKEY는 부분범위처리에 최적화되어 있다

분석함수를 Rownum처럼 사용하였기 때문에 WINDOW NOSORT STOPKEY가 발생하였다. 1건을 더 읽었지만 비효율은 느낄 수 없는 정도이다. WINDOW NOSORT STOPKEY와 이미 위에서 설명된 WINDOW SORT PUSHED RANK와의 차이는 단순히 SORT의 유무뿐만이 아니다. Block I/O량도 차이가 난다. WINDOW SORT PUSHED RANK는 전체 건을 읽은 후에 다 버리고 10건만 출력한다. 이에 반해 WINDOW NOSORT STOPKEY 11건을 읽고 한 건만 버리므로 부분범위처리의 효율이 좋다. 2번과 4번의 Block I/O량을 비교해보기 바란다. 몇 십 배나 차이가 난다..    

 

참고:  Ranking Family 분석함수란 RANK, DENSE_RANK, ROW_NUMBER를 말한다.

 

결론


WINDOW (SORT) :

-FTS를 사용하거나 인덱스가 적절하지 않은 경우 발생 

WINDOW (SORT PUSHED RANK) :

-위와 같으나 Ranking Family를 사용해야 하며, 인라인뷰 외부에서 분석함수를 filter로 사용하는 경우 발생

WINDOW (NOSORT) :

-인덱스가 적절한 경우나, OVER절에 ORDER BY가 없는 Ranking Family를 사용한 경우 발생

WINDOW (NOSORT STOPKEY) :

-위와 같으나 Ranking Family를 사용해야 하며, 인라인뷰 외부에서 분석함수를 filter로 사용하는 경우 발생

 

실행계획만 보고도 SQL을 어느 정도 짐작 할 수 있다라고 주장하는 튜닝의 고수가 있다. 필자는 때에 따라 그럴 수도, 아닐 수도 있다고 생각한다. 하지만 최소한 분석함수에 관해서는 어느 정도 가능하다고 생각한다. 분석함수의 내부를 이해했다면 충분히 그럴 수 있다. 이제 위의 결론을 이용하여, 실행계획만 보고도 어떤 분석함수를 어떤 상황에서 사용한 것인지 짐작 할 수 있겠는가? 대충 짐작 할 수 있다면 성공한 것이다.

 

다음 글(2 )에서 소개될 내용

WINDOW (BUFFER)

WINDOW (BUFFER PUSHED RANK)

WINDOW (CHILD PUSHED RANK)

WINDOW (IN SQL MODEL) SORT

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. salvationism 2011.02.07 10:22 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 보고 갑니다. 새해 복 많이 받으세요 ^^

  2. 혈기린 2011.02.07 15:42 신고  댓글주소  수정/삭제  댓글쓰기

    그 개발자한테 블로그 독자들이 술한잔 사야 할거 같네요 ㅋㅋ
    책도 다시 집필하신다니 기대 됩니다 ㅎㅎ

  3. 라튜니 2011.02.07 17:37 신고  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 포스팅 감사합니다. 벌써부터 2편이 기대되네요~
    새해 복 많이 받으세요~

  4. Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.08 08:58 신고  댓글주소  수정/삭제  댓글쓰기

    모두들 새해 복많이 받으시고, 소원성취하세요.

  5. Ejql 2011.02.09 00:53 신고  댓글주소  수정/삭제  댓글쓰기

    정말 유용한 글입니다. 감사합니다.

  6. greenluck 2011.02.09 15:56 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 보면서 많은 것을 배우고 있어 항상 감사드립니다. ^^

    그런데 "3. WINDOW NOSORT"부분에서 적절한 인덱스를 생성한 후에 WINDOW NOSORT이 발생하는 부분을 설명해 주셨는데 WHERE조건절이 equal이 아닌 range로 하게 되면 결합인덱스인 경우에 OVER절에는 맨 처음 칼럼이 있는 경우에만 WINDOW NOSORT로 되는 것 같습니다.

    create table test_1
    as
    select lpad(level, 2, '0') rn1
    , lpad(mod(level, round(dbms_random.value(1,3))), 2, '0') rn2
    from dual
    connect by level <= 10
    ;
    create index ix_test_1 on test_1 (rn1, rn2)
    ;

    select rn1
    , rn2
    , rank() over (order by rn2, rn1) t_rank
    from test_1
    where rn1 = '00'
    ;

    Execution Plan

    --------------------------------------------------------------------------------
    0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=8)
    1 0 WINDOW (NOSORT) (Cost=2 Card=1 Bytes=8)
    2 1 INDEX (RANGE SCAN) OF 'IX_TEST_1' (INDEX) (Cost=1 Card=1 Bytes=8)

    select rn1
    , rn2
    , rank() over (order by rn2, rn1) t_rank
    from test_1
    where rn1 > '00'
    ;

    Execution Plan

    --------------------------------------------------------------------------------
    0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=4 Bytes=32)
    1 0 WINDOW (SORT) (Cost=2 Card=4 Bytes=32)
    2 1 INDEX (RANGE SCAN) OF 'IX_TEST_1' (INDEX) (Cost=1 Card=4 Bytes=32)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.09 17:44 신고  댓글주소  수정/삭제

      greenluck님 반갑습니다.

      첫번째 SQL의 경우 인덱스의 컬럼순서와 over절의 order by 컬럼순서가 일치하지 않음에도 NOSORT 가 나옵니다. 그 이유는 order by rn2, rn1 라고 정의했더라도 ,실제로는 order by rn2 로 한것과 같기 때문입니다. 즉 where 조건때문에 rn1을 sort할 필요가 없는 것이죠. 그래서 nosort가 나온것 입니다.

      두번째 SQL에서 sort가 발생한 이유는 인덱스의 컬럼순서와 order by절의 컬럼순서가 맞지 않기 때문입니다. order by rn1, rn2 로 순서를 맞춰주시면 nosort로 바뀔 것 입니다.

      감사합니다.

  7. 야함나르 2011.02.09 16:24 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 보고 갑니다 ^^....

    그런데 nosort stopkey 가 오라클 버전 언제부터 지원하는지 궁금하네요.

    저 같은경우 9.2.0.7 이라서 그러는지 그냥 nosort로 풀려버리네요. 흠..

    index 는 parent_prd_no, prd_no 로 생성했고
    아래 쿼리에서

    SELECT PRD_NO, parent_prd_no
    FROM (
    select
    PRD_NO, parent_prd_no, prd_nm
    --, sum(prd_no) over ()
    , row_number() over (order by parent_prd_no, prd_no) rn
    From temp_1 a
    where parent_prd_no = 111111
    )
    where rn <= 5


    그냥
    WINDOW (NOSORT)
    로 풀리는군요 ..

  8. Favicon of http://www.topreplicawatchesstore.com/ BlogIcon replica dolce gabbana 2011.09.20 12:42 신고  댓글주소  수정/삭제  댓글쓰기

    제 기억으로 Oracle 10.1에서도 nosort stopkey 가 안되었던 것으로 기억합니다. 즉 10.2부터 가능합니다.

    감사합니다.

  9. Favicon of http://www.hotefashion.com/louis-vuitton-c-18.html BlogIcon louis vuitton shoulder bags 2011.09.20 12:42 신고  댓글주소  수정/삭제  댓글쓰기

    그냥
    WINDOW (NOSORT)
    로 풀리는군요 ..