Thu. Jun 30th, 2022

It actually creates a lookup table with column and a pointer to the memory address where a row with this column is actually stored. B Tree data structure is mostly used to store in the indexes. Because of time efficiency in B Trees.

Types of Indexing

Primary Index

Primary Index is an ordered file which is fixed length size with two fields. The first field is the same a primary key and second, filed is pointed to that specific data block. In the primary Index, there is always one to one relationship between the entries in the index table.

Primary Index (Ordered Indexing) is divided into two types

  • Dense Index
  • Sparse Index

Dense Index

In a dense index, a record is created for every search key valued in the database. This helps you to search faster but needs more space to store index records. In this Indexing, method records contain search key value and points to the real record on the disk

Sparse Index

It is an index record that appears for only some of the values in the file. Sparse Index helps you to resolve the issues of dense Indexing in DBMS. In this method of indexing technique, a range of index columns stores the same data block address, and when data needs to be retrieved, the block address will be fetched.

Secondary Index

Secondary index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values. It is also known as a non-clustering index.

Clustering Index

In a clustered index, records themselves are stored in the Index and not pointers.
Sometimes the Index is created on non-primary key columns which might not be unique for each record. In such a situation, you can group two or more columns to get the unique values and create an index which is called clustered Index. This also helps you to identify the record faster.

How we create an index in SQL?

Single column index syntax

CREATE INDEX index_name ON TABLE_NAME (COLUMN_NAME);

CREATE INDEX idx_phone ON EMPLOYEES (PHONE_NUMBER);

Single column index syntax

CREATE INDEX index_name ON TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2,.. COLUMN_NAME);

CREATE INDEX idx_emp_name ON EMPLOYEES (FIRST_NAME, LAST_NAME);

By admin

Leave a Reply

Your email address will not be published.