Bitmap Index Example:

Table 12-1 shows a portion of a company's employee data.

Table 12-1: Bitmap Index Example

Because NAME, DEPTNO, JOB, SALARY are all low-cardinality columns. That is appropriate to establish bitmap indexes on these columns.  The bitmap index should not be created on EMPLOYEE # since this is a high-cardinality column. In the other words, a unique B*-tree index on this column in order would gives the most efficient retrieval and representation.

Table 12-2: Sample Bitmap

Every entry (or "bit") in the bitmap corresponds to a single row of the EMPLOYEE table. A value of each bit depends upon the values of the corresponding row in the table. For example, the bitmap DEPTNO=10 holds a one as its first bit: this is since the deptno is 10 in the first row of the EMPLOYEE table.

A sample query is given below for a bitmap instance:

SELECT COUNT(*) FROM EMPLOYEE

WHERE JOB='manager' and deptno IN (10,20);

A Bitmap indexes can process this query with great effectiveness through merely counting the number of ones in the resulting bitmap.

Unique indexes

Unique Indexes are indexes which are described on columns that ensure which no two rows can contain the similar values. Instead, it prevents duplication of values.

For instance, unique Key and Primary constraints are by default creates unique index.

Composite Indexes

Composite Indexes are indexes which get created on more than one column. The Sorting is done based on the leading column and then on the subsequent column provided inside the index. They are also known as Concatenation Index.

Creating Indexes

Indexes can be created in the subsequent ways:

Create index <indexname> ON <tablename>(<columnname>);

Example:

CREATE INDEX i1 ON       emp(JOB);

Dropping Indexes

An Index can be dropped through using Drop command.

Syntax:

Drop index <indexname>;

Example:

DROP INDEX I1;