Difference between revisions of "Oracle:Indexes"
From mi-linux
Jump to navigationJump to search (Created page with "Main Page >> Oracle and SQL >> Workbook >> Miscellaneous features >> Indexes == Indexing Tables in Oracle == === Creating ...") |
|||
Line 23: | Line 23: | ||
* For information, the UNIQUE clause could have been used instead to achieve this, when the table was created (note, do not type this in): | * For information, the UNIQUE clause could have been used instead to achieve this, when the table was created (note, do not type this in): | ||
− | CREATE TABLE | + | CREATE TABLE Dept( |
deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, | deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, | ||
dname VARCHAR2(14) NOT NULL CONSTRAINT uc_dname CHECK (dname=UPPER(dname)), | dname VARCHAR2(14) NOT NULL CONSTRAINT uc_dname CHECK (dname=UPPER(dname)), |
Latest revision as of 15:36, 6 April 2016
Main Page >> Oracle and SQL >> Workbook >> Miscellaneous features >> Indexes
Indexing Tables in Oracle
Creating an index
A table index should include the columns you use most frequently in search conditions.
To index the ENAME column of the EMP table:
CREATE INDEX EMP_ENAME ON EMP(ENAME);
Using an index to enforce uniqueness
The primary key of the EMP table is EMPNO, however there may also be another candidate key, for example the National Insurance Number of every person is also unique, or all department names must be unique.
To create a unique index on a column such as this you could:
CREATE UNIQUE INDEX DEPT_UNQ ON DEPT(DNAME);
- For information, the UNIQUE clause could have been used instead to achieve this, when the table was created (note, do not type this in):
CREATE TABLE Dept( deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, dname VARCHAR2(14) NOT NULL CONSTRAINT uc_dname CHECK (dname=UPPER(dname)), loc VARCHAR2(13) CONSTRAINT uc_loc CHECK (loc=UPPER(loc)), constraint unq_dept UNIQUE(dname));
Deleting an index
To remove an index use the command DROP.
DROP INDEX EMP_EMPNI ON EMP
General rules for indexes
- If a table has more than a few hundred rows, index it
- Try not to create more than two or three indexes on a table
- Choose which columns to index based on frequency of use
- DO NOT Create a unique index on primary keys, specify the primary key constraint at table creation time, or alter the table to add the primary key constraint. The former is preferable.