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 <= 1000;

Table created.

 

SQL> 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s