Creating Sample Table dept For Use In Index Examples
create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
)
Creating Sample Table emp For Use In Index Examples
create table emp(
empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno)
)
Creating An Index On A Column Explicitly
CREATE INDEX emp_ename ON emp(ename)
STORAGE (INITIAL 20K
NEXT 20k)
Creating A Unique Index
CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
Creating An Index Online
CREATE INDEX emp_name ON emp (mgr) ONLINE
Associating An Index With A Constraint
CREATE TABLE a (
a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)))
Creating An Index Using Key Compression
CREATE INDEX emp_job_comp ON emp(job)
COMPRESS 1
Creating An Invisible Index
CREATE INDEX dep_loc_inv ON dept(loc)
INVISIBLE