Oracle Database Object _ Index

Queries are the most frequent accesses made on a table.

When querying data, few users are willing to query all the data in the table, unless the entire table is processed.
In general, users are always querying a subset of the data in a table.

In a SELECT statement, you typically specify a query condition through a WHERE clause to obtain all the data that satisfies that condition.
If you can query the required data in a very small scope, instead of querying across the table, you will reduce the number of unnecessary disk 1/0 , and the query speed will undoubtedly be greatly accelerated.
The way to provide this quick query is the index.

Basic concepts of indexing

An index is a database object that is built on a table and is used primarily to speed up query operations on tables.

Proper use of indexes can greatly reduce the number of disk accesses, thus greatly improving the performance of the database.
The primary purpose of using indexes is to speed up queries, and indexes can also be used as uniqueness constraints.

If a unique index is established on a column of a table, the system automatically establishes a uniqueness constraint on that column, which guarantees that the data inserted into the column is unique.

How does an index speed up the query?

Originally, an index is built on a column or columns in a table, and such a column is called an indexed column.
When you create an index, the database server sorts the data for the indexed columns and stores the results of the sorting in the storage space that the index occupies.
When querying data, the database server first queries the index and then queries the table.
Because the data in the index is sorted beforehand, only a small number of lookups are needed to find the data you need.

In the index, not only the data on the indexed column is stored, but also a ROWID value is stored.
rowID is a pseudo-column in a table that is automatically added by the database server, and each row of data in the table has a ROWID value that represents the identity of the row, the physical location of a row of data in the storage space.
When you access data in a table, you find the actual storage location of the data based on the value of the pseudo-column, and then access it.
Because the data on the indexed column is already sorted, the row can be found quickly in the index, and the data can be found directly in the table based on ROWID.

It is important to note that the table is indexed independently, regardless of how many indexes are established on the table, and no matter what sort of data the index has on the table, there is no change in the data in the table.

When querying a row of data, the row identity of the row is queried first in the index, and then the data in the table is found based on the row identity.
Because the data in the index is sorted, the binary lookup method is used to find the data to achieve the purpose of quick find.

The process of finding data in an index using the binary lookup method is similar to traversing a binary tree, first compared to the root node, and if the data is the same as the lookup, a single access completes the query.
If the data you are looking for is smaller than the root node, look in the left subtree of the root node, otherwise look in the right subtree, and the scope of the lookup is reduced by half.
In this way, the lookup range is reduced by half each time, and then the remaining nodes are searched until the desired data is found.

Indexes can be divided into unique and non-unique indexes by whether the values of indexed columns allow repetition, where the uniqueness index guarantees that the values of indexed columns are unique.
By the number of columns in the indexed column, the index can be divided into single-column and composite indexes.
The index can be divided into B + tree indexes, bitmap indexes, reverse indexes, and function-based indexes, in terms of how the data for indexed columns is organized, only the use of B + Tree indexes is described here.

Using indexes reasonably can greatly improve the query performance of the database, but unreasonable indexes can degrade the performance of the database, especially in the case of DML operations.
When the index is created, the data in the table is sorted, and if the table is DML, the data in the table changes, and the data in the index is reordered, and if multiple indexes are established on the table, the data in each index is reordered.

This sort of overhead is very large, especially when the table is very large.

Index is one of the effective methods to improve the performance of relational database system, and the use of indexes can reduce the number of disk accesses, which greatly improves the system.
However, when you design an index, you must fully consider the operations that are performed on the table, and if the primary action on the table is a query operation, consider indexing on the table if you want to make frequent
DML operations, the index can cause more overhead.

In general, creating an index follows these guidelines:
• If each query selects only a small number of rows in the table, the index should be established.

• Do not index if frequent DML operations are required on the table.
• Try not to index on columns that have many duplicate values.
• Do not create indexes on too small tables.

When querying data in a small table, the speed may be fast enough, and if you build an index, the query speed is not only not much help, but a certain amount of system overhead.

Creation, modification, and deletion of indexes

Indexes can be created automatically or manually . If a primary KEY constraint is established on one or several columns of the table, or
Uniqueness constraints , the database server will automatically establish a unique index on these columns, where the name of the index and the constraint
Name is the same.
Creating an index manually requires executing the SQL command, which creates index . A user can
Create index in your own mode, as long as the user has the CREATE INDEX system permission. If you want to have other users
mode, you need to have the Create any index for this system permission.
The syntax format for the CREATE INDEX command is:

CREATE index name on table name (column 1, column 2 ...). ）;

In this index, there is only one index column, and such an index is called a single-column index.

If you want to create a composite index, you specify more than one column.

For example:

CREATE INDEX ind_de_dn on test (Deptno, dname);

Composite indexes are primarily used in query statements with multiple criteria.

By default, the created index is non-unique, that is, duplicate values are allowed on the index columns in the table.
If you are creating a unique index, you need to use the keyword unique.

For example:

CREATE UNIQUE INDEX ind_de on test (DEPTNO);

In general, when you specify columns in an index, you follow these guidelines:

Creates an index on a column that is used frequently in the WHERE clause.
Try not to create an index on a column that has a large number of duplicate values.
A column with unique values is the best way to make an index, but whether or not to index the column depends on whether the column is frequently queried.
If the conditions in the WHERE clause involve more than one column, you might consider creating a composite index on those columns.

As mentioned earlier, a properly designed index will improve the performance of the system, while an unreasonable index will degrade the system performance.
Therefore, in the process of running the database, we often use SQL Trace to check whether the index is being used, and to check whether the index improves the performance of the database as expected.
If an index is used frequently, or if an index improves the performance of the database with minimal help or even help, consider deleting the index.

Query for index information

There are two index-related data dictionaries: user_indexes and user_ind_columns .
For example, to query the type of an index, the table on which it is based, whether a unique index is in reverse state, and so on, you can execute the following query statement:

SELECT Index_type, table_name, status from User_indexes WHERE index_name= ' Ind_de ';

The following query statement is used to obtain the columns on the table and table on which the index is based:

SELECT table_name, column_name from User_ind_columns WHERE index_name= ' Ind_de ';

the definitions and meanings of the data dictionary view user_ind_columns columns are as follows :

Name meaning

Index_name Index NAME

TABLE_NAME Table or cluster NAME

column_name column NAME or attribute of Object Column

Column_position POSITION of column or attribute within index

Column_length Maximum LENGTH of the column or attribute, in bytes

Char_length Maximum LENGTH of the column or attribute, in characters

Descend DESC If this column was sorted descending on disk, otherwise ASC

Note:cluster cluster table;

Attribute Property

Data dictionary view user_indexes the definitions and meanings of common columns are as follows:

Name meaning

Index_name Index Name

Index_type Index Type

Table_owner object Owner

TABLE_NAME Object Name

Table_type Object type

Status state

Note: There are a number of data dictionary view user_indexes listed above.

Oracle Database Object _ Index