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 is an example of executing a query with 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

The following example shows that if the query doesn't have the USE_DESC_IDX even though it is same as the above, it can not be scanned in descending order, and the output result may be different.

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

            1

            1

The following example has the same query and requests the sorting in descending order with ORDER BY DESC. There is no USE_DESC_IDX hint in this case but the output result is the same as in the first example, because it is scanned in descending order.

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