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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: