Database usage-create and classify oracle Indexes

Database usage-create and classify oracle Indexes

Database usage-create and classify oracle Indexes

An index is an optional data structure in a database. It is usually related to tables or clusters. You can create an index on a column or series of tables to Improve the Performance of SQL statements executed on the table. Just as the indexes in this document can help readers quickly locate the required information, Oracle indexes provide a more rapid way to access table data. Correct use of indexes can significantly reduce disk I/O.

Oracle provides multiple types of indexes to complement each other to improve query performance:

· Balance tree index (B-tree index)

· B-tree cluster index)

· Hash cluster index)

· Reverse key indexes)

· Bitmap index)

· Bitmap join index)

· Function-based index)

You do not need to modify the existing SQL statement whether or not the index exists. The index only provides a path for fast data access. Therefore, it only affects the query speed. However, during data editing, indexes are constantly created or updated, the disk overhead of the entire system is very high, which will further affect the operation of the entire system.

Therefore, when we want to create an index, we must ask ourselves, is it really necessary to create this index?

The index is logically and physically independent from its base table. You can create or drop an index at any time without affecting other indexes in the base table or base table. When a user removes an index, all applications can continue to work, but the data access speed may decrease. As an independent data structure, indexes occupy storage space.

After an index is created, it is automatically maintained and used by Oracle. When the data on which the index depends is inserted, updated, or deleted, Oracle automatically reflects the data changes to the relevant index, without additional operations.

Even if new data is inserted into the base table of the index, the query performance of the indexed data remains stable. However, if too many indexes are created on a table, the insertion, update, and deletion performance will be reduced. Because Oracle must modify the index information related to this table at the same time.

The optimizer can use existing indexes to create (build) new indexes. This will accelerate the creation of new indexes.

Unique and non-unique Indexes

An index can be unique or non-unique. Creating a unique index on a table ensures that the index columns (one or multiple columns) of the table do not have duplicate values. Nonunique indexes do not impose such restrictions on index column values.
Oracle recommends that you use the create uniqueindex statement to explicitly CREATE a unique index ). Creating a unique index using a primary key or a unique constraint (unique constraint) cannot guarantee the creation of a new index, and the indexes created using these methods cannot be a unique index.

Composite Index

Composite index (also known as concatenated index) refers to the index created on multiple columns of a table. The columns in the composite index can be arranged randomly, and they do not need to be adjacent in the data table.

If the WHERE clause of a SELECT statement references all the columns of the composite index) or leading portion of thecolumn can improve the query performance. Therefore, the column order in the index definition is very important. Generally, most commonly accessed or most selective columns that are frequently accessed should be placed in front.

A regular (regular) composite index cannot exceed 32 columns, while a bitmap index cannot exceed 30 columns. The total length of a key value in an index should not exceed half of the total available space of a data block.

Index and key

Index and key are different concepts, but these two terms are often mixed. An index is a data structure actually stored in a database. You can create, modify, or drop an index using an SQL statement ). Indexes provide a way to quickly access table data. The key is just a logical concept. The concept of keys is mainly used in the integrity constraint function of Oracle. integrity constraints are used to guarantee business rules in the database ).

Because Oracle also uses indexes to implement certain integrity constraints, the indexes and keys are often mixed. Do not confuse them.

Index and null value

For two or multiple rows of a data table, if all non-NULL values in the index column (key column) are identical (identical ), in the index, these rows are considered the same; otherwise, these rows are considered different in the index. Therefore, using the UNIQUE index can avoid the same row containing NULL. The above discussion does not include the case where all column values in the index column are NULL.

Oracle does not add all data rows with NULL index columns (key column) to the index. However, bitmap index is an exception. When all column values of the cluster key are NULL, it is also an exception.

Function Index

If a function or expression uses one or more columns of a table, you can create an index for the table based on these functions or expressions, such an Index is called a Function-Based Index ). Function indexes can calculate the value of a function or expression and store it in the index. The function indexes created by the user can be B-tree indexes or bitmap indexes.

The function used to create an index can be a mathematical expression (arithmetic expression), a PL/SQL function (PL/SQL function), and a package function ), C External call (C callout), or SQL function expression. The function used to create an index cannot contain any aggregate function. If the function is a user-defined function, the DETERMINISTIC keyword must be used in the Declaration. If you create a function index on a column using the object type, you can use the method of this object as a function, such as the map method of this object. You cannot create function indexes on columns of the Data Type LOB, REF, or nested table, or contain LOB, REF, or create a function index for the object type columns of data types such as nested tables.

Use function indexes

If a function is used in the WHERE clause of an SQL statement, the function-based index is an effective mechanism to improve data access performance. The results of expressions are stored in the index after calculation. However, when executing INSERT and UPDATE statements, Oracle needs to perform function operations to maintain indexes.

For example, if you create the following function indexes:

Create index idx ON table_1 (a + B * (c-1), a, B );

The previously created index can be used when Oracle processes the following queries:

SELECT a FROM table_1 WHERE a + B * (c-1) <100;

Using the UPPER (column_name) or LOWER (column_name) function to create a function index is helpful for case-insensitive queries. For example, create the following function indexes:

Create index uppercase_idx ON employees (UPPER (first_name ));

This helps improve the performance of the following queries:

SELECT * FROM employees WHEREUPPER (first_name) = 'Richard ';

Function Index Optimization

You must collect statistics about function indexes (unction-based indexes) for the optimizer ). Otherwise, this index will not be used when processing SQL statements.

When the WHERE clause of a query contains an expression, the optimizer can scan the index range (index range scan) of the function index ). For example, the following query:

SELECT * FROM t WHERE a + B <10;

If an index is created using expression a + B, optimizer can scan the index range ). If the predicate (WHERE clause) produces a lower selectivity, the interval scan is extremely advantageous. In addition, if the expression results are materialized within the function index, the optimizer can more accurately estimate the preference of the predicates using this expression.

Optimizer can parse expressions in SQL statements and function-based indexes into expression trees and compare them to achieve expression matching. This comparison process is case-insensitive and will ignore all spaces (blank space ).

Reverse Key Index

You can create a reverse key index. Here, the reverse order refers to the reverse order of each byte of the index key value, instead of Sorting index keys in reverse order. In the RAC environment, this arrangement can be used to avoid performance degradation caused by changes to indexes concentrated on a small number of leaf blocks (leaf block. By sorting the key values of an index in reverse order, the insert operation can be distributed across all the leaf blocks of the index.

After a reverse key index is used, index range scanning cannot be performed on this index, because in the reverse key index, lexically) adjacent index keys are not necessarily adjacent to each other in storage. Therefore, you can only perform fetch-by-key scan or full-index scan on the reverse key index ).

In some cases, the use of reverse-order key index can make OLTP applications in the RAC environment more efficient. For example, index all emails in an e-mail application: Because the user may save the old emails, the index must be able to quickly access the latest emails and the old emails.

You can use REVERSE to easily create a reverse key index ). Use the REVERSE keyword in the create index statement as the option to CREATE an INDEX:

Create index I ON t (a, B, c) REVERSE;

You can also add the NOREVERSE keyword after the REBUILD clause to convert a reverse key index to a regular index:

Alter index I REBUILD NOREVERSE;

If the NOREVERSE keyword is not used after the REBUILD clause, the reverse order will remain after the reverse key index is rebuilt.

Bitmap Index

Bitmap indexes are also a very important index. In some specific environments, bitmap indexes have very good results, but are very prone to misuse. You need to carefully verify before and after use to see if the expected results are achieved.

Table Analysis

Sometimes, we will find that although there is an index, the execution plan does not go through the index, and the full table scan is running. In this case, we need to check the last analysis time of the table, if the DBA does not perform Table Analysis in time, the index may not be used.

Select * from user_tables t;

Find the last_analyzed time of the corresponding table. If the time is too old or there is no time, it indicates that the table needs to be analyzed.

Table analysis is generally performed when the business performance is not busy. First, let's take a look at what table analysis is:

Simply put, it is to collect information about tables and indexes. Based on this information, CBO determines the optimal SQL Execution path. Through table analysis, you can generate some statistical information, through which the oracle optimization program can be optimized.

The oracle online document describes how to use analyze:

Use the ANALYZE statement to collect non-optimizer statistics, forexample,:

-- Use the analyze function to collect statistics that are not optimized, for example:

Collect or delete statistics about an index or index partition, table ortable partition,

-- Clustered or deleted index or index partitions, table or table partitions

Index-organized table, cluster, or scalar object attribute.

-- Index table, string, or target attribute of the cursor and other statistical information

Validate the structure of an index or index partition, table or tablepartition, index-organized table,

-- Adds the validity of the following structures: Index or index partition, table or table partition, and index table

Cluster, or object reference (REF ).

-- String, target reference

Identify migrated and chained rows of a table or cluster.

-- Identifies a column that has been moved or locked in a table or string.

Table Analysis Statement reference:

Analyze table tableName compute statistics;

Advantages of the Balance Tree Structure

B-tree structure has the following advantages:

· The depth of all the leaf blocks in the B-tree is the same, so it takes roughly the same time to obtain data at any position in the index.

· B-tree indexes can be automatically flat.

· The average capacity of all blocks in the Balance Tree is about 3/4 of the total capacity.

· When querying within a wide range (wide range), the balance tree can provide better query performance regardless of whether an individual value (exact match) matches or searches for a range search.

· Insert, update, and delete operations are efficient and easy to maintain key order)

· Large tables and small tables use the Balance Tree for better search efficiency, and the search efficiency will not be reduced due to data growth.

Unique index Scan

Index unique scan is one of the most efficient data access methods. This method is used to obtain data from the B-tree index. When all the columns of a unique index (with a balanced tree structure) are included in the query condition and the query body condition expression is equal, the optimizer uses the unique index scan.

Index interval Scan

When you access selective data, Oracle usually performs index range scan ). The scan interval can be bounded or unbounded ). The returned data is scanned in ascending order of the index column. Rows with the same index value are sorted in ascending order of ROWID.

Performance and storage considerations

Key compression can save a lot of storage space. Therefore, you can store more index keys in one index block ), this reduces I/O and improves performance.

Key compression can reduce the storage space required by indexes, but reconstruct (key value) needs to be reconstructed during index scanning, thus increasing the CPU burden. In addition, key compression brings about some storage overhead. Each prefix entry requires 4 bytes of management overhead.

Use Key Compression

Key compression can be used in many situations, such:

· For nonunique indexes, Oracle adds rowid after each duplicate index key for differentiation. If key compression is used, Oracle only needs to store duplicate index keys as the prefix once in an index block, use the rowid of each row as the suffix (suffix entry ).

· The same situation exists in nonunique indexes. For example, the meaning of the unique index (stock_ticker, transaction_time) is (Project, timestamp). Generally, the value of stock_ticker is the same in thousands of records, however, their transaction_time values are different. After key compression is used, each stock_ticker value in an index block is stored only once as a prefix, and each transaction_time value is stored as a suffix and a shared stock_ticker prefix is referenced.

· In an index-organized TABLE containing the VARRAY or NESTED table data type (datatype), the object identifier of each element in these collection types) is repeated. You can use key compression to avoid repeated storage of these object identifiers.

In some cases, key compression cannot be used ). For example, key compression cannot be used for a unique index with only one index key, because the index key does not contain grouping piece ).