Experiment-12
Create a table and perform the search operation on table using
indexing and non-indexing techniques.
SQL> CREATE TABLE TEACHER(STAFF_ID VARCHAR2(4)
PRIMARY KEY, STAFF_NAME VARCHAR2(30), QUALIFICATION
VARCHAR2(10), HIREDATE DATE, JOB VARCHAR2(30)
ADDRESS VARCHAR2(15), PH_NUM NUMBER(10), SALARY
NUMBER(7, 2), DEPARTMENT VARCHAR2(10));
Table created.
SQL> insert into teacher values('T101','SUNITHA','MCA','29-JUN
06','ASSOCIATE PROFESSOR','VIJAYAWADA',9985061308,23000,
'MCA');
1 row created.
SQL>insert into teacher values('T102','FRED SMITH','MTECH',
'07-
MAR03','ASSOCIATEPROFESSOR','GUNTUR',9985063445,36000,
'MBA');
1 row created.
SQL>insert into teacher values('T103','JACK BARNES','BTECH','27-
JUN-07','PROFESSOR','TENALI',9985012345,27000,'MTECH');
1 row created.
SQL>insert into teacher values('T104','JANE DOE','MCA','04-JUL
06','ASSISTANT PROFESSOR','VIJAYAWADA',9985045678,29000,
'BTECH');
1 row created.
SQL>insert into teacher values('T105','JOE SHMOE','MBA','16-AUG-
08','ASSOCIATE PROFESSOR','ELURU',9987651308,36000,'MCA');
1 row created.
SQL>insert into teacher values('T106','JON BAKER','MSC(COM)','12-
JAN-03','PROFESSOR','HYDERABAD',8876561308,46000,'MCA');
1 row created.
To show the query execution timing use the following query SQL> Set
timing on;
Retrieve details of teacher before creation of index.
SQL> select *from teacher;
STAF STAFF_NAME QUALIFICAT HIREDATE JOB ADDRESS PH_NUM SALARY DEPARTMENT
T101 SUNITHA MCA 29-06-06 ASSOCIATE PROFESSOR VIJAYAWADA 9985061308 23000 MCA
T102 FRED SMITH MTECH 07-MAR-03 ASSOCIATE PROFESSOR GUNTUR 9985063445 36000 MBA
T103 JACK BARNES BTECH 27-JUN-07 PROFESSOR TENALI 9985012345 27000 MTECH
T104 JANE DOE MCA 04-JUL-06 ASSISTANT PROFESSOR VIJAYAWADA 9985045678 29000 BTECH
T105 JOE SHMOE MBA 16-AUG-08 ASSOCIATE PROFESSOR ELURU 9987651308 36000 MCA
T106 JON BAKER MSC(COM) 12-JAN-03 PROFESSOR HYDERABAD 8876561308 46000 MCA
Index creation:
SQL> create index teacher_job_ind on teacher(job);
Index created.
Elapsed: 00:00:00.00
Retrieve details of teacher after creation of index.
SQL> select *from teacher;
STAF STAFF_NAME QUALIFICAT HIREDATE JOB ADDRESS PH_NUM SALARY DEPARTMENT
T101 SUNITHA MCA 29-06-06 ASSOCIATE PROFESSOR VIJAYAWADA 9985061308 23000 MCA
T102 FRED SMITH MTECH 07-MAR-03 ASSOCIATE PROFESSOR GUNTUR 9985063445 36000 MBA
T103 JACK BARNES BTECH 27-JUN-07 PROFESSOR TENALI 9985012345 27000 MTECH
T104 JANE DOE MCA 04-JUL-06 ASSISTANT PROFESSOR VIJAYAWADA 9985045678 29000 BTECH
T105 JOE SHMOE MBA 16-AUG-08 ASSOCIATE PROFESSOR ELURU 9987651308 36000 MCA
T106 JON BAKER MSC(COM) 12-JAN-03 PROFESSOR HYDERABAD 8876561308 46000 MCA
