Clustered and Non-clustered indexes

What are Clusters in Oracle

Clusters are an optional method of storing table data. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.

For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT tables, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks.

Because clusters store related rows of different tables together in the same data blocks, properly used clusters offers these benefits:

• Disk I/O is reduced for joins of clustered tables.

• Access time improves for joins of clustered tables.

• In a cluster, a cluster key value is the value of the cluster key columns for a particular row. Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value. Therefore, less storage is required to store related table and index data in a cluster than is necessary in nonclustered table format.

Clusters in Other databases(SQL Server)

SQL Server has two types of indexes: clustered index and non-clustered index.

Clustered index

Cluster index is a type of index which sorts the data rows in the table on their key values. In the Database, there is only one clustered index per table.

A clustered index defines the order in which data is stored in the table which can be sorted in only one way. So, there can be an only a single clustered index for every table. In an RDBMS, usually, the primary key allows you to create a clustered index based on that specific column.

When you create a table with a primary key, SQL Server automatically creates a corresponding clustered index based on columns included in the primary key.

For below table, a clustered index will be created automatically.

CREATE TABLE part_prices(
    part_id int,
    valid_from date,
    price decimal(18,4) not null,
    PRIMARY KEY(part_id, valid_from) 
);

If you add a primary key constraint to an existing table that already has a clustered index, SQL Server will enforce the primary key using a non-clustered index:

ALTER TABLE parts
ADD PRIMARY KEY(part_id);

SQL Server will create a non-clustered index for the primary key.

SQL Server CREATE CLUSTERED INDEX statement to create a clustered index

CREATE CLUSTERED INDEX index_name
ON schema_name.table_name (column_list);

Non-clustered index

A Non-clustered index stores the data at one location and indices at another location. The index contains pointers to the location of that data. A single table can have many non-clustered indexes as an index in the non-clustered index is stored in different places.

For example, a book can have more than one index, one at the beginning which displays the contents of a book unit wise while the second index shows the index of terms in alphabetical order.

Difference Between Clustered and Non-clustered Indexes

• Cluster index is a type of index that sorts the data rows in the table on their key values whereas the Non-clustered index stores the data at one location and indices at another location.
• Clustered index stores data pages in the leaf nodes of the index while Non-clustered index method never stores data pages in the leaf nodes of the index.
• Cluster index doesn’t require additional disk space whereas the Non-clustered index requires additional disk space.
• Cluster index offers faster data accessing, on the other hand, Non-clustered index is slower.

Characteristic of Clustered Index

• Default and sorted data storage
• Use just one or more than one columns for an index
• Helps you to store Data and index together
• Fragmentation
• Operations
• Clustered index scan and index seek
• Key Lookup

Characteristics of Non-clustered Indexes

• Store key values only
• Pointers to Heap/Clustered Index rows
• Allows Secondary data access
• Bridge to the data
• Operations of Index Scan and Index Seek
• You can create a nonclustered index for a table or view
• Every index row in the nonclustered index stores the nonclustered key value and a row locator

Advantages of Clustered Index

• Clustered indexes are an ideal option for range or group by with max, min, count type queries
• In this type of index, a search can go straight to a specific point in data so that you can keep reading sequentially from there.
• Clustered index method uses location mechanism to locate index entry at the start of a range.
• It is an effective method for range searches when a range of search key values is requested.
• Helps you to minimize page transfers and maximize the cache hits.

Advantages of Non-clustered index

• A non-clustering index helps you to retrieves data quickly from the database table.
• Helps you to avoid the overhead cost associated with the clustered index
• A table may have multiple non-clustered indexes in RDBMS. So, it can be used to create more than one index.

Disadvantages of Clustered Index

• Lots of inserts in non-sequential order
• A clustered index creates lots of constant page splits, which includes data page as well as index pages.
• Extra work for SQL for inserts, updates, and deletes.
• A clustered index takes longer time to update records when the fields in the clustered index are changed.
• The leaf nodes mostly contain data pages in the clustered index.

Disadvantages of Non-clustered index

• A non-clustered index helps you to stores data in a logical order but does not allow to sort data rows physically.
• Lookup process on non-clustered index becomes costly.
• Every time the clustering key is updated, a corresponding update is required on the non-clustered index as it stores the clustering key.