Descending Index

Descending Indexes are new feature of Oracle8i, They extend the Functionality of B*Tree Index. They allow a column to be stored from Big to small values in Index instead of Ascending.

rajesh@10GR2> CREATE TABLE t AS SELECT * FROM ALL_OBJECTS;

Table created.

Elapsed: 00:00:04.87
rajesh@10GR2> CREATE INDEX t_ind ON t(owner,object_type,object_id);

Index created.

Elapsed: 00:00:00.48
rajesh@10GR2>
rajesh@10GR2> EXEC dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,method_opt=>'for all indexed columns size auto');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.03
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain;
rajesh@10GR2>
rajesh@10GR2> select owner,object_type
  2  from T
  3  where owner IS NOT NULL
  4  and object_type IS NOT NULL
  5  order by owner DESC,object_type DESC;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1014620761

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       | 56281 |   879K|   243   (1)| 00:00:03 |
|*  1 |  INDEX FULL SCAN DESCENDING| T_IND | 56281 |   879K|   243   (1)| 00:00:03 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_TYPE" IS NOT NULL)

rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select owner,object_type
  2  from T
  3  where owner IS NOT NULL
  4  and object_type IS NOT NULL
  5  order by owner ASC,object_type DESC;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 473021395

---------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       | 56281 |   879K|       |   358   (2)| 00:00:05 |
|   1 |  SORT ORDER BY        |       | 56281 |   879K|  2664K|   358   (2)| 00:00:05 |
|*  2 |   INDEX FAST FULL SCAN| T_IND | 56281 |   879K|       |    55   (2)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE" IS NOT NULL)

rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2> CREATE INDEX desc_ind_x ON T(owner ASC,object_type DESC);

Index created.

Elapsed: 00:00:00.35
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain;
rajesh@10GR2>
rajesh@10GR2> select owner,object_type
  2  from t
  3  where owner IS NOT NULL
  4  and object_type IS NOT NULL
  5  order by owner ASC,object_type DESC;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2722378448

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            | 56281 |   879K|   212   (1)| 00:00:03 |
|*  1 |  INDEX FULL SCAN | DESC_IND_X | 56281 |   879K|   212   (1)| 00:00:03 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_TYPE")) IS NOT
              NULL)

rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2>

Now, we are able to read the data sorted, there is no extra sort step at the end of the plan.