Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



ORDER BY 절 최적화

설명

ORDER BY 절에 있는 모든 컬럼을 포함하는 인덱스를 정렬된 인덱스(ordered index)라고 한다. 정렬된 인덱스가 되기 위한 일반적인 조건은 ORDER BY 절에 있는 컬럼들이 인덱스의 가장 앞부분에 위치하는 경우이다.

SELECT * FROM tab WHERE col1 > 0 ORDER BY col1, col2

  • tab(col1, col2)으로 구성된 인덱스는 정렬된 인덱스이다.
  • tab(col1, col2, col3)으로 구성된 인덱스도 정렬된 인덱스이다. ORDER BY 절에서 참조하지 않는 col3는 col1, col2 뒤에 오기 때문이다.
  • tab(col1)으로 구성된 인덱스는 정렬된 인덱스가 아니다.
  • tab(col3, col1, col2) 혹은 tab(col1, col3, col2) 로 구성된 인덱스는 최적화에 사용할 수 없다. 이는 col3가 ORDER BY 절의 컬럼들 뒤에 위치하지 않기 때문이다.

인덱스를 구성하는 컬럼이 ORDER BY 절에 없더라도 그 컬럼의 조건이 상수일 때는 정렬된 인덱스의 사용이 가능하다.

SELECT * FROM tab WHERE col2=val ORDER BY col1,col3;

tab(col1, col2, col3)로 구성된 인덱스가 존재하고 tab(col1, col2)로 구성된 인덱스는 없이 위의 질의를 수행할 때, 질의 최적화기는 tab(col1, col2, col3)로 구성된 인덱스를 정렬된 인덱스로 사용한다. 즉, 인덱스 스캔 시 요구하는 순서대로 결과를 가져오므로, 행에 대해 정렬할 필요가 없다.

정렬된 인덱스와 커버링 인덱스를 함께 사용할 수 있으면 커버링 인덱스를 먼저 사용한다. 커버링 인덱스를 사용하면 요청한 데이터의 결과가 인덱스 페이지에 모두 들어 있어 추가적인 데이터를 검색할 필요가 없으며, 이 인덱스가 순서까지 만족한다면, 결과를 정렬할 필요가 없기 때문이다.

질의가 조건을 포함하지 않으며 정렬된 인덱스를 사용할 수 있다면, 인덱스의 첫 번째 컬럼이 NOT NULL 조건을 만족한다는 전제 하에서는 정렬된 인덱스가 사용될 것이다.

예제

CREATE TABLE tab (i INT, j INT, k INT);

CREATE INDEX on tab (j,k);

INSERT INTO tab VALUES (1,2,3),(6,4,2),(3,4,1),(5,2,1),(1,5,5),(2,6,6),(3,5,4);

다음의 예는 j, k 컬럼으로 ORDER BY를 수행하므로 tab(j,k)로 구성된 인덱스는 정렬된 인덱스가 되고 별도의 정렬 과정을 거치지 않는다.

SELECT i,j,k FROM tab WHERE j > 0 ORDER BY j,k;

 

--  the  selection from the query plan dump shows that the ordering index i_tab_j_k was used and sorting was not necessary

--  (/* --> skip ORDER BY */)

Query plan:

iscan

    class: tab node[0]

    index: i_tab_j_k term[0]

    sort:  2 asc, 3 asc

    cost:  fixed 0(0.0/0.0) var 1(0.0/1.0) card 0

Query stmt:

select tab.i, tab.j, tab.k from tab tab where ((tab.j> ?:0 )) order by 2, 3

/* ---> skip ORDER BY */

 

            i            j            k

=======================================

            5            2            1

            1            2            3

            3            4            1

            6            4            2

            3            5            4

            1            5            5

            2            6            6

다음의 예는 j, k 컬럼으로 ORDER BY를 수행하며 SELECT하는 컬럼을 모두 포함하는 인덱스가 존재하므로 tab(j,k)로 구성된 인덱스가 커버링 인덱스로서 사용된다. 따라서 인덱스 자체에서 값을 가져오게 되며 별도의 정렬 과정을 거치지 않는다.

SELECT /*+ RECOMPILE */ j,k FROM tab WHERE j > 0 ORDER BY j,k;

 

--  in this case the index i_tab_j_k is a covering index and also respects the orderind index property.

--  Therefore, it is used as a covering index and sorting is not performed.

 

Query plan:

iscan

    class: tab node[0]

    index: i_tab_j_k term[0] (covers)

    sort:  1 asc, 2 asc

    cost:  fixed 0(0.0/0.0) var 1(0.0/1.0) card 0

 

Query stmt: select tab.j, tab.k from tab tab where ((tab.j> ?:0 )) order by 1, 2

/* ---> skip ORDER BY */

 

            j            k

==========================

            2            1

            2            3

            4            1

            4            2

            5            4

            5            5

            6            6

다음의 예는 i 컬럼 조건이 있으며 j, k 컬럼으로 ORDER BY를 수행하고, SELECT하는 컬럼이 i, j, k이므로 tab(i,j,k)로 구성된 인덱스가 커버링 인덱스로서 사용된다. 따라서 인덱스 자체에서 값을 가져오게 되지만, ORDER BY j, k에 대한 별도의 정렬 과정을 거친다.

CREATE INDEX ON tab (i,j,k);

SELECT /*+ RECOMPILE */ i,j,k FROM tab WHERE i > 0 ORDER BY j,k;

 

-- since an index on (i,j,k) is now available, it will be used as covering index. However, sorting the results according to

-- the ORDER BY  clause is needed.

Query plan:

temp(order by)

    subplan: iscan

                 class: tab node[0]

                 index: i_tab_i_j_k term[0] (covers)

                 sort:  1 asc, 2 asc, 3 asc

                 cost:  fixed 0(0.0/0.0) var 1(0.0/1.0) card 1

    sort:  2 asc, 3 asc

    cost:  fixed 6(5.0/1.0) var 1(0.0/1.0) card 1

 

Query stmt: select tab.i, tab.j, tab.k from tab tab where ((tab.i> ?:0 )) order by 2, 3

 

            i            j            k

=======================================

            5            2            1

            1            2            3

            3            4            1

            6            4            2

            3            5            4

            1            5            5

            2            6            6