PL-SQL
Autonomous Transaction in Oracle PL/SQL: Commit, Rollback
What are TCL Statements in PL/SQL? TCL stands for Transaction Control Statements. It will either save...
An Index is a key built from one or more columns in the database that speeds up fetching rows from the table or view. This key helps a Database like Oracle, SQL Server, MySQL, etc. to find the row associated with key values quickly.
Two types of Indexes are:
In this tutorial, you will learn:
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.
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.
A non-clustering index is defined in the non-ordering field of the table. This type of indexing method helps you to improve the performance of queries that use keys which are not assigned as a primary key. A non-clustered index allows you to add a unique key for a table.
In the example below, SalesOrderDetailID is the clustered index. Sample query to retrieve data
SELECT CarrierTrackingNumber, UnitPrice FROM SalesData WHERE SalesOrderDetailID = 6
In the below example, a non-clusted index is created on OrderQty and ProductID as follows
CREATE INDEX myIndex ON SalesData (ProductID, OrderQty)
The following query will be retrieved faster compared to the clustered index.
SELECT Product ID, OrderQty FROM SalesData WHERE ProductID = 714
Parameters | Clustered | Non-clustered |
---|---|---|
Use for | You can sort the records and store clustered index physically in memory as per the order. | A non-clustered index helps you to creates a logical order for data rows and uses pointers for physical data files. |
Storing method | Allows you to stores data pages in the leaf nodes of the index. | This indexing method never stores data pages in the leaf nodes of the index. |
Size | The size of the clustered index is quite large. | The size of the non-clustered index is small compared to the clustered index. |
Data accessing | Faster | Slower compared to the clustered index |
Additional disk space | Not Required | Required to store the index separately |
Type of key | By Default Primary Keys Of The Table is a Clustered Index. | It can be used with unique constraint on the table which acts as a composite key. |
Main feature | A clustered index can improve the performance of data retrieval. | It should be created on columns which are used in joins. |
The pros/benefits of the clustered index are:
Pros of using non-clustered index are:
Here, are cons/drawbacks of using clustered index:
Here, are cons/drawbacks of using non-clustered index:
What are TCL Statements in PL/SQL? TCL stands for Transaction Control Statements. It will either save...
{loadposition top-ads-automation-testing-tools} There are many SQL management tools available in...
$20.20 $9.99 for today 4.6 (119 ratings) Key Highlights of SQLite PDF 159+ pages eBook Designed for...
Aggregate Functions are all about Performing calculations on multiple rows Of a single column of a...
What is While Loop? WHILE loop statement works similar to the Basic loop statement except the EXIT...
What is Record Type? A Record type is a complex data type which allows the programmer to create a...