Why does Oracle sometimes use indexes to find data?

When you use the SQL language to publish a query to the database, Oracle will be accompanied by an "execution plan", which is how the statement will be executed through the data search scenario, through a full table scan, or through an index search. The selection of the search scheme is closely related to the Oracle Optimizer.

Execution steps for SQL statements

The process of processing an SQL statement takes several steps.

1 grammatical analysis and analysis of the syntax of the sentence is consistent with the norms, the meaning of the expression in the sentence.

2 Semantic analysis checks that all database objects involved in the statement exist, and that the user has appropriate permissions.

The 3 view transformation converts a query statement that involves a view to a corresponding base table query statement.

The 4 expression conversion converts a complex SQL expression to a simpler equivalent join expression.

5 Select optimizer different optimizer generally produces different "execution plan"

6 Select the connection way Oracle has three connection modes, and you can choose the appropriate connection mode for multiple table connections Oracle.

7 Select the connection order for multiple table connections Oracle Select which pair of tables to join first, select which table in the two tables as the source data table.

8 Select the search path of the data according to the above criteria to select the appropriate data search path, if the use of the whole table search or index or other means.

9 Run "execution plan"

Oracle Optimizer

Oracle has two types of optimizer: Rule-based optimizer (RBO, Rule Based Optimizer), and cost-based optimizer (CBO, costing Based Optimizer).

Rbo has been adopted since Oracle version 6, there is a strict set of rules to use, as long as you follow it to write SQL statements, no matter what the contents of the datasheet, it will not affect your "Implementation plan", that is, the data is not "sensitive", Oracle Company no longer develop this technology.

The CBO has been introduced from Oracle version 7, and many of the new technologies that Oracle has adopted since version 7 are based on CBO, such as star-link routing queries, hash-join queries, and parallel queries. The CBO calculates the "cost" of a variety of possible "execution plans", that is, costs, from which the least expensive scheme is chosen as the actual operating plan. The calculation of the cost of each "execution plan" depends on the statistical distribution of the data in the data table, and the Oracle database itself is not clear about the statistical distribution, and needs to analyze tables and related indexes to collect the data required by the CBO.

In general, the CBO chooses an "execution plan" that is no worse than the Rbo "execution plan", and, in contrast, the CBO's requirements for programmers are not as harsh as rbo, saving the debugging time that programmers spend to choose an optimal solution from multiple possible "execution plans". But there are also problems in some situations.

The more typical problems are: Sometimes, it indicates that Ming has indexed, but the query process obviously did not use the relevant index, resulting in a lengthy query process, a huge resource, the problem in the end is where? Search in the following order and basically find out why.

To find a reason

First, we want to determine what optimization mode the database runs in, and the corresponding parameters are: Optimizer_mode. You can run "show parameter Optimizer_mode" in SVRMGRL to view it. The default setting for ORACLE V7 should be "choose", that is, if you select CBO for the parsed table query, select Rbo. If the argument is set to rule, Rbo is selected regardless of whether the table is parsed, unless the statement is enforced in hint.

Second, checking whether the first column of the indexed column or composite index appears in the WHERE clause of the PL/SQL statement is necessary for the execution plan to use the associated index.

Third, see which type of connection is used. Oracle's total sort Merge join (SMJ), Hash join (HJ), and nested Loop join (NL). When two tables are connected and an index is built on the target column of the inner table, only the nested loop can effectively utilize the index. SMJ even if indexed on related columns, the data ordering process can only be avoided by the presence of indexes. Because the HJ has to do the hash operation, the existence of the index has little effect on the speed of the data query.

Four, see if the connection order allows for the use of related indexes. Assuming that there is an index on the Deptno column of the table EMP, there is no index on the column deptno the table dept and the WHERE statement has emp.deptno=dept.deptno conditions. In the NL connection, the EMP as the appearance, first accessed, because of the connection mechanism, the appearance of the data access is a full table scan, the index on the Emp.deptno is obviously not used, at most on its index full scan or index fast full scan.

Five, whether to use the System data dictionary table or view. Because system data dictionary tables are not parsed, they can lead to extremely poor execution plans. However, do not arbitrarily analyze the data dictionary table, otherwise it may lead to deadlock, or system performance degradation.

The index column is the parameter of the function. If so, the index is not available in the query.