Differences and linkages between Oracle unique indexes and normal indexes, and using index usage

differences and linkages between Oracle unique indexes and ordinary indexes

Difference: Unique index and general index the biggest difference is that you add a single layer of constraint to the index column. A data column that adds a unique index can be empty, but it must be unique as long as the data value is respected.

Contact: 1) Unique index is an additional constraint that adds uniqueness. This constraint strictly guarantees that the value of the indexed column is unique, and that the business constraints on some data columns are important functions. such as a data column, cannot be a primary key, and allowed to be empty, but the business requires a unique feature, this time with a unique index is the best rotation. 2) There is no significant difference in performance.

Using index usage:

Create primary key .... Using Index: Creates a primary key at the same time it is indexed, and the name is the same as the primary key name.

After 9i, when a PK is created, a unique index corresponding to it is created automatically. (Because a field is automatically indexed if it is set to unique). If not specifically specified, the table space for this index is the same as the table space for tables, but it is not recommended to put the two together.

General statement Format:

Create table Test (name varchar (10)); Alter table Test Add primary key (name) tablespace Tablespace1;

There are two bad ways to handle the above, the first is that you cannot specify the name of the index, and the second cannot specify the table space where the index resides. To avoid this error, finally define the table and table space in the following manner.

CREATE TABLE Test_uid (name varchar), Constraint TEST_UID_PK primary key (name) using index (create unique index Uid_test_ UID on Test_uid (name) tablespace tablespace2);

Of course, it can be partially handled.

Create table Testone (name varchar) Tablespace1; Alter table Testone Add constraint pk_testine1 primary key (name) using index tablespace tablespace2;

As a good habit, do not put index and table data in the same table space.

A generic index builds a single table space.

---------

This series for the recent period of time to learn Oracle's learning notes, recorded in this as their own review, some of the network, some of the books, but the time has long, remember what is quoted, if reproduced but not marked out, specifically apologized.

Differences and linkages between Oracle unique indexes and normal indexes, and using index usage