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
