Oracle --- Index

What Is an Index?
An Oracle Server index is a schema object that can speed up the retrieval of rows by using a pointer. Indexes can be created explicitly or automatically.  If you do not have an index on the column, then a full table scan will occur.
An index provides direct and fast access to rows in a table. Its purpose is to reduce the necessity of disk I/O by using an indexed path to locate data quickly. The index is automatically used and maintained by the Oracle Server. Once an index is created, no direct activity is required by the user.
Indexes are logically and physically independent of the table they index. This means that they can be created or dropped at any time and have no effect on the base tables or other indexes.

index can be create automatically or manually.

Automatically
A unique index is created automatically when you define a PRIMARY KEY or UNIQUE key constraint in a table definition.
Manually
Users can create nonunique indexes on columns to speed up access time to the rows.
SQL> CREATE INDEX  emp_ename_idx
  2  ON    emp(ename);
Index created.

When to Create an Index
The column is used frequently in the WHERE clause or in a join condition.
The column contains a wide range of values.
The column contains a large number of null values.
Two or more columns are frequently used together in a WHERE clause or join condition.
The table is large and most queries are expected to retrieve less than 2-4% of the rows.
Remember that if you want to enforce uniqueness, you should define a unique constraint in the table definition. Then, a unique index is automatically created.

confirm index
SQL> SELECT ic.index_name, ic.column_name,
  2  ic.column_position col_pos,ix.uniqueness
  3  FROM user_indexes ix, user_ind_columns ic
  4  WHERE ic.index_name = ix.index_name
  5  AND  ic.table_name = 'EMP';

remove index
SQL> DROP INDEX emp_ename_idx;
Index dropped.