Indexes in SQL Server and Oracle (1)

Bkjia.com exclusive Article] This article introduces the indexes and common index types of SQL Server and Oracle. We hope to deepen our understanding of the two database index applications through the comparison method.

1. Index in SQL Server
 
An index is a disk structure associated with a table or view, which can accelerate the speed of retrieving rows from a table or view. An index contains keys generated by one or more columns in a table or view. These keys are stored in a structure B tree), so that SQL Server can quickly and effectively find the rows associated with the key value.

A table or view can contain the following types of indexes:

Clustered Index
The clustered index sorts and stores the data rows according to the key values of the data rows in the table or view. The index definition contains clustered index columns. Each table can have only one clustered index, because data rows can only be sorted in one order.

Data rows in the table are stored in order only when the table contains clustered indexes. If a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in a unordered structure called a heap.
Almost every table defines clustered indexes for columns to implement the following functions:
1. It can be used for frequently-used queries.
2. Provide High uniqueness.

Before creating a clustered index, you should first understand how data is accessed. Consider using clustered indexes for queries with the following features:

Return a series of values using operators such as BETWEEN,>, >=, <, and <=.
When a clustered index is used to locate the row that contains the first value, the rows that contain the subsequent index value are physically adjacent. For example, if a query retrieves records between a series of purchase order numbers, the clustered index of the PurchaseOrderNumber column can quickly locate the rows containing the start purchase order number, and then retrieve all consecutive rows in the table, until the final purchase order number is retrieved.

Returns a large result set.

The JOIN clause is generally used as a foreign key column.

Use the order by or group by clause.
The index of the column specified in the order by or group by clause does not need to be sorted BY the database engine because these rows have been sorted. This improves the query performance.

Clustered indexes are not applicable to columns with the following attributes:

Columns frequently changed
This will move the entire row because the database engine must physically keep the data values in the row. Pay special attention to this because the data in the large-capacity transaction processing system is usually variable.

Width key
A wide key is a combination of several or several large columns. All non-clustered indexes use the key values in the clustered index as the search key. Any non-clustered index defined for the same table will be much larger, because the non-clustered index item contains the clustering key and the key column defined for this non-clustered index.

Non-clustered Index
Non-clustered indexes have a structure independent of data rows. A non-clustered index contains a non-clustered index key value, and each key value item has a pointer to a data row containing the key value.

A pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data page is stored in the heap or clustering table. For a heap, the row positioner is a pointer to a row. For clustered tables, the row locator is the clustered index key.
In SQL Server 2005, you can add non-key columns to the leaf level of a non-clustered index to skip the existing index key limit of 900 bytes and 16 key columns ), perform index queries within the full range.
Non-clustered indexes and clustered indexes have the same B-tree structure, and the significant difference between them lies in the following two points:

1. Data rows in the base table are not sorted and stored in the order of non-clustered keys.
2. The leaf layer of a non-clustered index is composed of index pages rather than data pages.

Pay attention to the database features when designing non-clustered indexes:

Databases or tables with low Update Requirements but large amounts of data can benefit from many non-clustered indexes to improve query performance.
Decision Support System applications and databases that primarily contain read-only data can benefit from many non-clustered indexes. The query optimizer has more available indexes to determine the fastest access method, and the low update feature of the database means that index maintenance will not degrade performance.

Online transaction processing applications and databases that contain a large number of update Tables should avoid using too many indexes. In addition, the index should be narrow, that is, the fewer columns, the better.
If a table has a large number of indexes, the performance of INSERT, UPDATE, and DELETE statements will be affected, because all indexes must be adjusted accordingly as the table data changes.

Unique Index
The unique index ensures that the index key does not contain duplicate values. Therefore, each row in a table or view is unique to some extent.

Both clustered and non-clustered indexes can be unique indexes.
 
Include column Index
A non-clustered index that extends to include not only key columns but also non-key columns.