Oracle SQL Performance Optimization series (8)

25.Improve efficiency with indexes

 

An index is a conceptual part of a table to improve the efficiency of data retrieval. in fact, ORACLE uses a complex self-balancing B-tree structure. data Query by index is usually faster than full table scan. when ORACLE finds the optimal path for executing the query and Update statements, the ORACLE optimizer uses the index. using indexes when joining multiple tables can also improve efficiency. another advantage of using an index is that it provides uniqueness verification of the primary key.

Except for those LONG or long raw data types, You Can index almost all columns. generally, using indexes in large tables is particularly effective. of course, you will also find that using indexes to scan small tables can also improve efficiency.

Although the index can improve the query efficiency, we must pay attention to its cost. The index requires space.

Storage also requires regular maintenance. The index itself is also modified whenever there are records added or removed in the table or the index column is modified. this means that the INSERT, DELETE, and UPDATE operations for each record will pay four or five more disk I/O. because indexes require additional storage space and processing, unnecessary indexes will slow the query response time.

 

Press:

Regular index reconstruction is necessary.

Alter index <INDEXNAME> REBUILD <TABLESPACENAME>

 

26.Index operations

 

ORACLE has two access modes for indexes.

 

UNIQUE INDEX SCAN)

 

In most cases, the optimizer accesses the INDEX through the WHERE clause.

 

For example:

The table LODGING has two indexes: the unique index LODGING_PK on the LODGING column and the non-unique index LODGING $ MANAGER on the MANAGER column.

 

 

 

SELECT *

FROM LODGING

Where lodging = 'Rose HILL ';

 

Internally, the preceding SQL statement is executed in two steps. First, the LODGING_PK index is accessed by unique index scanning to obtain the corresponding ROWID, use ROWID to access the table and perform the next retrieval.

If the returned columns are included in the INDEX column, ORACLE will not perform the second step (access the table through ROWID ). because the retrieval data is stored in the index, accessing the index alone can fully meet the query results.

The following SQL statements only require the INDEX UNIQUE SCAN operation.

SELECT LODGING

FROM LODGING

Where lodging = 'Rose HILL ';

 

INDEX RANGE SCAN)

Applicable to two situations:

1. a range-based search

2. Non-unique index-based retrieval

 

Example 1:

 

SELECT LODGING

FROM LODGING

Where lodging like'm % ';

 

The WHERE clause condition includes a series of values. ORACLE will query LODGING_PK through index range query. Because index range query returns a set of values, it is more efficient than unique index scanning.

Lower.

 

Example 2:

 

SELECT LODGING

FROM LODGING

Where manager = 'bill Gates ';

 

The execution of this SQL statement is divided into two steps: LODGING $ MANAGER's index range query (obtain the ROWID of all matching records) and the next step is to obtain the value of the LODGING column through the ROWID access table. because LODGING $ MANAGER is a non-unique index, the database cannot perform a unique index scan on it.

 

Because the SQL statement returns the LODGING column and it does not exist in the LODGING $ MANAGER index, an operation is executed to access the table through ROWID after the index range query.

 

In the WHERE clause, if the first character of the value corresponding to the index column starts with the WILDCARD (WILDCARD), the index will not be used.

 

SELECT LODGING

FROM LODGING

Where manager like '% HANMAN ';

 

In this case, ORACLE uses full table scan.