Search

Top 60 Oracle Blogs

Recent comments

Take care with automatic indexes

This one came in via an AskTOM question. You need to be careful when disabling constraints that are underpinned by an index, especially when it comes to the storage for that index.

Let’s take a look at a simple example. I’ll explicitly nominate the LARGETS tablespace for my index that will support the primary key.



SQL> create table t as
  2      select 1 x, 1 y from dual;

Table created.

SQL>
SQL> alter table t add constraint
  2    t_pk primary key ( x ) using index tablespace largets;

Table altered.

SQL>
SQL> select tablespace_name from user_segments
  2  where segment_name = 'T_PK';

TABLESPACE_NAME
--------------------
LARGETS

1 row selected.

SQL>
SQL> alter table t modify constraint t_pk disable;

Table altered.

SQL> select * from user_indexes where index_name = 'T_PK';

no rows selected

SQL> alter table t modify constraint t_pk enable;

Table altered.

Now let us look at where the index that is needed for that constraint has been re-created.


SQL> select tablespace_name from user_segments
  2  where segment_name = 'T_PK';
TABLESPACE_NAME
--------------------
USERS

The index is a quite distinct entity from the constraint, and unless you nominate a tablespace it will be created in the default tablespace for the schema, which in my case was USERS.

So take care – you might need to pre-create indexes or explicitly rebuild them with a tablespace specification before re-enabling those constraints.


SQL> create table t as
  2      select 1 x, 1 y from dual;

Table created.

SQL>
SQL> create unique index t_pk on t ( x ) tablespace largets;

Index created.

SQL> alter table t add constraint   t_pk primary key ( x ) using index ;

Table altered.

SQL>
SQL> select tablespace_name from user_segments
  2  where segment_name = 'T_PK';

TABLESPACE_NAME
--------------------
LARGETS

1 row selected.

SQL>
SQL> alter table t modify constraint t_pk disable;

Table altered.

SQL> select tablespace_name from user_segments
  2  where segment_name = 'T_PK';

TABLESPACE_NAME
--------------------
LARGETS

1 row selected.

SQL> alter table t modify constraint t_pk enable;

Table altered.

SQL>
SQL> select tablespace_name from user_segments
  2  where segment_name = 'T_PK';

TABLESPACE_NAME
--------------------
LARGETS

1 row selected.