INDEX

Overview

• Automatically- PRIMARY KEY- UNIQUE KEY
• Manually- CREATE INDEX- CREATE TABLE

CREATE INDEX with CREATE TABLE statement

Examples)

CREATE TABLE NEW_EMP (employee_id NUMBER(6) PRIMARY KEY USING INDEX (CREATE INDEX emp_id_idx ON NEW_EMP(employee_id) first_name VARCHAR2(20), last_name VARCHAR2(25));

SELECT INDEX_NAME, TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'NEW_EMP';

** An index cannot be modified. You have to drop it and create it again to modify the Index.** This post was posted by Office 2007Using Existing Index for your PRIMARY KEY Step 1: Just create a table without any primary key. CREATE    TABLE NEW_EMP2 ( employee_id   NUMBER(6),   first_name      VARCHAR2(20),   last_name      VARCHAR2(25)); Step 2: Create an index CREATE INDEX emp_id_idx ON new_emp2(employee_id); Step 3: Add a primary key to the existing table by using the existing index. ALTER TABLE new_emp2 ADD PRIMARY KEY (employee_id) USING INDEX emp_id_idx2;