Learn "Index" with Example

Index in 7 points :

1. Index is used for faster retrieval of rows from a table. 
2. An index can be created explicitly by using the CREATE INDEX statement or implicitly by Oracle.
   The oracle server uses the index to search for a row rather than scanning through the entire table. 
3. Indexing reduces search time and disk input/output.
4. Creating and removing an index does not affect the table at all.
5. When a table is dropped,all indexes based on that table are also removed.
6. Implicit indexes are created when the primary key or unique constraint is defined. Such indexes get the name of the constraint .
7. User can create explicit indexes based on non-primary key or non unique columns or on any combination of columns for faster table access.

The general syntax is : 

CREATE INDEX indexname ON tablename(columnname1,columnname1);

By using the above syntax the columns can hold duplicate values. To create unique index,the keyword UNIQUE should be included : 

CREATE UNIQUE INDEX indexname ON tablename(columnname1,columnname1);

EXAMPLE :

CREATE INDEX stu_idx ON student(last,first);
Now suppose we want to see the index for table student :
SELECT index_name, table_name, FROM user_indexes WHERE table_name =
'student';

Exercise :

Create an index to search students faster based on their ID.

CREATE INDEX index02 ON employee24(fname,lname);