Description of Oracle database index and SQL Server

Oracle Database indexes are the structures on disks associated with tables or views. We can retrieve rows from tables or views more quickly. Oracle Database indexes mainly contain keys generated by one or more columns in a table or related view. These keys are stored in a structure (B tree ).

1. Index in SQL Server

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 Oracle database 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 Oracle database 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 is recorded between a series of purchase order numbers, the clustered index of the PurchaseOrderNumber column can quickly locate the row 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 Oracle database index of the columns specified in the order by or group by clause makes it unnecessary for the database engine to sort data 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 a non-key column to the leaf level of the non-clustered Oracle database index to skip the existing index key limit (900 bytes and 16 key columns ), perform index queries within the full range.

Non-clustered indexes and clustered indexes share the same B-tree structure. 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 Oracle database 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.

Index coverage

The columns used by the SELECT and WHERE clauses in the query are also non-clustered indexes. In this way, you can retrieve data faster, because all information can directly come from the index page, so that SQL Server can avoid accessing the data page. With an independent index file group, you can access data as quickly as possible.

See the following example:

A. Create A simple non-clustered index the following example creates A non-clustered index for the VendorID column of the Purchasing. ProductVendor table.

 
 

  
  
USE AdventureWorks;  
  
  
GO  
  
  
CREATE INDEX IX_ProductVendor_VendorID  
  
  
ON Purchasing.ProductVendor (VendorID);  
  
  
GO  
 
 

B. Create a simple non-clustered Oracle database index

The following example creates a non-clustered composite index for the SalesQuota and SalesYTD columns of the Sales. SalesPerson table.

 
 

  
  
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD  
  
  
ON Sales.SalesPerson (SalesQuota, SalesYTD);  
  
  
GO  
 
 

C. Create a unique non-clustered Index

The following example creates a unique non-clustered index for the Name column of the Production. UnitMeasure table. This index will force the data in the Name column to be unique.

 
 

  
  
USE AdventureWorks;  
  
  
GO  
  
  
CREATE UNIQUE INDEX AK_UnitMeasure_Name  
  
  
ON Production.UnitMeasure(Name);  
  
  
GO  
 
 

The SQL Server 2005 database engine automatically maintains indexes whenever basic data is inserted, updated, or deleted. Over time, these modifications may cause information in the index to be dispersed in the database (containing fragments ). When the logical sorting (based on the key value) in the page contained in the index does not match the physical sorting in the data file, fragments exist.

A large number of shards of indexes may reduce query performance, resulting in slow application response. At this time, we need to re-organize and re-generate the cable reference. Re-indexing will delete the index and create a new index. In this process, fragments will be deleted, and the disk space will be reclaimed by setting the compression page with the specified or existing fill factor, and the index rows will be re-ordered on consecutive pages (new pages will be allocated as needed ). This reduces the number of page reads required to obtain the requested data and improves disk performance.

You can use the following methods to regenerate clustered indexes and non-clustered Oracle Database indexes:

Alter index with REBUILD clause. This statement replaces the dbcc dbreindex statement.

Create index with the DROP_EXISTING clause.

Example:

A. regenerate the index

The following example Re-generates a single index.

 
 

  
  
USE AdventureWorks;  
  
  
GO  
  
  
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee  
  
  
REBUILD;  
  
  
GO  
 
 

B. regenerate all indexes of the table and specify the options.

The following example specifies the ALL keyword. This will re-generate all indexes associated with the table. Three options are specified.

 
 

  
  
ALTER INDEX ALL ON Production.Product  
  
  
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,  
  
  
STATISTICS_NORECOMPUTE = ON);  
  
  
GO  
 
 

2 Oracle Database indexes in Oracle

An index is a database object used by Oracle to retrieve data in a accelerated table.

You can consider using indexes in the following situations:

1) large table

2) primary key (automatic index)

3) single-key columns (Automatic Indexing)

4) Foreign key column (automatic index)

5) columns frequently used in the WHERE clause in large tables

6) columns used in the order by or group by clause.

7) return at least 20% rows of queries in the table

8) columns that do not contain null values.