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)
)
Table created.
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)
)
Table created.
Creating An Index On A Column Explicitly
CREATE INDEX emp_ename ON emp(ename)
STORAGE (INITIAL 20K
NEXT 20k)
Index created.
Creating A Unique Index
CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
Index created.
Creating An Index Online
CREATE INDEX emp_name ON emp (mgr) ONLINE
Index created.
Associating An Index With A Constraint
CREATE TABLE a (
a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)))
Table created.
Creating An Index Using Key Compression
CREATE INDEX emp_job_comp ON emp(job)
COMPRESS 1
Index created.
Creating An Invisible Index
CREATE INDEX dep_loc_inv ON dept(loc)
INVISIBLE
Index created.