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.
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.
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;
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))
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;
Index scan(di di, i_di_i, (di.i range (0 gt_inf max) and inst_num() range (min inf_le 3)) (covers))
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;
Index scan(di di, i_di_i, (di.i range (0 gt_inf max)) (covers) (desc_index))