Take care with automatic indexes

Posted by

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.

 

4 comments

  1. Connor -few questions.

    which version was your test case is from?

    I am on 12.2.0.1 – don’t see that happen.

    demo@PDB1> col username format a10
    demo@PDB1> select username,default_tablespace from user_users;

    USERNAME DEFAULT_TABLESPACE
    ———- ——————————
    DEMO USERS

    demo@PDB1> drop table t purge;

    Table dropped.

    demo@PDB1> create table t as
    2 select *
    3 from all_users;

    Table created.

    demo@PDB1> create unique index t_pk on t(user_id) tablespace ts_data;

    Index created.

    demo@PDB1> alter table t add constraint t_pk primary key(user_id) using index;

    Table altered.

    demo@PDB1> select tablespace_name from user_segments where segment_name =’T_PK’;

    TABLESPACE_NAME
    ——————————
    TS_DATA

    demo@PDB1> select constraint_name,index_name
    2 from user_constraints
    3 where table_name =’T’
    4 and constraint_type =’P’;

    CONSTRAINT INDEX_NAME
    ———- ———-
    T_PK T_PK

    demo@PDB1> alter table t modify constraint t_pk disable;

    Table altered.

    demo@PDB1> select index_name from user_indexes where table_name =’T’;

    INDEX_NAME
    ———-
    T_PK

    demo@PDB1> select tablespace_name from user_segments where segment_name =’T_PK’;

    TABLESPACE_NAME
    ——————————
    TS_DATA

    demo@PDB1> select constraint_name,index_name
    2 from user_constraints
    3 where table_name =’T’
    4 and constraint_type =’P’;

    CONSTRAINT INDEX_NAME
    ———- ———-
    T_PK

    demo@PDB1> alter table t modify constraint t_pk enable;

    Table altered.

    demo@PDB1> select index_name from user_indexes where table_name =’T’;

    INDEX_NAME
    ———-
    T_PK

    demo@PDB1> select tablespace_name from user_segments where segment_name =’T_PK’;

    TABLESPACE_NAME
    ——————————
    TS_DATA

    demo@PDB1> select constraint_name,index_name
    2 from user_constraints
    3 where table_name =’T’
    4 and constraint_type =’P’;

    CONSTRAINT INDEX_NAME
    ———- ———-
    T_PK T_PK

    demo@PDB1> select tablespace_name from user_segments where segment_name =’T’;

    TABLESPACE_NAME
    ——————————
    USERS

    demo@PDB1>
    demo@PDB1>

  2. Hello Connor,

    Which database version have you used for this test ?

    As far as I am aware, at least since 8i, when an index is created separately and then “picked up”
    by adding a PK/UK constraint, the index remains in place when dropping or disabling the constraint.

    I just tested this in LiveSQL, and, indeed, after disabling the constraint, the index is still there.

    Cheers & Happy Easter !
    Iudith Mentzel

    1. LOL….this was a cut-paste fail on my part. Even in my conclusion I had “You might need to precreate the index” and then had cut-pasted a chunk of code from my “precreated index” solution into the main body. Thanks for spotting it – I’ve amended the post

Got some thoughts? Leave a comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.