Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

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

Optimizing ORDER BY Clause

Description

The index including all columns in the ORDER BY clause is referred to as the ordered index. In general, for an ordered index, the columns in the ORDER BY clause should be located at the front of the index.

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

  • The index consisting of tab(col1, col2) is an ordered index.
  • The index consisting of tab(col1, col2, col3) is also an ordered index. This is because the col3, which is not referred by the ORDER BY clause comes after col1 and col2.
  • The index consisting of tab(col1) is not an ordered index.
  • You can use the index consisting of tab(col3, col1, col2) or tab(col1, col3, col2) for optimization. This is because col3 is not located at the back of the columns in the ORDER BY clause.

Although the columns composing an index do not exist in the ORDER BY clause, you can use an ordered index if the column condition is a constant.

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

If the index consisting of tab(col1, col2, col3) exists and the index consisting of tab(col1, col2) do not exist when executing the above query, the query optimizer uses the index consisting of tab(col1, col2, col3) as an ordered index. You can get the result in the requested order when you execute an index scan, so you don't need to sort records.

If you can use the sorted index and the covering index, use the latter first. If you use the covering index, you don't need to retrieve additional data, because the data result requested is included in the index page, and you won't need to sort the result if you are satisfied with the index order.

If the query doesn't include any conditions and uses an ordered index, the ordered index will be used under the condition that the first column meets the NOT NULL condition.

Example

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);

The following example shows that indexes consisting of tab(j,k) become sorted indexes and no separate sorting process is required because GROUP BY is executed by j and k columns.

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

The following example shows that j and k columns execute ORDER BY and the index including all columns are selected so that indexes consisting of tab(j,k) are used as covering indexes; no separate process is required because the value is selected from the indexes themselves.

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

The following example shows that i column exists, ORDER BY is executed by j and k columns, and columns that perform SELECT are i, j, and k. Therefore, indexes consisting of tab(i,j,k) are used as covering indexes; separate sorting process is required for ORDER BY j, k even though the value is selected from the indexes themselves.

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