메뉴 건너뛰기

ㅇr름ㄷr운총각's 끄적끄적

DB
2015.06.02 10:53

SQL 순위구하기 팁

조회 수 1520 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

. T-SQL 순위를 구하는 문제의 해결책

가장 쉬우면서도 가장 평범한 문제를 다루고자 한다간단하게 생각하면 이 순위 패턴은 오라클 에서는 필요하지 않다라고 생각 할 수 있다왜냐면 오라클에서는 ROWNUM을 제공하므로 데이터를 ORDER BY해주고 ROWNUM만 적어 주면순위를 매겨 번호를 붙여 줄 수 있다하지만순위가 동률인 데이터에 대해서 어떻게 처리할 것인가이런 문제에 대해서 순위 패턴을 적용할 수 있을 것이다. SQL Server T-SQL은 어떠한가? ROWNUM같은 기능의 함수는 존재하지 않는다그러므로 이 순위 패턴을 절대적으로 피할 수 없을 것이다.(순위에 대한 문제를 클라이언트에서 처리하지 않는다면)

예전에 T-SQL을 공부하기 시작했을 때는 왜 ROWNUM 같은 것이 없을까 하고찾아 헤맨 적이 있었다하지만, SQL에 대해 알면 알아 갈수록 ROWNUM이 없어도 모든 처리가 가능하다는 것을 알 수 있었다.

SQL을 하면서 항상 느끼는 것은 연구하고 생각할수록 더 좋은 문장을 만들 수 있다는 것이다이 데이터베이스의SQL은 기능상으로 어떤 함수가 제공되지 않는다고 못한다는 것은 연구하고 생각하고자 하는 의지가 부족한 것이다그런 개발자들은생각부터 고쳐야 할 것이다.

가장 간단한 데이터를 가지고 생각을 해보도록 하자.

우리가 만들 테이블은 점수 테이블이다간단하게 학번과 점수로만 구성되어 있다.

이렇게 간단한 데이터를 가지고 시작하는 것은 우리가 알아야 할 순위를 구하는 원리에 대해 집중하기 위한 것이다.

학번과 점수로만 구성이 된 간단한 테이블이므로 ERD는 생략하도록 하겠다.

<SQL 1-1>

CREATE TABLE SCORE    

(       StudentID INT IDENTITY PRIMARY KEY

        ,Score NUMERIC(3,0)

)

go

테이블을 생성했으면 샘플 데이터를 입력하도록 하자.

<SQL 1-2>

--점수 입력

INSERT INTO    SCORE   (Score) VALUES  (90)

INSERT INTO    SCORE   (Score) VALUES  (80)

INSERT INTO    SCORE   (Score) VALUES  (80)

INSERT INTO    SCORE   (Score) VALUES  (85)

INSERT INTO    SCORE   (Score) VALUES  (95)

INSERT INTO    SCORE   (Score) VALUES  (70)

go

샘플 데이터 입력 후에 점수(Score) 순으로 데이터를 관찰 해보도록 하자. “갑자기 무슨 관찰인가” 란 생각이 들 수 있겠지만 데이터를 관찰 하는 것은 매우 중요하다데이터를 관찰하고 자신이 원하는 결과가 무엇인지 상상하다 보면 풀 수 있는 해법을 찾을 수 있기 때문이다.

<SQL 1-3>

--순위대로 데이터 보기

SELECT  *

FROM    SCORE

ORDER By Score DESC

위의 SQL을 통해서 다음과 같은 결과를 얻을 수 있다.

StudentID

Score

Ranking

5

95

1

1

90

2

4

85

3

3

80

4 or 5

2

80

4 or 5

6

70

6

이 결과에서 점선으로 표시된 Ranking 부분은 아직 얻어내지 않은 결과이다우리가 원하는 것은 위의 결과처럼 점선의 Ranking까지 같이 표시하는 결과이다.

이 결과를 얻기 위해 먼저 정해야 하는 것은 같은 점수를 가진 학생에 대해서 어떻게 등수를 부여할 것이냐의 문제이다여기서는 일단동률 점수를 가진 학생에 대해 같은 낮은 순위의 등수를 부여하기로 하겠다그러므로StudentID 3번과 2번은 같은 4등을 가지게 할 것이다.

이 순위 문제를 해결하기 위해서 SQL문을 만들기 전에종이와 펜을 들고또는 머릿속으로 생각해 보도록 하자.생각으로 SQL문을 만들어 보자는 것이 아니라, StudentID 5번은 1등이고, 1번은 2등이 되는지 생각해 보자는 것이다. 5번은 점수가 가장 높으니까, 1등이고, 1번은 2번째로 점수가 좋으니 2등이다그렇다면 이 당연한 것을StudentID Score만 가지고 어떻게 만들어 낼 수 있는가를 깊이 생각해 보자.

이것은 당연하고 쉬운 문제이다. “5번 학생의 점수 95점보다 높은 점수를 가진 학생이 몇 명일까?”란 질문으로 이 해답은 풀어지게 된다. 5번 학생 점수보다 높은 학생은 아무도 없다그러므로 1등이다. 1번 학생의 점수보다 높은 학생은 몇 명인가? 5번 학생 한 명이다그러므로 1번 학생은 2등이다. 4번 학생보다 점수가 좋은 학생은 5번과 1번 학생 두 명이다그러므로 4번은 3등이다이렇게자기 자신의 점수보다 점수가 좋은 학생의 수를 센다면쉽게 순위를 구할 수 있다.

그럼생각한 대로 SQL문을 만들어 보도록 하자여기서는 이해가 쉽도록 SELECT절의 서브쿼리를 사용할 것이다간혹개발자들의 특성에 따라, SELECT절에 서브쿼리가 오는 것은 최악이라고 생각하는 경우가 있다하지만,필자는 꼭 그렇다라고 생각하지는 않는다. SQL문의 가독성 적인 측면을 생각한다면, SELECT절의 서브쿼리가 훨씬 유용할 때가 많기 때문이다.

우리가 생각한 논리는 다음과 같이 SQL문으로 표현 할 수 있다.

<SQL 1-4>

--순위 구하기

SELECT  StudentID

        ,Score

        ,(      SELECT  COUNT(*)

               FROM    SCORE T2

               WHERE   T2.Score > T1.Score) Ranking

FROM    SCORE T1

ORDER BY Ranking

위의 SQL문에서 실제 순위를 구하는 곳은 SELECT절의 서브쿼리란 것을 쉽게 알 수 있을 것이다. FROM절의 SCORE테이블(T1) Score보다 큰 Score를 가진 학생들의 COUNT를 구해서 Ranking으로 표현하는 것이다.

실행 결과원하지 않는 결과가 나온 것을 알 수 있다.

StudentID

Score

Ranking

5

95

0

1

90

1

4

85

2

3

80

3

2

80

3

6

70

5

실제 1등으로 표현되어야 할 StudentID 5번은 Rangking 0으로 표시되고실제, 2등인 1번의 Ranking 1로 표시된 것을 알 수 있다이것을 해결할 수 있는 방법은 무엇인가아주 쉬울 것이다결과에 1씩만 더해 주면 될 것이다다음과 같은 SQL이 될 것이다.

<SQL 1-5>

SELECT  StudentID

        ,Score

        ,(      SELECT  COUNT(*)

               FROM    SCORE T2

               WHERE   T2.Score > T1.Score) + 1 Ranking

FROM    SCORE T1

ORDER BY Ranking

원하는 결과가 나온 것을 알 수 있다.

이런 순위의 문제에 대해서 항상고려해야 하는 것은 동률을 이룬 데이터에 대한 처리이다.

동률의 데이터에 대해 동일하게 낮은 등수를 적용하는 경우도 있을 것이다. 3번과 2 StudentID에 대해 같은 4등을 적용하는 것이 아닌같은 5등을 적용하는 것이다이 경우는 어떻게 하면 되겠는가?

0 1로 바꾸기 위해서 서브쿼리의 결과에 + 1 을 한 것과 같이 + 2를 해주면 되는 것인가아니다. + 2를 해주면, 1등은 2등이 되고, 2등은 3등이 될 것이다단지, StudentID, 3번과 2번만이 우리가 원하는 5등이 될 것이다.같은 낮은 등수를 적요하기 위해서는 서브쿼리에 + 1을 하는 것이 아닌서브쿼리의 T2.Score > T1.Score 부분을>= 조건으로 변경하면 된다자신보다 높은 점수를 가진 사람을 세는 것이 아니라자신보다 높거나 같은 점수를 가진 사람들을 세게 된다면동률 데이터에 대해서는 저절로 동일한 낮은 순위가 매겨지게 될 것이다.

<SQL 1-6>

SELECT  StudentID

        ,Score

        ,(      SELECT  COUNT(*)

               FROM    SCORE T2

               WHERE   T2.Score >= T1.Score) Ranking

FROM    SCORE T1

ORDER BY Ranking

만족스러운 결과를 얻을 수 있을 것이다.

우리는 비록 원하는 결과를 일차적으로 얻었지만또 다른 방법이 없는지 연구 해봐야 한다해당 시스템에서 가장 최적이고 좋은 방법을 생각해 내야 하는 것이다.

이런 순위를 구하는 가장 간단한 방법은 클라이언트에서 순위를 비교해서 뿌려 주는 것이다. SQL로는 ORDER BY만 해주고데이터를 화면에 출력할 때기존에 출력했던 데이터보다점수가 높으면 1증가시키고아닌 경우는 순위를 이전과 동일하게 유지하면서 출력해주는 방법이다가장 쉬운 방법이면서도가장 좋은 성능을 낼 것이라 생각 된다문제는다양하게 변하는 사용자의 요구사항을 적용하기 위해서 클라이언트 프로그램을 변경하는 일은 만만한 작업이 아니라는 것이다이미 운영되고 있는 시스템이라면배포에 대한 문제도 있을 수 있으면어떤 코딩을 건드렸을 때다른 코딩에 영향을 주지 않는지를 알아내기란 쉽지 않은 문제이다.

모든 것에는 장 단점이 있는 것이므로 적절하게 사용을 하면 될 것이다하지만우리는 좀더 SQL쪽에 접근해서 문제를 푸는 방법을 생각하도록 하자.

순위를 구할 수 있는 또 다른 SQL문에는 어떤 것이 있을까서브쿼리를 사용한 것은 가장 쉬운 방법이라고 필자는 생각한다서브쿼리를 사용하지 않고 해결하는 방법에는 셀프 조인(Self Join)이 있다.

자기 자신과 테이블을 조인하는 방법이다사실순위를 구하기 위해 사용한 서브쿼리 역시 셀프 조인이다자기 자신의 테이블을 관련되어서 순위를 찾아내니까 말이다하지만문법적으로 셀프 조인을 작성 할 때는 FROM절에 두 개의 같은 테이블이 모두 있어야 한다.

순위를 구하기 위한 셀프조인 SQL문은 다음과 같다.

<SQL 1-7>

SELECT  T1.StudentID

        ,MIN(T1.Score) Score

        ,COUNT(*) Ranking

FROM    SCORE T1 JOIN SCORE T2

        ON T1.Score <= T2.Score

GROUP BY T1.StudentID

ORDER BY Ranking

확실히 셀프 조인은 서브쿼리보다 읽기가 쉽지는 않을 것이다하지만이 어려워 보이는 문장도 하나씩 각개격파를 해보면 별 것 아니다그리고이 문장을 이해하는 것은 SQL JOIN이 얼마나 강력한지 이해하게 되는 것이다.위의 결과에 대해 결과는 다음과 같다.

StudentID

Score

Ranking

5

95

1

1

90

2

4

85

3

3

80

5

2

80

5

6

70

6

실행 결과를 보면, StudentID 3번과 2번이 4등이 아닌, 5등으로 나타난 것을 알 수 있다이것을 4등으로 표현하기 위한 문제는 좀 더 후에 생각하도록 하고지금은이 결과가 어떻게 해서 나왔는지 생각을 해보도록 하자약간씩 복잡한 SQL문을 만들거나복잡한 SQL을 파악할 때는 하나씩 작은 단위로 나누어서 SQL을 파악하는 것이다.이 정도 SQL가지고 복잡하다고 말 할 수 없음을 많은 개발자들은 알고 있을 것이다하지만이 짧은 SQL을 파악하는 과정을 통해 더 복잡한 문장도 파악할 수 있는 능력이 생길 것이다.

먼저 순수하게 조인만 수행하고 결과를 살펴보도록 하자.

<SQL 1-8>

SELECT  T1.StudentID   ,T1.Score

        ,T2.StudentID  ,T2.Score

FROM    SCORE T1 JOIN SCORE T2

        ON T1.Score <= T2.Score

ORDER BY T1.Score DESC

결과를 보면 다음과 같다.

T1.StudentID

T1.Score

T2.StudentID

T2.Score

5

95

5

95

1

90

1

90

1

90

5

95

4

85

1

90

4

85

4

85

4

85

5

95

3

80

1

90

3

80

2

80

이 결과에서 눈여겨 볼 것은 같은 T1.StudentID를 가진 데이터가 몇 개씩 있느냐 이다.

거듭 강조하지만, T2로 나온 결과는 신경쓰지 말도록 하자. T1.StudentID 5번인 데이터는 한 건이다. 1번인 데이터는 2, 4번인 데이터는 3, 3번과 2번은 각각 5건이다 T1.StudentID별로 데이터 건수가 등수로 연결되는 것은 너무도 쉽게 알 수 있을 것이다그럼왜 이렇게 데이터가 나올 수 있을까를 고민해 보도록 하자이것에 대한 답은 조인을 통해 변하는 데이터의 건수이다. SQL문을 보면 조인 조건이 T1.Score = T2.Score가 아닌, T1.Score <= T2.Score인 것을 알 수 있다이 것이 어떻게 조인이 이루어 지는가를 알기 위해서 간단한 테이블이 있다고 생각하고 하나씩 짚어 보도록 하자.

no란 컬럼을 가진 A테이블과 B테이블이 있다고 생각해 보자.

두 테이블에서 같은 no를 가진 데이터만 연결(JOIN)해서 조인 결과(A.no = B.no)를 만들어 보자.

A.no

B.no

A.no = B.no

1

1

1

1

2

2

2

2

4

2

2

2

 

3

 

 

조인의 결과는 총 3 건이 된다그림을 보고 A테이블부터 첫 번째 데이터부터 시작을 해보도록 하자.

A테이블의 첫 번째 데이터는 no 1이다 1이란 숫자를 가지고 B테이블로 가져가 보도록 하자.

B테이블에서 지금 가져온 1이란 값과 일치되는 데이터는 B테이블의 가장 첫 데이터이다그러므로 이것은 1건의 조인 결과를 만들어 내게 된다.

이번에는 A테이블의 두 번째 데이터인 2번을 가지고 B테이블에서 찾아보도록 하자. B테이블은 2번을 두 건 가지고 있다그러므로 결과는 조인 결과는 2건이다.

마지막으로 A테이블의 4번 데이터를 B테이블에서 찾아보자알다시피 한 건도 없으므로 조인 결과에는 참여하지 않게 된다이것이같다 조건을 사용한 조인이 된다여기서 정확히 알고 있어야 하는 것은, A테이블과 B테이블의 연결 순서에 상관없이 결과는 동일하다는 것이다위와는 반대로 B테이블로 시작해서 A테이블을 거쳐서 조인을 해보도록 하자.

B.no

A.no

B.no = A.no

1

1

1

1

2

2

2

2

2

4

2

2

3

 

 

 

B테이블의 처음 데이터는 no 1이다이 데이터를 가지고 A테이블을 뒤져 보면, no 1인 데이터는 1건이다그러므로 결과에는 1건이 나오게 된다. B테이블의 두 번째 데이터는 no 2이다이 데이터와 동일한, no를 가진 데이터를 A테이블에서 찾아보면 1건이다그러므로 B.no 2이고, A.no 2인 데이터가 결과에 한 건 만들어 진다. B테이블의 세 번째 데이터의 no도 역시 B테이블의 두 번째 데이터와 마찬가지로 2이다이 데이터를 가지고 다시A테이블에서 찾아보면이전에 B테이블의 두 번째 데이터와 짝을 맞춘 no 2인 데이터가 한 건 있다이 데이터와 조인이 되어서 역시 결과로 한 건이 보내 진다마지막으로 B테이블의 no 3인 데이터는 A테이블에서 찾아볼 수 없으므로 결과에 참여하지 못한다.

이와 같이조인의 순서는 결과 내용에는 영향을 미치지 않는다하지만이러한 조인의 순서는 때로는 성능에 영향을 미치게 되며내부 조인(일반적인 조인)이 아닌외부조인을 사용할 경우에는 조인의 순서가 조인결과에 영향을 미치기도 한다하지만내부 조인에서는 절대적으로 조인의 순서에 따라 결과가 틀리게 나오는 경우는 없다.

그러면 이번에는 같다 조건이 아닌 크기 비교 조건으로 조인을 수행해 보도록 하자.

대부분의 개발자들은 같다 조건 이외의 조건으로 조인을 해본 적이 거의 없을 것이다실제로이런 조건을 잘못 사용하게 되면, SQL의 실행 성능에 악영향을 끼치기도 하며실제로 사용되는 일이 드물기 때문이다하지만원리를 알고 정확히 사용한다면아주 유용하게 사용할 수 있을 것이다.

A테이블과 B테이블을 A.no >= B.no 의조건으로 조인을 해보도록 하자.

총 몇 건의 결과가 나올 거 같은가아마머리 좋은 개발자들은 이 정도의 데이터 건수라면쉽게 암산 할 수도 있을 것이다하지만데이터가 많아 지면 이를 암산하기는 쉽지 않다그림을 통해서 알아보도록 하자.

A.no

B.no

A.no >= B.no

1

1

1

1

2

2

2

1

4

2

2

2

 

3

2

2

 

4

1

4

2

4

2

4

3

같다 조건으로 결합한 경우보다 좀 더 보기 복잡해 졌을 것이다그래도 인내를 갖고 하나씩 생각하면서 보도록 하자결합(조인조건은 A no B no보다 크거나 같은 경우이다.

A의 첫 번째 데이터는 no 1이다그럼 B테이블에서 1보다 작거나 같은 no를 가진 데이터는 첫 번째 데이터 한 건이다그러므로 먼저 한 건이 결과로 만들어 진다.

A의 두 번째 데이터는 no 2이다. B테이블에서 2보다 작거나 같은 no를 가진 데이터는 무엇이 있는지 찾아보자. B 테이블의 첫 번째 데이터두 번째세 번째 데이터가 각각, 1, 2, 2의 값을 가지고 있으므로 A테이블의 두 번째 데이터와 결합 할 수 있다그러므로 3건이 결과 집합에 만들어 지게 된다.

마지막으로 A의 세 번째 데이터는 4에 대해서 살펴보도록 하자. B테이블에서 4보다 작거나 같은 no를 가진 데이터는 B테이블의 전부이다 4건이므로 총 4개의 결과가 추가 된다그 결과 8건의 결과가 만들어 진 것을 볼 수 있다반대로 B테이블을 시작점으로 해서 조인을 해보아도 = 조건의 조인과 결과는 동일하다.

우리는 이 A.no >= B.no 같은 조건을 순위를 구하는 SQL문에 응용했던 것이다.

그럼실제 순위를 구했던 SQL을 통해 크기비교 조건으로 조인이 어떻게 되는지 살펴보자.

아마도관계형 DB를 할 때는 집합개념이 중요하다는 말을 많이 들었을 것이다필자는 수학이고 산수고 모두 자신 없다.(실제로 구구단을 외자 게임을 하게 된다면세번 답변 하다가 지고 말 것이다.) 그러므로 집합에 대한 얘기는 하지 않겠다집합을 몰라도 관찰력과 상상력을 가지고 있다면 SQL을 능숙히 만들어 낼 수 있을 것이다이런관찰력이 우리에게는 필요하다는 것을 상기하고 SQL문과 결과를 관찰해 보도록 하자.

먼저 SQL문장에서는 셀프 조인(SELF JOIN)을 사용했다셀프 조인이란 것은 특별히 어려운 것이 없다. FROM절에 같은 테이블이 두 개 이상 온다면 셀프 조인인 것이다우리는 이전의 크기비교 조인을 살펴보기 위해서 간단한 A테이블과 B테이블을 사용했지만실제 순위를 구하는 SQL은 크기비교 조인을 자기 자신과 수행하게 된다. <SQL 1-8>을 수행해서 결과를 살펴보자어떤 특징이 있는가위에서도 설명했듯이이 결과에서는, T1.StudentID 5번인 데이터는 한 건, 1번인 데이터는 2, 4번인 데이터는 3, 3번과 2번은 각각 5건라는 것이다이러한 사항을 좀 더 쉽게 관찰 할 수 있는 방법은 무엇일 까그것은 바로 하나씩 실행을 해보는 것이다실제 개발 시에도 하나의 데이터에 대해 관찰해서 얻은 결론으로 전체 데이터에 적요하는 SQL문을 만들면 생각보다 쉽게 SQL을 만들 수 있다.

다음과 같은 SQL을 실행해 보자.

<SQL 1-9>

SELECT  T1.StudentID   ,T1.Score

        ,T2.StudentID  ,T2.Score

FROM    SCORE T1 JOIN SCORE T2

        ON T1.Score <= T2.Score

WHERE   T1.StudentID = 1

T1테이블의 StudentID 1번인 학생 보다 높거나 같은 점수를 가진 데이터를 T2에서 찾는 것이다결과는 총 두 건이 나온 것을 알 수 있다그러므로 이 1번 학생은 2등이다. WHERE조건의 StudentID를 하나씩 바꿔가면서 실행해 보도록 하자해당 번호의 학생이 몇 등인지를 쉽게 알 수 있을 것이다.

<SQL 1-8>의 결과를 T1.StudentID별로 집계를 해서카운트를 센다면등수가 나올 것이다그리고, ScoreT1.StudentID에 대해 종속적(T1.Score T1.StudentID에 대해 결정되어 진다.)이므로 MAX, MIN을 사용해서Score를 표시하도록 하자그래서 나온 결과가 <SQL 1-7>이다. GROUP BY, COUNT, MAX, MIN등에 대해서는 설명하지 않아도 모두 알고 있으리라 생각하고 생략하도록 하겠다.

이번에는셀프 조인을 사용해서 동률인 데이터에 대해서 낮은 등수를 출력할 수 있도록 구성해 보자기존의<SQL 1-7>의 결과는 다음과 같았다.

StudentID

Score

Ranking

5

95

1

1

90

2

4

85

3

3

80

5

2

80

5

6

70

6

이 결과에서 3번과, 2 StudentID 5등이 아닌, 4등이 되어야 하는 것이다.

스스로 한번시도를 해보기 바란다아마도다양한 방법이 있을 것이다하지만결코 만만한 문제는 아닐 것이다.

먼저단순하게, Ranking -1을 하게 되면 어떻게 되겠는가이것은, 3번과, 2번 학생에 대해서는 우리가 원하는4를 돌려주겠지만나머지데이터들의 등수가 모두 내려가게 될 것이다.

이것 역시 관찰과 상상력을 통해서 결과를 얻을 수 있다.

먼저, 3등과, 2등이, 4등이 되려면 어떻게 되어야 하는가기존의 서브쿼리로 순위를 구했던 <SQL 1-5>를 참고 할 수 있다. <SQL 1-5>의 서브 쿼리에서 조건은 크거나 같은 데이터가 아닌외부 테이블보다 큰 점수를 가진 데이터만 찾았다그러므로 다음과 같은 조건의 셀프 조인이 될 수 있다.

<SQL 1-10>

SELECT  T1.StudentID

        ,MIN(T1.Score) Score

        ,COUNT(*) Ranking

FROM    SCORE T1 JOIN SCORE T2

        ON T1.Score < T2.Score

GROUP BY T1.StudentID

ORDER BY Ranking

결과는 다음과 같다.

StudentID

Score

Ranking

1

90

1

4

85

2

3

80

3

2

80

3

6

70

4

제대로 된 결과 같은가자세히 보도록 하자데이터가 한 건 사라진 것을 알 수 있다.

이것이 바로 내부 조인의 핵심이다조인 조건에 참인 데이터만 내보낸다는 것이다.

거듭 강조하는 것은 조인은 같은 데이터를 연결 하는 것이 아닌조인 조건을 만족하는 데이터를 연결하는 것이다그러므로 한 건의 데이터가 사라진 것이다어떤 데이터가 사라졌는가? T1.StudentID 5번인 1등 데이터가 사라진 것이다그럼왜 사라졌는가조인 조건을 만족하는 데이터는 연결이 되어서 결과에 나타나지만조인 조건을 만족하지 못하는 데이터는 연결되지 못하기 때문이다. 5번인 데이터의 Score 95이다우리는 T1.Score < T2.Score조건으로 조인을 했다그러므로 T2에서, 95점 보다 큰 데이터는 없으므로 사라진 것이다같다 조인을 사용하든크기 비교 조인을 사용하든이것은 조심해야 할 사항이다기존의 <= 조건에서 5번 데이터는 사라지지 않았다왜냐면, T2에는 같은 95점을 가지고 있는 T2.StudentID 5번인 데이터가 있기 때문이다.

그렇다면 어떻게 해야 하는가여기서 조건을 반대로 준다거나크거나 같다조건 등으로 바꿔 보는 것은 아주 좋은 시도다조건을 바꿔서 결과가 어떻게 나왔는지 살펴보고 왜 그렇게 되었는가를 고민해 본다면정답을 찾든 안 찾든큰 발전이 있을 것이다스스로 많은 방법을 연구 해보고 다음을 보도록 하자.

필자가 생각하는 방법은 외부조인을 사용하는 것이다외부조인의 특징이 무엇인지 확실히 알고 있어야 한다외부 조인은어느 한쪽의 기준 테이블의 모든 데이터를 조인 조건이 참이 아니더라도 결과에 내보내는 것이다, WHERE조건을 통해 걸러 진 데이터는 역시 결과에 참여할 수 없는 것이다.

LEFT OUTER JOIN을 했다면, JOIN문장의 왼편에 있는 테이블이 기준 테이블이 되어서 내부적으로 먼저 접근 되어지게 되며왼편에 있는 데이터는 모두 결과에 나오게 된다왼편에 테이블과 결합되는 데이터가 오른편의 테이블에 존재하지 않는다면오른쪽의 데이터는 모두 NULL이 되어서 결합되어 진다.

이전의 A테이블과 B테이블에 대해서 A LEFT OUTER JOIN B ON A.no = B.no 에 대해서 수행해 보도록 하자.

A.no

B.no

A.no = B.no

1

1

1

1

2

2

2

2

4

2

2

2

 

3

4

NULL

이전의 내부 조인처럼 조인 조건을 만족하는 데이터를 결과에 내보내 준다하지만, A테이블의 마지막 데이터만은 다른 데이터와 틀리다. A.no 4인 데이터는 B테이블에서 전혀 찾을 수 없다그러므로, A.no 4인 데이터는 B테이블에 해당하는 값들 대신에 NULL값을 가지고결과로 내보내 진다그러므로 결과에는 A테이블의 모든 데이터들이 포함되어 있다.

B테이블을 기준으로 LEFT OUTER JOIN을 한다면 어떻게 될 것인가조인 조건은 B LEFT OUTER JOIN A ON B.no = A.no이다결과는 B테이블에 있는 모든 데이터가 나오게 된다하지만, A테이블의 A.no 4인 데이터는 결과에 나오지 않는다이처럼 외부 조인에서는 조인의 순서가 있게 된다그러므로 어떤 테이블의 데이터가 기준이 되어서 모두 나와야 하는지를 유심히 생각을 해서 정해야 하며이런 순서는 성능에 영향을 미치게 되므로 특히 조심을 해야 한다계속해서 강조하게 되는 것은 조인은 같다란 조건을 만족하는 데이터를 결과로 내보내는 것이 아니라,조인 조건이 참인 데이터를 내보내게 된다그러므로 우리는 외부조인이라 해도 크기 비교의 조건을 사용할 수 있는 것이다.

우리는 순위를 구하기 위해 어느 한쪽의 테이블의 데이터가 모두 나와야 하는지 결정해야 한다.

조건은 T1.Score < T2.Score 이다. T2테이블은 순위를 카운트하기 위해 참여하는 테이블이다그러므로 T1테이블의 데이터들이 모두 나와야 한다.

그러므로 다음처럼 외부 조인을 구사해야 할 것이다.

<SQL 1-11>

SELECT  T1.StudentID

        ,MIN(T1.Score) Score

        ,COUNT(*) Ranking

FROM    SCORE T1 LEFT OUTER JOIN SCORE T2

        ON T1.Score < T2.Score

GROUP BY T1.StudentID

ORDER BY Ranking

결과를 살펴보도록 하자결과를 보면, StudentID 1번인 데이터와 5번인 데이터가 동일하게 1등을 가지고 있는 것을 볼 수 있다무엇이 잘 못되었는지 알겠는가그것은 바로 COUNT이다.

COUNT의 특성을 제대로 알고 있다면쉽게 고칠 수 있을 것이다. COUNT(*)를 하는 경우에는 해당 레코드(모든 컬럼들이 포함된 한 줄의 데이터)의 모든 컬럼 값이 NULL을 가지고 있어도 카운트에 추가시킨다.

하지만, COUNT(컬럼)을 사용해서 특정 컬럼에 대해 카운트를 하게 되면, NULL값은 카운트에서 제외시키게 된다.다음의 간단한 두개의 SQL을 통해 정확히 알 수 있을 것이다.

<SQL 1-12>

SELECT  COUNT(*)

FROM    (SELECT cast(NULL as int) a,   cast(NULL as int) b) T1

go

SELECT  COUNT(a)

FROM    (SELECT cast(NULL as int) a,   cast(NULL as int) b) T1

Go

두 개의 결과가 틀리다는 것을 알 수 있다첫 번째 SQL문은 1을 두 번째 SQL 0을 결과값으로 보여준다.

두 번째 SQL문은 특정 컬럼에 대해 카운트를 세었기 때문에 NULL값은 제외되었기 때문이다.

그러므로 우리는 제대로 된 등수를 구하기 위해서 COUNT를 변경해야 한다. <SQL 1-11>에서, T1.StudentID 5인 데이터와 결합될 T2의 데이터는 없다그리고, T1.StudentID 1인 데이터는 T2 StudentID 5인 데이터와 결합된다.(1번은 90, 5번은 95점이므로그러므로, COUNT(T2.StudentID)를 한 후에 COUNT +1만 해주게 되면 원하는 결과가 정확히 나오게 된다.

<SQL 1-13>

SELECT  T1.StudentID

        ,MIN(T1.Score) Score

        ,COUNT(T2.StudentID) +1 Ranking

FROM    SCORE T1 LEFT OUTER JOIN SCORE T2

        ON T1.Score < T2.Score

GROUP BY T1.StudentID

ORDER BY Ranking

여기까지 원하는 순위를 구하기 위한 SQL문을 모두 살펴 보았다.

우리는 서브쿼리와 셀프조인을 사용해서 순위를 각각 구해 보았다개발자들이 SQL문을 작성할 때 가장 중요하게 고민해야 하는 것은 바로 성능이다우리가 작업했던 SQL문들에 대한 성능을 측정해 보도록 하는 시간을 가져 보도록 하자본격적으로 성능을 살펴보기 전에 알아야 할 것은 SQL Server의 버전마다테스트 환경의 PC성능에 따라, SQL의 환경 설정에 따라 다른 실행 계획을 나타날 것이다하지만우리는 여기서 기본적으로 어떤 요소들이 성능에 영향을 주는지는 알 수 있을 것이다.

먼저서브쿼리와셀프 조인을 했을 경우 어떻게 성능의 차이가 있는지 알아보도록 하자.

먼저 <SQL 1-5> <SQL 1-13>을 비교해 보도록 하겠다 SQL문은 정확히 같은 결과를 돌려주도록 만들 어진 문장이다이 두 문장을 수행하기 전에 쿼리 분석기에서 Ctrl + K(또는 메뉴의 쿼리의 실행계획 표시를 선택한다.)를 눌러서 그림으로 실행계획을 표시하도록 설정해 놓고 수행해 보도록 하자우리가 수행할 두 문장이 같이 있는SQL <SQL 1-14>이다.

<SQL 1-14>

SELECT  StudentID

        ,Score

        ,(      SELECT  COUNT(*)

               FROM    SCORE T2

               WHERE   T2.Score > T1.Score) + 1 Ranking

FROM    SCORE T1

ORDER BY Ranking

go

SELECT  T1.StudentID

        ,MIN(T1.Score) Score

        ,COUNT(T2.StudentID) +1 Ranking

FROM    SCORE T1 LEFT OUTER JOIN SCORE T2

        ON T1.Score < T2.Score

GROUP BY T1.StudentID

ORDER BY Ranking

go

수행한 후 쿼리분석기의 왼쪽아래의 탭에서 실행계획을 클릭해서 실행계획을 확인 할 수 있다필자의 PC에서는 두 개의 SQL에 대해 거의 동일한 비용이 소모된 것으로 나타났다서브쿼리를 사용한 SQL 49.99%이고셀프조인을 사용한 SQL 50.01%이다이 것은 두 개의 비용이 별 반 차이 없음을 나타낸다그것은 곧둘 중에 어느 문장을 사용해도 좋다는 것을 나타낸다이 비용에 대한 % SQL 성능을 나타내는 절대적인 수치가 될 수 없음을 알도록 하자그럼에도 불구하고 실행계획의 그림에 표시되는 수치들은 큰 도움을 준다여기서는 두 문장이 수치적으로 어떤 차이가 있는지 알게 해준 것과 더불어 SQL에서 어떤 부분에서 가장 많은 비용이 소모되었는지 알 수 있는 것이다 SQL의 실행계획의 그림을 보고 어떤 부분이 가장 비용이 많이 소모되었는지 알아보자먼저,첫 번째 서브쿼리를 사용한 SQL의 경우에는 SCORE테이블의 PK인덱스를 스캔하는 부분이다이 인덱스 스캔이 두 번 일어난 것을 알 수 있다하나는 FROM절의 테이블에 대한 스캔이고또 하는서브쿼리에 있는 스캔이다두 번째 SQL 역시두 개의 SCORE 테이블의 PK 인덱스가 가장 많은 비용을 차지하고 있다그럼 우리가 튜닝할 것의 목표가 정해진 것이다 SCAN에 대한 비용을 줄이는 것이다.

이 중에는 줄일 수 있는 것과 없는 것이 있다어떤 것일까바로한 테이블에 대한 PK SCAN이다테이블의 모든 데이터에 대해 등수를 구하는 것이기 때문에어느 한 테이블에 대한 SCAN은 피할 수 없는 운명인 것이다.

설명을 좀더 편하게 하기 위해서다시 한번 Ctrl + K 를 눌러서 실행계획 그림표시를 제거하고 다음을 수행한다.

<SQL 1-15>

SET STATISTICS PROFILE ON

go

<SQL 1-15>를 수행 한 다음, <SQL 1-14>를 다시 수행해 보도록 하자우리는 실행 결과와 더불어 다음과 같은 실행 계획들을 얻을 수 있다먼저 <PLAN 1-1>은 서브쿼리에 대한 실행 계획이다.

<PLAN 1-1>

No Rows  Exec     StmtText

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

1  6     1        SELECT StudentID   ,Score   ,( SELECT COUNT(*)    FROM SCORE T2    WHERE T2.Score >

2  6     1          |--Sort(ORDER BY:([Expr1004] ASC))

3  6     1               |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))

4  6     1                    |--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[Score]))

5  6     1                         |--Clustered Index Scan(OBJECT:([SQL_PATTERN].[dbo].[SCORE].[PK__SCORE__79A81403] AS [T1]))

6  6     6                         |--Hash Match(Cache, HASH:([T1].[Score]), RESIDUAL:([T1].[Score]=[T1].[Score]))

7  5     5                              |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1007])))

8  5     5                                   |--Stream Aggregate(DEFINE:([Expr1007]=Count(*)))

9  11    5                                        |--Clustered Index Scan(OBJECT:([SQL_PATTERN].[dbo].[SCORE].[PK…] AS [T2]),

                                                      WHERE:([T2].[Score]>[T1].[Score]))

실행 계획을 보는 순서는처음 문장을 쫓아서 내려가면서분기점(두 개의 노드를 가지고 있는 계획)을 만나면 위의 노드부터 다시 쫓아 내려간다더 이상 내려갈 경우가 없을 때 해당 노드의 연산이 수행되고수행된 연산의 부모 노드의 연산이 시작된다기존의 분기되었던부모 노드일 경우다시 아래쪽 노드를 쫓아 내려가야 한다.이렇게 설명할 수 있는데말로는 설명이 만만하지가 않다.

일단필자가 실행계획에 번호(No)를 붙여 났기 때문에이 번호를 통해 순서를 파악해 보도록 하자.

먼저, 1번 실행 계획은 자식 노드 2번이 있기 때문에, 2번보다 1번이 먼저 수행된다. 2번도 자식 노드 3번을 가지고 있으므로 2번보다 3번이 먼저 수행되고, 3번도 자식 노드 4번이 있으므로 3번보다 4번이 먼저 수행된다그리고, 4번 노드는 두 개의 자식 노드 5번과 6번을 가지고 있다자식 노드중에 위에께 먼저 수행되므로, 5번이 6번보다 먼저 수행된다그런데, 5번은 자식 노드가 없다그러므로 5번 노드가 가장 먼저 수행된다다시, 6번 노드부터 쫓아가면, 6번 이후로 9번까지 하나 만의 자식 노드를 가지고 연결되어 있으므로이 부분에서는 9-8-7-6순으로 실행계획의 연산이 수행될 것이다종합해 보면실행계획이 실행 되는 순서는 이렇다. 5-9-8-7-6-4-3-2-1이 되는 것이다제일 처음 수행되는 5번 연산부터 알아보자. 5번 연산은 T1테이블에 대해 클러스터드 인덱스 스캔을 하고 있다이것은, T1테이블에 있는모든 데이터에 대해 순위를 구해야 하는 것이므로 피할 수 없는 운명이다그렇다면, 5번 연산은 무시하고 넘어가도록 하자. 5번 다음의 연산인 9번 연산을 살펴 보도록 하자. 9번 연산에서는 T2.Score T1.Score보다 큰 데이터를 찾고 있다실제적으로 순위를 구하기 위해 대상 데이터를 찾는 과정이다그런데, 9번 연산도클러스터드 인덱스 스캔을 하고 있다이것은, T1테이블의 각각의 데이터에 대해 스캔을 계속해서 수행한다는 것을 나타낸다, SCORE테이블의 데이터 건수만큼, SCORE테이블을 스캔하는 것이다. SCORE에는 현재 총 6건의 데이터가 있다그러나, 9번 문장의 수행 횟수(Execute) 6이 아닌 5이다이것은,영리한 SQL Server가 캐쉬를 사용하기 때문이다서브쿼리에서 사용 되어진 내용을 메모리 캐쉬에 올려 났다가,똑 같은 조건의 매개변수 값(T1.Score가 된다.)이 들어오면 메모리 캐쉬에 있는 부분을 돌려주므로 9번 연산의 수행 횟수가 6이 아닌 5이다. SCORE테이블에는 85점의 점수를 가진 사람이 2명이 존재한다그러므로 첫 번째 85점에 대해서는 실제 테이블에 접근해서 서브쿼리의 결과를 찾았겠지만두 번째 85점에 대해서는 기존에 캐쉬에 저장된 결과를 참조하게 되는 것이다이것은 메모리에 접근해서 연산하는 수행 횟수를 줄여 주므로 성능의 이득을 주게 된다.

메모리 캐쉬에 대한 내용은 Windows Magazine 2004 9월호 page67의 정재우님의 멀티 캐시의 효과를 통해서 더 자세히 알 수 있다.

9번 과정을 거쳐 8번과 7번을 수행하게 된다. 8번과 7번 역시 실행 횟수가 5번 이라는 것은, 9->8->7의 과정의 결과가 메모리 캐쉬에 저장되었다는 것을 의미한다. 8번 과정은 서브쿼리의 결과를 COUNT하는 과정이다이 과정을 통해 등수를 구할 수 있다는 것은 잘 알 것이다. 7번 과정은 스칼라 계산을 하는 과정이다정확히 7번 과정이 무슨 연산을 수행하는지는 필자도 모르겠다아무튼 7번 과정 자체가 성능에 영향을 미치는 요소는 아니므로 무시하고 넘어가도록 하겠다그 다음에는 6번 과정을 거치게 된다. 6번 과정은 해시 매치라는 과정인데이 과정은 해시 테이블을 만드는 과정이다해시 매치는 해시 함수를 사용해서 입력되는 값을 계산해서 테이블을 만들어 낸다.이 테이블은 조인을 위해 사용 된다이 해시 테이블은 T1.Score의 값에 순위를 저장하게 된다그러므로T1.Score 95가 들어 왔을 때는 1이란 숫자를 돌려주고,  90이란 숫자가 들어왔을 때는 2라는 숫자를 돌려주게 된다이 해시 테이블은 무엇을 통해서 만들어 졌는가바로 9, 8, 7 과정을 통해서 만들어 진 것이다이 해시 매치는 9, 8, 7과는 다르게 수행횟수가 6번 인 것을 알 수 있다그것은, FROM절의 SCORE테이블에서 같은 등수의 데이터가 두 번째 들어온 경우라도 이 연산은 수행하게 된다는 것이다.

4번 과정은 실제적으로 조인을 수행하는 연산이다. 5번의 내용과 6번의 내용을 실제 INNER JOIN을 하는 과정이다. 6번의 내용은 해시 테이블이고 5번의 연산 결과는 실제 SCORE테이블이다. 6번의 해시 테이블에는 이미 각 점수 별 등수가 있으므로 이 두 데이터를 연결하는 과정을 수행하는 것이다.

지금까지의 과정을 종합해 보면, 5번의 과정에서 데이터를 입력 받아서, 9->8->7과정을 수행하게 된다.

이 결과를 메모리 캐쉬(해시 테이블)에 쌓아 놓게 되고, 6번과 4번 과정을 통해 해시 테이블과, 5번의 실제 테이블 사이에 데이터 조인을 수행하게 한다. 5번 연산의 모든 데이터에 대해서 동일한 과정을 반복 수행한다여기서 만약 5번 테이블의 데이터에 대해 조인될 결과 값이 이미 해시 테이블에 있는 경우는 9, 8, 7 과정을 생략하고 바로 6 4를 거쳐 조인을 하게 된다.

마지막으로, 3번과 2번 과정은 설명을 하지 않아도 모두 알 수 있으리라 생각한다.

지금까지 서브쿼리의 실행계획을 살펴보았다무엇이 성능을 향상시킬 수 있는 요소라고 생각 되는가? SQL문에 대해서 여러 가지 해법이 있는 것처럼 성능 향상의 방법에도 여러 가지 해법이 있다.

필자가 생각하는 부분은 9번 과정이라고 생각된다실제로 그래픽으로 실행 계획을 보게 되면 9번 과정이 43%로 큰 부분을 차지하고 있다그러므로 9번 과정을 향상시킬 방법이 필요하다무엇이 있을까아마도 대부분의 성능 향상의 답은 인덱스 설정이다실제로 시스템의 성능을 향상시키는 것은 제대로 설정된 인덱스와 제대로 작성된SQL문이라고 생각 할 수 있다이 외에도 여러 가지 요소가 많이 있겠지만개발자가 할 수 있는 요소는 이 두 가지 일것이다사실인덱스도 개발자 스스로 만드는 것에는 무리가 있다하지만개발자가 적절한 인덱스가 무엇인지 알고, DBA팀장을 통해 인덱스를 생성하도록 하는 것은 꼭 필요한 일이다.

9번 과정의 성능 향상을 위해서는 9번 과정의 WHERE절의 컬럼에 대해 성능을 향상시켜 주면 된다.

현재 SCORE테이블에는 StudentID에만 클러스터드 인덱스가 설정되어 있다.

*클러스터드 인덱스와 넌클러스터드의 인덱스의 차이점은 매우 중요하다이에 대해서는 다른 문서를 통해 알고 있기 바란다.

여러 가지 인덱스 전략이 있을 수 있다. StudentID + Score의 결합된 클러스터드 인덱스또는 Score로 만든넌클러스터드 인덱스이것에 대해 어떤 것이 최적일까라는 것을 알아내기란 쉽지 않다하지만어느 정도의 추측은 가능하다그 추측은 경험이 더해 질수록 더욱 정확해 질 것이다.

먼저, SCORE테이블에 제약사항이 무엇인가를 살펴보자그것은 바로 PK인 StudentID이다데이터베이스는 PK를 유지하기 위해서 UNIQUE 인덱스를 사용해야 한다. SQL Server PK에 대해서 기본적으로 클러스터드 인덱스를 만들어 버린다그리고클러스터드 인덱스 스캔과 테이블 스캔은 동일한 것이다클러스터드 인덱스는 성능 향상을 위한 아주 중요한 요소이다이 클러스터드 인덱스를 무조건 PK에 사용할 수는 없다필자의 생각은, StudentID에는 UNIQUE 넌클러스터드 인덱스를 만들어서 PK를 유지시켜 주고, Score에 대해서는 클러스터드 인덱스를 만들어 주는 것이다아마도 SQL에 대해서는 최적을 발휘 할 수 있지 않을까 싶다.

먼저기존의 SCORE테이블을 보호하기 위해서, SCORE2라는 테이블을 만들고인덱스를 설정 해 주는 과정을 거치도록 하자.

<SQL 1-16>

SELECT  * INTO SCORE2

FROM    SCORE

go

ALTER   TABLE SCORE2

        ADD CONSTRAINT PK_SCORE2 PRIMARY KEY NONCLUSTERED (StudentID)

go

CREATE CLUSTERED INDEX SCORE2_CLU_IDX ON SCORE2(Score)

go

자 그럼 <SQL 1-5>의 서브쿼리를 다음처럼 SCORE SCORE2에 대해 실행해서 실행 비용을 비교해 보도록 하자우리는 실행 비용을 쉽게 판단 할 수 있도록 그래픽으로 실행계획을 볼 것이다그래픽으로 실행 계획을 보기 위해서 먼저 SET STATISTICS PROFILE OFF 를 실행해서 기존의 문자열로 출력되던 실행 프로필을 OFF 시켜야 한다.

<SQL 1-17>

SET STATISTICS PROFILE OFF

문자열 실행 프로필을 OFF시킨 후에는 다시 Ctrl + K 를 눌러서 그래픽 실행 계획을 활성화한다.

그 다음다음의 두 개의 SQL을 동시에 실행한다.

<SQL 1-18>

SELECT  StudentID

        ,Score

        ,(      SELECT  COUNT(*)

               FROM    SCORE T2

               WHERE   T2.Score > T1.Score) + 1 Ranking

FROM    SCORE T1

ORDER BY Ranking

go

SELECT  StudentID

        ,Score

        ,(      SELECT  COUNT(*)

               FROM    SCORE2 T2

               WHERE   T2.Score > T1.Score) + 1 Ranking

FROM    SCORE2 T1

ORDER BY Ranking

첫 번째 SQL은 기존의 SCORE테이블이고두 번째 SQL은 새롭게 인덱스를 설정해 준 SCORE2 테이블이다.

그래픽 실행계획을 보면첫 번째 SQL 60%의 비용을두 번째 SQL 40%의 비용을 사용하는 것을 알 수 있다.이것은 기존의 <PLAN 1-1>에서 9번 과정이 인덱스 스캔에서 인덱스 씨크(Seek)로 변경되었기 때문이다새로 만들어진 Score에 대한 클러스터드 인덱스는 9번 과정을 인덱스 씨크로 변경하는데 도움을 준 것이다.

이 외에 다른 방법들을 실제만들어 보고 테스트해 보기 바란다이런고민과 테스트는 자신의 발전에 큰 도움을 줄 것이다.

계속해서 셀프조인으로 해결했던 SQL의 실행 계획을 보도록 하자.

<PLAN 1-2>

No Rows  Exec     StmtText

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

1  6     1        SELECT T1.StudentID   ,MIN(T1.Score) Score   ,COUNT(T2.StudentID) +1 Ranking  FROM SCORE T1 LEFT OUTER

2  6     1          |--Sort(ORDER BY:([Expr1004] ASC))

3  6     1               |--Compute Scalar(DEFINE:([Expr1004]=[Expr1003]+1))

4  6     1                    |--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1008])))

5  6     1                         |--Stream Aggregate(GROUP BY:([T1].[StudentID]) DEFINE:([Expr1002]=MIN([T1].[Score]),

                                             [Expr1008]=COUNT_BIG([T2].[StudentID])))

6  15    1                              |--Nested Loops(Left Outer Join, WHERE:([T1].[Score]<[T2].[Score]))

7  6     1                                   |--Clustered Index Scan(OBJECT:([SQL_PATTERN].[dbo].[SCORE].[PK…] AS [T1]

8  36    6                                   |--Clustered Index Scan(OBJECT:([SQL_PATTERN].[dbo].[SCORE].[PK…] AS [T2]

셀프조인으로 사용한 순위 SQL은 기존의 서브쿼리로 해결한 방법의 실행 계획과는 틀리다제일 눈에 뛰는 것은 조인이 Left Outer Join이며 해시매치를 사용하지 않는다는 것이다.

먼저이 실행계획이 실행되는 순서를 찾아보자한 번 해보았으므로 쉽게 알 수 있을 것이다가장 먼저 수행되는 것은 7번이다그 다음 8, 6번부터 2번 까지 역순으로 수행된다. 7->8->6->5->4->3->2->1 순으로 수행되는 것이다이번 실행 계획은 이전의 서브쿼리 실행계획보다 확실히 보기 쉬운 것 같다.

먼저 7번 연산은 클러스터드 인덱스 스캔을 하고 있다. 8번 연산도 클러스터드 인덱스 스캔을 하고 있으며 실행 횟수가 7번 연산은 1번인데 비해 8번 연산은 6번 이다이것은 7번 연산의 데이터를 스캔 하면서 각 데이터에 대해 8번 연산을 한 번씩 수행한 다는 것이다이것이 실제적으로 6번 과정을 위한 과정이다. 6번 과정은 외부 조인을 수행하고 있다. T1.Score < T2.Score의 조건으로 조인을 수행한다. 6번 과정을 통해 총 15건의 데이터가 만들어 진다그 후 5번 과정을 거쳐서 GROUP BY를 수행하게 된다. GROUP BY MIN COUNT를 통해 해당 StudentID별로 점수와 등수를 구해낸다. 4번 연산은 스칼라 연산이고, 3번 연산도 스칼라 연산이다. 4번 연산은 결과를 보여주기 위해 형 변환을 수행하는 과정이고,(아마도등수를 센 것을 숫자형으로 변환시크는 과정일 것이다.) 3번 연산은 등수에 + 1을 해줌으로서 우리가 원하는 등수를 정확히 표현해 주도록 하는 과정이다마지막으로 2번은 결과를 보기 좋도록 정렬을 하는 과정이다.

이 실행 계획에서 성능을 향상 시킬 수 있는 부분은 무엇일까이것 역시서브쿼리와 마찬가지의 인덱스를 설정해 주면 좀 더 나은 성능을 낼 것 같다그러므로 SCORE2를 사용해서 똑 같은 셀프조인 쿼리를 만들어 비교를 해보도록 하자그래픽 실행 계획을 보는 방법에 대해서는 더 이상 설명하지 않겠다아울러실행 프로필을 설정하고 보는 방법에 대해서도 더 이상 설명하지는 않겠다.

다음과 같은 SQL을 동시에 날려보도록 하자.

<SQL 1-19>

SELECT  T1.StudentID

        ,MIN(T1.Score) Score

        ,COUNT(T2.StudentID) +1 Ranking

FROM    SCORE T1 LEFT OUTER JOIN SCORE T2

        ON T1.Score < T2.Score

GROUP BY T1.StudentID

ORDER BY Ranking

go

SELECT  T1.StudentID

        ,MIN(T1.Score) Score

        ,COUNT(T2.StudentID) +1 Ranking

FROM    SCORE2 T1 LEFT OUTER JOIN SCORE2 T2

        ON T1.Score < T2.Score

GROUP BY T1.StudentID

ORDER BY Ranking

go

순위를 구하기 위한 서브쿼리 방법으로 SCORE SCORE2를 실행해서 비교했던 것과 동일하게 SCORE를 사용한 첫 번째 셀프조인 SQL 60%의 비용을 사용하고, SCORE2를 사용한 두 번째 SQL 40%의 비용이 소모되는 것을 알 수 있었다.

이제순위 구하는 SQL에서 좋은 성능을 내기 위한 방법은 무엇인가조인 조건이 되는 컬럼 부분에 인덱스를 만들어 주는 것이다그리고 넌클러스터드 인덱스보다 클러스터드 인덱스가 좋은 성능을 내는 것은 당연한 일일 것이다우리가 사용했던 예제 SQL만을 볼 때는 이와 같은 결론을 내릴 수 있다.

하지만실제업무에서는 WHERE절의 조건을 충분히 줄여 주는 대상에 인덱스가 있는 것이 아주 중요하다. 6건의 데이터를 가지고 성능을 얘기한다는 건 우스운 얘기이다. 6건의 데이터를 가지고아무리 복잡한 연산을 수행하는SQL을 만든다 해도데이터베이스는 빛의 속도로 이것을 해결할 것이기 때문이다하지만 6건의 데이터를 가지고 성능을 테스트한 우리는 훨씬 더 많은 수의 데이터가 들어 있는 테이블에 대한 SQL도 테스트 할 수 있는 능력을 배운 것이다.

 

지금 우리가 했던 SQL에 대해 최적의 인덱스가 무엇인지가 중요하지 않다제일 중요한 건어느 연산을 변경해서 성능을 향상시킬 수 있는 것을 찾아내는 부분이다필자 역시 계속해서 노력해야 하는 부분이고여러 개발자들도 끊임없이 관찰하고 연구해야 하는 부분이다.

 

순위 구하는 문제에 대해서 마무리 한다면순위를 구하기 위해 어떤 방법을 사용했는가 보다는 데이터를 어떻게 관찰하고 연구해서 결과를 만들어 냈는지의 과정이 더 중요하며그 과정에 대해 많은 개발자들이 끊임없이 노력하기를 바란다는 말로 마무리를 하겠다.




언제 어디서나 좋은 정보 티알조아 - www.trzoa.com



List of Articles
번호 카테고리 제목 글쓴이 날짜 조회 수
12 프로그램 php 날짜 계산식 아름다운총각 2016.05.31 602
» DB SQL 순위구하기 팁 아름다운총각 2015.06.02 1520
10 CSS 텍스트 줄바꿈, 글자자르기 CSS 아름다운총각 2015.02.27 2721
9 DB [mysql] 문자열의 일부를 반환하는 substr, substring_index 함수 아름다운총각 2015.02.13 2821
8 OS 리눅스에서 사용자 완벽 삭제방법 아름다운총각 2014.12.09 4420
7 OS [CentOS] 윈도우 네트워크드라이브 mount 방법 아름다운총각 2014.12.08 2842
6 DB 오라클 NVL함수 & DECODE함수 아름다운총각 2014.12.03 2892
5 DB [MySQL] 테이블 구조복사, 자료복사 아름다운총각 2014.12.01 2531
4 OS Ruby on Rails 를 리눅스에 설치하기 아름다운총각 2014.12.01 2589
3 프로그램 [html5] video 사용법 아름다운총각 2014.11.26 3424


Board Pagination Prev 1 ... 2 Next
/ 2