Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

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



Index Scan in Descending Order

Description

When a query is executed by sorting in descending order as follows, it usually creates a reverse index.

SELECT * FROM tab [WHERE ...] ORDER BY a DESC

However, if you create an ascending index and an descending index in the same column, the possibility of deadlock increases. In order to decrease the possibility of such case, CUBRID supports the descending scan without the separate descending index creation. Users can use the USE_DESC_IDX hint to specify the use of the descending scan. If the hint is not specified, the following three query executions should be considered, provided that the columns listed in the ORDER BY clause can use the index.

  • Sequential scan + Sort in descending order
  • Scan in general ascending order + sort in descending
  • Scan in descending order that does not require a separate scan

Although the USE_DESC_IDX hint is omitted for the scan in descending order, the query optimizer decides the last execution plan of the three listed for an optimal plan.

Note The USE_DESC_IDX hint is not supported for the join query.

Example

CREATE TABLE di (i INT);

CREATE INDEX on di (i);

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

The following example shows how to execute queries by using the USE_DESC_IDX hint.

-- We now run the following query, using the ''use_desc_idx'' SQL hint:

 

SELECT /*+ USE_DESC_IDX */ * FROM di WHERE i > 0 LIMIT 3;

 

Query plan:

 Index scan(di di, i_di_i, (di.i range (0 gt_inf max) and inst_num() range (min inf_le 3)) (covers) (desc_index))

 

            i

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

            5

            5

            5

Even though the example below is the same as that above, the output result may be different because it cannot be scanned in descending order; which is caused by not using the USE_DESC_IDX hint.

-- The same query, without the hint, will have a different output, since descending scan is not used.

 

SELECT  * FROM di WHERE i > 0 LIMIT 3;

 

Query plan:

 

Index scan(di di, i_di_i, (di.i range (0 gt_inf max) and inst_num() range (min inf_le 3)) (covers))

 

            i

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

            1

            2

            3

The following example shows how to sort in descending order by using ORDER BY DESC; the example below is the same as that above. There is no USE_DESC_IDX hint in the following example; however it is scanned in descending order and the result is the same as the example 1.

-- We also run the same query , this time asking that the results are displayed in descending order. However, no hint will be given. Since the

 

-- ORDER BY...DESC clause is present, CUBRID will use descending scan, even if the hint is  was not given, thus avoiding to sort the records.

 

SELECT * FROM di WHERE i > 0 ORDER BY i DESC LIMIT 3;

 

Query plan:

 Index scan(di di, i_di_i, (di.i range (0 gt_inf max)) (covers) (desc_index))

 

            i

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

            5

            5

            5