oracle| Performance | optimization
25. Use Index to improve efficiency
An index is a conceptual part of a table that is used to improve the efficiency of retrieving data. In fact, Oracle uses a complex, b-tree, self-balanced structure. In general, querying data through an index is faster than full table scans. The Oracle optimizer uses indexes when Oracle finds the best path to execute queries and UPDATE statements. It also increases efficiency when you use indexes to join multiple tables. Another advantage of using the index is that it provides uniqueness validation for the primary key (primary key).
In addition to those long or long raw data types, you can index almost all columns. In general, using indexes in large tables is particularly effective. Of course, you'll also find that using indexes can also improve efficiency when scanning small tables.
Although the use of indexes can improve query efficiency, we must also pay attention to its cost. The index requires space to
Storage, also requires regular maintenance, and the index itself is modified whenever a record is added or subtracted from the table or the index column is modified. This means that each record's insert, DELETE, and update will pay 4, 5 more disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow down query response times.
Translator by:
It is necessary to periodically refactor the index.
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
26. Operation of the Index
Oracle has two modes of access to indexes.
Indexed unique Scan (index unique SCAN)
In most cases, the optimizer accesses the index through the WHERE clause.
For example:
Table Lodging has two indexes: a unique index established on the lodging column LODGING_PK and a non-uniqueness index Lodging$manager on the MANAGER column.
SELECT *
From lodging
WHERE lodging = ' ROSE HILL ';
Internally, the above SQL is divided into two steps, first of all, the LODGING_PK index will be accessed by means of an index-only scan, obtaining the corresponding rowid, and performing the next retrieval by ROWID access to the table.
If the column returned by the retrieval is included in the index column, Oracle will not perform the second step (access to the table via ROWID). Because the retrieved data is stored in the index, access to the index alone can fully satisfy the query results.
The following SQL requires only the index UNIQUE SCAN operation.
SELECT Lodging
From lodging
WHERE lodging = ' ROSE HILL ';
Index range Query (index range SCAN)
Applies to two situations:
1. Based on a range of searches
2. Retrieval based on the non-uniqueness Index
Example 1:
SELECT Lodging
From lodging
WHERE lodging like ' m% ';
Where clause conditions include a series of values, Oracle queries the LODGING_PK by way of an indexed range query. Because the index range query returns a set of values, it's efficient to have the peso to be the only scan
A little lower.
Example 2:
SELECT Lodging
From lodging
WHERE MANAGER = ' BILL GATES ';
This SQL is executed in two steps, Lodging$manager the index range query (get all the rowid that match the condition Records) and the next ROWID Access table to get lodging column values. Because Lodging$manager is a non unique index, the database cannot perform an index-only scan on it.
Because SQL returns the lodging column, and it does not exist in the Lodging$manager index, an operation that accesses the table through the ROWID is performed after the index range query.
In the WHERE clause, the index is not adopted if the first character of the value of the indexed column Fu Yutung (wildcard) begins.
SELECT Lodging
From lodging
WHERE MANAGER like '%hanman ';
In this case, Oracle will use a full table scan.
adjourned
