By default , relational databases create indexes using increasingly ordered binary tree structures. However there is the possibility of creating it in a decreasing way as well.
My question is whether (and when) it is recommended to use it. For example:
SELECT nome
FROM pessoa
WHERE data_cadastro >= '2014-01-01'
ORDER BY data_cadastro DESC
In SQL above, considering WHERE and ORDER BY , a decreasing index in column data_cadastro would improve performance?
It depends on your need. By default the indexes are created in ascending order because, in general, the values (usually ids) are generated incrementally. In this way the new (larger) values go to the end of the "list". This reduces the chance of index fragmentation.
Example: Suppose you have a table where the Id is autoident starting at 1 and adding +1 to each new record. You define this column as clustered PK but it changes the order from index to decreasing.
This will force page splits since each new record will force table reordering.
A plausible scenario for using declining sorting could be where you make fewer inserts but often make many selects about newer values
Example: You have a meeting record that you attended and create an index sorted by date. Eventually a new meeting is entered and you usually just use a select to return meetings last week.
As always in SQL everything depends. In doubt do an acid test. Try select, insert, delete, update operations on the table in question using the ordering of the two forms and try to measure the performance differences. This is really the only way to figure out how to improve the performance of an index
Imagine an index in a column of a grouped table:
CREATE TABLE mytable (
pk INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL
)
CREATE INDEX ix_mytable_col1 ON mytable (col1)
The index in col1 maintains col1 values ordered together with the row references.
Since the table is grouped, the references to the rows are actually the values of pk. They are also sorted within each col1 value.
This means that the indexes are sorted in (col1, pk), and this query:
SELECT col1, pk
FROM mytable
ORDER BY
col1, pk
Do not need sorting!
If we create the index as follows:
CREATE INDEX ix_mytable_col1_desc ON mytable (col1 DESC)
then the values of col1 will be sorted descending, but the values of pk within each col1 value will be sorted ascending.
This means that the following query:
SELECT col1, pk
FROM mytable
ORDER BY
col1, pk DESC
Can be served by ix_mytable_col1_desc but not by ix_mytable_col1.