Oracle unique index constraint creation and Deletion

/* Memory space parameters allocated for creating Bitmap indexes to accelerate index creation */

Show parameter create_bit;

/* Change the index storage parameter */

Alter index index_name pctfree 30 storage (initial 200 K next 200 k );

/* Manually allocate a partition to the Index */

Alter index index_name allocate extent (size 200 K datafile '$ Oracle/oradata /..');

/* Release useless space in the Index */

Alter index index_name deallocate unused;

/* Index reconstruction */

Alter index index_name rebuild tablespace tablespace_name;

/* Interchange between common indexes and reverse indexes */

Alter index index_name rebuild tablespace tablespace_name reverse;

/* Do not lock the table when re-indexing */

Alter index index_name rebuild online;

/* Fragment the Index */

Alter index index_name coalesce;

/* The analysis index is actually the process of updating statistics */

Analyze index index_name validate structure;

Desc index_state;

Drop index index_name;

Alter index index_name monitoring usage; ----- monitor whether the index is used

Alter index index_name nomonitoring usage; ---- cancel monitoring

/* View of index information */

Select * From dba_indexes/dba_ind_columns/dbs_ind_expressions/V $ object_usage;

######### Maintaining data integrity )##########

Alter table table_name drop constraint constraint_name; ---- drop Constraint

Alter table table_name add constraint constraint_name primary key (column_name1, column_name2); ----- create a primary key

Alter table table_name add constraint constraint_name unique (column_name1, column_name2); --- create a unique constraint

/* Create a foreign key constraint */

Alter table table_name add constraint constraint_name foreign key (column_name1) References table_name (column_name1 );

/* The old data is invalid. Only new data is restricted. [enable/disable: constraint/not constraint new data; novalidate/validate: Incorrect/verification of old data] */

Alter table table_name add constraint constraint_name check (column_name like 'B %') enable/disable novalidate/validate;

/* Modify constraints, delay verification, and commit verification */

Alter table table_name modify constraint constraint_name initially deferred;

/* Modify constraints and verify now */

Alter table table_name modify constraint constraint_name initially immediate;

Alter session set constraints = deferred/immediate;

/* Drop a primary key table with a foreign keys and cascade deletion with the cascade constraints parameter */

Drop table table_name cascade constraints;

/* When the truncate foreign key table is used, the foreign key is set to invalid first, and then truncate ;*/

Truncate table table_name;

/* Invalid constraints */

Alter table table_name disable constraint constraint_name;

Alter table table_name enable novalidate constraint constraint_name;

Simple and practical. Thank you.Http://wfly2004.blog.163.com/blog/static/11764272010629114155174/

Http://hi.baidu.com/zhangcheng1/blog/item/54deb0cc9ab69d1701e9281e.html