INDEXES

Indexes are schema objects that are used to speed up data retrieval. Even though they are separate schema objects, when you drop a table to which indexes are associated, also indexes get dropped.
They are created automatically when you create primary key and unique key constraint or manually using
CREATE INDEX. For the full syntax check this LINK

CREATE [UNIQUE | BITMAP] INDEX [ schema.]indexname
ON [schema.]tablename (column [, column…] ) ;

Most simple is:

CREATE INDEX tbl1_col1_idx
ON table1(column1);

There are two main types of indexes.

B*Tree index
B*Tree, which is default index type and can be either unique or nonunique. A unique index will not permit insertion of two rows with the same key values; a nonunique index will permit as many rows as you want with the same values. Nonunique is the default. NULLs do not go into B*Tree indexes.

Bitmap index
In general, bitmap indexes should be used if:
– The cardinality (the number of distinct values) in the column is low (such as male/female), and
– The number of rows in the table is high, and
– The column is used in Boolean algebra (AND/OR/NOT) operations
NULLs are included in bitmap indexes.