Oracle Database object_index

Oracle Database object_index

Query is the most frequently accessed table.

When querying data, few users are willing to query all the data in the table unless they want to process the entire table.
In general, you always query a part of the data in the table.

In a SELECT statement, you must specify the query conditions using the WHERE clause to obtain all data that meets the conditions.
If you can query the required data in a very small range, rather than in the full table range, it will reduce unnecessary disks by 1/0, and the query speed will undoubtedly be greatly accelerated.
The Quick query method is index.

Basic concepts of Indexes

An index is a database object created on a table. It is mainly used to accelerate the query of a table.

The rational use of indexes can greatly reduce the number of disk accesses, thus greatly improving the database performance.
The main purpose of using indexes is to speed up the query. In addition, indexes can also be used as uniqueness constraints.

If a unique index is created on a column in the table, the system automatically creates a unique constraint on the column to ensure that the data inserted into the column is unique.

How does an index accelerate the query speed?

It turns out that the index is based on a column or several columns in the Table. Such a column is called an index column.
When creating an index, the database server sorts the data in the index column and stores the sorting results in the storage space occupied by the index.
When querying data, the database server first queries the data in the index and then the data in the table.
Because the data in the index is sorted in advance, you only need a small number of searches to find the required data.

The index not only stores the data on the index column, but also stores the value of a ROWID.
ROWID is a pseudo column in the table and is automatically added by the database server. each row of data in the table has a ROWID value, which indicates the ID of this row, that is, the physical location of a row of data in the bucket.
When accessing the data in a table, you must find the actual storage location of the data based on the value of this pseudo column, and then access the data.
Because the data on the index column has been sorted, this row of data can be quickly found in the index, and then this row of data can be found directly in the table based on ROWID.

Note that the table is independent from the index. No matter how many indexes are created on the table, no matter what sort the index is on the table, the data in the table will not change.

When querying a row of data, first query the row ID of the row in the index, and then find the data in the table based on the row ID.
Because the data in the index is sorted, the semi-query method is used to search for data, so as to achieve quick search.

Using the semi-query method to search for data in an index is similar to traversing a binary tree. First, it is compared with the root node. If it is the same as the searched data, the query is completed after one access.
If the data to be searched is smaller than the root node, search in the left subtree of the root node; otherwise, search in the right subtree. In this way, the search range is reduced by half.
In this way, the search range is reduced by half each time, and the search continues in the remaining nodes until the required data is found.

The index can be divided into a unique index and a non-unique index based on whether the index column value can be repeated. The unique index ensures that the index column value is unique.
Based on the number of columns in the index column, the index can be divided into a single column index and a composite index.
According to the data organization of the index column, indexes can be divided into B + tree indexes, bitmap indexes, reverse indexes, and function-based indexes. Here we only introduce the usage of B + tree indexes.

The rational use of indexes can greatly improve the query performance of the database, but unreasonable indexes can reduce the performance of the database, especially when performing DML operations.
When an index is created, the data in the table is sorted. If you perform the DML operation on the table, the data in the table changes, and the data in the index is also re-ordered, if multiple indexes are created on the table, the data in each index must be sorted again.

The overhead of such sorting is very large, especially when the table is very large.

Indexes are one of the most effective ways to improve the performance of relational database systems. Using indexes can reduce the number of disk accesses and greatly improve the system performance.
However, you must fully consider the operations performed on the table when designing the index. If the primary operation on the table is the query operation, you can create an index on the table. If the operation is performed frequently on the table
The index causes more system overhead.

In general, the following principles should be followed to create an index:
· If only a few rows in the table are selected for each query, an index should be created.

· If frequent DML operations are required on a table, do not create an index.
· Try not to create an index on columns with many duplicate values.
· Do not create indexes on small tables.

When querying data in a small table, the speed may be fast enough. If an index is created, the query speed is not very helpful, but requires a certain amount of system overhead.

Index creation, modification, and deletion

Indexes can be created automatically or manually. If a primary key constraint or
The database server automatically creates a unique index on these columns.
The name is the same.
To CREATE an INDEX manually, run the SQL command. The command to CREATE an INDEX is CREATE INDEX. A user can
CREATE an INDEX in your own mode, as long as the user has the create index system permission. If you want
To CREATE an INDEX, you must have the create any index system permission.
The syntax format of the create index Command is:

Create index name ON table name (column 1, column 2 ...);

In this index, there is only one index column. Such an index is called a single column index.

To create a composite index, you must specify multiple columns.

For example:

Create index ind_de_dn ON test (deptno, dname );

Composite indexes are mainly used in query statements with multiple conditions.

By default, the created index is not unique, that is, duplicate values are allowed in the index column of the table.
If you want to create a UNIQUE index, you need to use the keyword UNIQUE.

For example:

Create unique index ind_de ON test (deptno );

Generally, when specifying columns in an index, follow the following principles:

Create an index on a column that is frequently used in the WHERE clause.
Try not to create an index on a column with a large number of duplicate values.
A column with unique values is the best choice for creating an index, but whether to create an index on this column depends on whether the column is frequently queried.
If the conditions in the WHERE clause involve multiple columns, you can consider creating a composite index for these columns.

As mentioned above, a well-designed index will improve the system performance, while an unreasonable index will reduce the system performance.
Therefore, during database operation, SQL Trace should be used frequently to check whether the index is used and whether the index improves the database performance as expected.
If an index is frequently used, or an index is only helpful for improving the database performance, you can consider deleting this index.

Query index information

There are two index-related data dictionaries: user_indexes and user_ind_columns.
For example, you can run the following query statement to query the index type, the table based on, and whether the index is unique based on information such as reverse status and status:

SELECT index_type, table_name, status FROM user_indexes WHERE index_name = 'ind _ de ';

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

SELECT table_name, column_name FROM user_ind_columns WHERE index_name = 'ind _ de ';

The definitions and meanings of the columns of user_ind_columns in data dictionary view 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 is sorted descending on disk, otherwise ASC

Note: cluster Table;

Attribute

The definitions and meanings of common columns in user_indexes 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

Note: The data dictionary view has many columns on user_indexes.