Descending Index type – performance and limitations

The main topic of this article is the DESC index type, which is often mixed-up with the Btree index type because of the great similarity they share when looking at the index creation syntax.

“Raison d’etre” for having DESC index type is to avoid expensive sorting when you are sure you’ll always query and sort a table in descending order (ORDER BY … DESC clause at the end of SQL statement).

Being able to avoid sorting was especially important 10+ years when hard drives where the main bottlenecks (this is also why you need to calibrate your Oracle database before you can use it in order for CBO to be aware of storage limitations).

That’s also why you need to understand the historical context in which some technology was created.

Let’s take a look at the mechanics behind the DESC index type.

Below you can see the syntax for creating a “normal” Btree index in the CREATE_DATE column:

create index create_date_ind on ttest(create_date) tablespace users nologging;

Next DDL statement will create a DESC index type in the UPDATE_DATE column:

create index update_date_ind on ttest(update_date desc) tablespace users nologging;

Looking at the syntax, you probably understand why many believe that the DESC index is just a subset of the Btree index, and this the main reason for making Data Model design mistakes.

Despite the syntax being quite similar, what happens behind the scenes is not.

As you can see in the following figure, when creating a DESC index type, Oracle silently creates a function based index on a virtual column.

To understand the difference between the two index types, I’ll first execute the following query using the normal Btree index defined in the CREATE_DATE column:

select /*+ index(a, create_date_ind) */ * from ttest a where a.create_date > to_date('04.07.2019 00:01:00', 'dd.mm.rrrr hh24:mi:ss')
order by a.create_date desc;

Note the SORT ORDER BY operation in the execution plan.

Next I’ll execute a similar statement, but this time I’ll use an index defined as DESC on UPDATE_DATE column:

This time SORT ORDER BY is missing, which explains the reason for having DESC index type.

While the previous test yields the expected results, what you might not know is the execution speed of running Btree versus the DESC index type.

This time I’ll execute a plain count of all records (the selected date is the oldest in the table) using the Btree index to avoid network fetch overhead and force the query to use the index instead of FTS:

SCOTT@orcl> select /*+ index(a, create_date_ind) */ count(*) from ttest a where a.create_date > to_date('04.07.2019 00:01:00', 'dd.mm.rrrr hh24:mi:ss');

Elapsed: 00:00:00.39

Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
      26528  consistent gets
	  0  physical reads
	  0  redo size
	553  bytes sent via SQL*Net to client
	624  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

The same query but this time the DESC index type will be used:

SCOTT@orcl> select /*+ index(a, update_date_ind) */ count(*) from ttest a where a.update_date > to_date('04.07.2019 00:01:00', 'dd.mm.rrrr hh24:mi:ss');

Elapsed: 00:00:00.81

Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
      29365  consistent gets
	  0  physical reads
	  0  redo size
	553  bytes sent via SQL*Net to client
	624  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

You may notice that in the case of the DESC index, the execution speed is more than 2 times slower than the Btree index (or Btree index is 2x faster than the DESC index) even though the statistics are almost identical (consistent gets).

Additionally, in a more complex queries, DESC index behavior might produce an unexpected execution plan, as you can see in the following figure:

Summary:

As always, the most important is to understand how some technology works under the hood, what problems it solves and what problems it introduces, and put all of these facts in a historical context (e.g. flash disks where not available at the time of DESC index type creation).

DESC index type was invented to avoid expensive SORT operations in cases where most of a queries that includes ORDER BY DESC clause at the end.

On the other hand, there will be penalties and restrictions that you have to pay when using the DESC index type, which is why I wrote this article to clarify less known consequences.

As always, you have to weight all the pros & cons and decide whether it’s worth using it or not.

Get notified when a new post is published!

Name*

Email*

 Please accept terms & conditions