Indexing:
Indexing serves as a gateway to rapid data retrieval in a database. Its primary objective is
to reduce the number of disk accesses required when processing queries, making it a valuable
asset for improving database performance.
Types of Indexing
*Ordered Indexing:* This type of indexing maintains a sorted order of index entries based
on the indexed field, making range queries and searching much faster.
*Primary Indexing:* The primary index is based on the primary key of the table and provides
a direct link to the actual data.
*Secondary Indexing:* Unlike primary indexing, this type creates indexes on non-primary
key fields, enhancing the search capabilities of the database.
*Clustered Indexing:* In clustered indexing, the data in the table is physically sorted based
on the indexed field, leading to improved query performance for certain scenarios.
Bitmap Indexing:
Bitmap Indexing is a data indexing technique used in database management systems (DBMS)
to improve the performance of read- only queries that involve large datasets.
In a bitmap index, each distinct value in a
column is assigned a bit vector that represents the presence or absence of that value in each
row of the table. The bit vector contains one bit for each row in the table, where a set bit
indicates the presence of the corresponding value in the row, and a cleared bit indicates the
absence of the value.
Syntax:
CREATE BITMAP INDEX Index_Name ON Table_Name (Column_Name);
Ex:
CREATE BITMAP INDEX index_New_Emp ON Employee (New_Emp);
Advantages of Bitmap Indexing
• Efficiency in terms of insertion deletion and updation.
• Faster retrieval of records
Disadvantages of Bitmap Indexing
• Only suitable for large tables
Indexing serves as a gateway to rapid data retrieval in a database. Its primary objective is
to reduce the number of disk accesses required when processing queries, making it a valuable
asset for improving database performance.
Types of Indexing
*Ordered Indexing:* This type of indexing maintains a sorted order of index entries based
on the indexed field, making range queries and searching much faster.
*Primary Indexing:* The primary index is based on the primary key of the table and provides
a direct link to the actual data.
*Secondary Indexing:* Unlike primary indexing, this type creates indexes on non-primary
key fields, enhancing the search capabilities of the database.
*Clustered Indexing:* In clustered indexing, the data in the table is physically sorted based
on the indexed field, leading to improved query performance for certain scenarios.
Bitmap Indexing:
Bitmap Indexing is a data indexing technique used in database management systems (DBMS)
to improve the performance of read- only queries that involve large datasets.
In a bitmap index, each distinct value in a
column is assigned a bit vector that represents the presence or absence of that value in each
row of the table. The bit vector contains one bit for each row in the table, where a set bit
indicates the presence of the corresponding value in the row, and a cleared bit indicates the
absence of the value.
Syntax:
CREATE BITMAP INDEX Index_Name ON Table_Name (Column_Name);
Ex:
CREATE BITMAP INDEX index_New_Emp ON Employee (New_Emp);
Advantages of Bitmap Indexing
• Efficiency in terms of insertion deletion and updation.
• Faster retrieval of records
Disadvantages of Bitmap Indexing
• Only suitable for large tables
