What is the difference between clustered and non-clustered indexes?

Vaibhav Kothia May 26, 2023 0 Comments

Clustered indexes are physical structures that determine the order in which data is stored in a table. They are used to improve the speed of data retrieval from the table. For example, if you have a table of customer orders, you can create a clustered index on the order date field to quickly locate orders within a certain date range.

Non-clustered indexes are logical structures that create a separate copy of the data from the table. They are used to improve the speed of data retrieval from the table. For example, if you have a table of customer orders, you can create a non-clustered index on the customer name field to quickly locate orders by customer name.

What are the different types of indexes in Oracle?

Vaibhav Kothia May 26, 2023 0 Comments

1. Unique Index: A unique index is a type of index that ensures that all values stored in the index key are unique. For example, if you have a table with a column that stores employee IDs, you can create a unique index on that column to ensure that no two employees have the same ID.

2. Bitmap Index: A bitmap index is a type of index that uses a bitmap to store the index keys. Each bit in the bitmap corresponds to a value in the index key. For example, if you have a table with a column that stores gender (male or female), you can create a bitmap index on that column to quickly find all the males or females in the table.

3. Composite Index: A composite index is a type of index that is made up of multiple columns. For example, if you have a table with columns for first name, last name, and age, you can create a composite index on those three columns to quickly find all the records with a particular combination of first name, last name, and age.

4. Function-Based Index: A function-based index is a type of index that is based on a function or expression. For example, if you have a table with a column that stores dates, you can create a function-based index on that column to quickly find all the records with a particular date range.

5. Spatial Index: A spatial index is a type of index that is used to quickly find records that are related to a particular location. For example, if you have a table with columns for latitude and longitude, you can create a spatial index on those columns to quickly find all the records that are within a certain distance of a particular location.