Create and Drop index on table with constraint understanding

Create and Drop index on table with constraint understanding

If we create primary key constraint and unique constraint on any column of table then Oracle automatically create index (With Primary key it will create cluster index) and for unique key it will create non cluster index.

Create index on table Name

-- Create normal index
Create index index_name on table_name ( column_name);

-- Create unique index
Create unique index index_name on table_name (column_name);

-- Create bitmap index
Create bitmap index index_name on table_name(column_name);

Note: Bitmap index is not suitable for transnational tables(OLTP tables), its held locks for long time during update or delete operation, it better to used in data warehouse databases.

Drop index

drop index schema_name.index_name;

Check information about index

set line 250 pages 1000
column b.tablespace_name format a10
column table_name format a10
column index_name format a20
column column_name format a16
column tablespace_name format a8
column last_analyzed format a9
column degree format a1
Select a.table_name, a.index_name, a.column_name,b.tablespace_name,b.last_analyzed,b.degree,b.partitioned,b.status,a.descend
FROM dba_ind_columns a,dba_indexes b where b.index_name = a.index_name and
a.table_owner='IC' and a.table_name = 'TRAN'
Order by a.table_name, a.Index_name,a.column_position,a.column_name;

Check index with constraints on table

col constraint_name for a20
col owner for a6
col table_name for a20
col index_owner for a6
col index_name for a20
select constraint_name,owner,constraint_type,table_name,index_owner,index_name from dba_constraints where owner='HR' and table_name='EMPLOYEES' and index_name is not null;

CONSTRAINT_NAME      OWNER  C TABLE_NAME           INDEX_ INDEX_NAME
-------------------- ------ - -------------------- ------ ---------------
EMP_EMAIL_UK         HR     U EMPLOYEES            HR     EMP_EMAIL_UK
EMP_EMP_ID_PK        HR     P EMPLOYEES            HR     EMP_EMP_ID_PK


Note: If we tried to drop constraint enabled index then we get the error:

SQL> drop index hr.emp_email_uk;
drop index hr.emp_email_uk
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

If you drop unique constraint then it will drop the index also.

SQL> alter table hr.employees drop constraint emp_email_uk;
Table altered.

SQL> select constraint_name,owner,constraint_type,table_name,index_owner,index_name from dba_constra
ints where owner='HR' and table_name='EMPLOYEES' and index_name is not null;

CONSTRAINT_NAME      OWNER  C TABLE_NAME           INDEX_ INDEX_NAME
-------------------- ------ - -------------------- ------ --------------------
EMP_EMP_ID_PK        HR     P EMPLOYEES            HR     EMP_EMP_ID_PK

SQL> Select a.table_name, a.index_name from dba_indexes a where a.table_name='EMPLOYEES';

TABLE_NAME           INDEX_NAME
-------------------- --------------------
EMPLOYEES            EMP_EMP_ID_PK
EMPLOYEES            EMP_DEPARTMENT_IX
EMPLOYEES            EMP_JOB_IX
EMPLOYEES            EMP_MANAGER_IX
EMPLOYEES            EMP_NAME_IX

Advertisements

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 )

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.