Cluster vs Non Cluster Index
Indexes are very important in any database. They are used to improve the performance of retrieving data from tables. They are logically and physically independent of the data in the associated tables. Therefore, indexes can be drop, recreate and rebuild without affecting the data of the base tables. Oracle server can automatically maintain its indexes without any involvement of a DBA, when the related tables are inserted, updated and deleted. There are several index types. Here, are some of them.
1. B-tree indexes
2. Bitmap indexes
3. Function-based indexes
4. Reverse-key indexes
5. B-tree cluster indexes
What is a Non – Cluster Index?
From the above index types, following are non-clustered indexes.
• B-tree index
• Bitmap index
• Function based index
• Reverse-key indexes
B-tree indexes are the most widely used index type of databases. If CREATE INDEX command is issued on the database, without specifying a type, Oracle server creates a b-tree index. When a b-tree index is created on a specific column, oracle server stores the values of the column and keeps a reference to the actual row of the table.
Bitmap indexes are created when the column data is not very selective. That means, the column data has a low cardinality. These are specially designed for data warehouses, and it is not good to use bitmap indexes on highly updatable or transactional tables.
Functional indexes are coming from Oracle 8i. Here, a function is used in the indexed column. Therefore, in a functional index, column data are not sorted in the normal way. It sorts the values of the columns after applying the function. These are very useful when the WHERE close of the select query is used a function.
Reverse-key indexes are a very interesting index type. Let us assume a column contains many unique string data like ‘cityA’, ‘cityB’, ‘cityC’…etc. All the values have a pattern. First four characters are the same and next parts are changed. So when REVERSE key index is created on this column, Oracle will reverse the string and restore it in a b-tree index.
The above-mentioned index types are NON-CLUSTERED indexes. That means, indexed data is stored outside the table, and a sorted reference to the table is kept.
What is a Clustered Index?
Clustered indexes are a special type of indexes. It stores data according to the way of storing table data physically. So, there can’t be many clustered indexes for one table. One table can have only one clustered index.
|
What is the difference between Clustered and Non-Clustered Indexes? 1. Table can have only one clustered index, but there can be up to 249 non-clustered indexes in one table. 2. Clustered index is automatically created when a primary key is created, but a non-clustered index is created when a unique key is created. 3. Logical order of the clustered index matches with the physical order of the table data, but in non-clustered indexes, it does not.
|