Clustered Index vs. Non-Clustered Index in Oracle
Oracle database provides two types of indexes - clustered and non-clustered indexes. Both of them serve the same purpose of improving query performance by reducing the number of disk I/O operations. However, there are a few differences between them which determine their usage.
Clustered Index in Oracle:
A clustered index is created on a table which orders the data physically on the disk based on the indexed column. Simply put, the clustered index determines the order in which the data is stored on the disk. As a result, it stores the actual data rows in the index structure. Therefore, a table can have only one clustered index. For example, consider an 'Employee' table with columns Name, Age, and Salary. If we create a clustered index on 'Salary' column, the data rows will be physically ordered on the disk based on the salaries. Hence, when we query the table using the 'Salary' column, it will result in fewer disk I/O operations.
Non-Clustered Index in Oracle:
A non-clustered index, on the other hand, does not store the actual data rows in the index structure. Instead, it stores a reference to the storage location of the data. As a result, a table can have multiple non-clustered indexes. For example, let's consider the same 'Employee' table and create a non-clustered index on the 'Age' column. The index structure will contain an ordered list of all the distinct ages present in the table along with a reference to the location of their corresponding data rows. Therefore, when we query the table using the 'Age' column, it will result in fewer disk I/O operations.In summary, a clustered index determines the way the data is physically stored on the disk, whereas a non-clustered index contains only a reference to the location of the data on the disk.
Citations: