Descending index gotcha

Sometime things just leap out at you when you least expect it.

Let’s say I’ve got a table that I’d like to shrink the space on. No problems there.

SQL> drop table T purge;

Table dropped.

 

SQL> create table T as
  2  select rownum x from dual
  3  connect by level  create index IX on T ( x );

Index created.

 

SQL> alter table T enable row movement;

Table altered.

 

SQL> alter table T shrink space;

Table altered.

So far so good. But then I decide that I’d like to scan this table in descending order through the index column. So I recreate my index as DESCENDING.

SQL> drop index IX;

Index dropped.

 

SQL> create index IX on T ( x desc);

Index created.

 

SQL> alter table T shrink space;

alter table T shrink space

*

ERROR at line 1:

ORA-10631: SHRINK clause should not be specified for this object

So what happened ? Well, a descending index is implemented as function based index, and shrink space on tables that have function based indexes is prohibited.

As with all things, prudent testing to find the boundary cases in Oracle is important.