Why does the Oracle database not use indexes to search for data?

When you use the SQL language to publish a query statement to the database, Oracle will generate an "Execution Plan", that is, the data search scheme through which the statement will be executed, whether to scan the entire table or search by index. The selection of search solutions is closely related to the Oracle optimizer.
SQL statement execution steps

The following steps are required to process an SQL statement.

1. analyze whether the syntax of a statement complies with the specifications to measure the meaning of each expression in the statement.

2. semantic analysis checks whether all database objects involved in the statement exist and the user has the relevant permissions.

3. View conversion converts query statements related to views into corresponding query statements for base tables.

4. Expression conversion converts complex SQL expressions into simple equivalent join expressions.

5. Select different optimizers to generate different execution plans"

6. Select the connection mode. Oracle has three connection modes. You can select an appropriate connection mode for multi-Table Oracle connection.

7. Select which table is first connected to the Oracle database connected to multiple tables in the connection sequence, and which table is selected as the source data table in the two tables.

8. Select the data search path and select the appropriate data search path based on the preceding conditions. If you select full table search, you can use indexes or other methods.

9 run the "Execution Plan"

　　Oracle optimizer

Oracle has two types of optimizers: Rule-Based optimizer and cost-based optimizer ).

RBO has been adopted since Oracle version 6 and has a set of strict rules for use. As long as you write SQL statements according to it, no matter what content in the data table, it will not affect your "Execution Plan ", that is to say, Oracle is no longer developing this technology because it is not "sensitive" to Data.

Since oracle 7, many new technologies adopted by Oracle 7 are based on CBO, such as star join arrangement query, hash join query, and parallel query. CBO calculates the "cost" of various possible "execution plans", namely, cost. It selects the lowest cost solution as the actual operation plan. The cost calculation of each "Execution Plan" depends on the statistical distribution of data in the data table. The Oracle database itself is not clear about the statistical distribution and requires analysis tables and related indexes, to collect the data required by CBO.

Generally, the "Execution Plan" selected by CBO is not inferior to the "Execution Plan" of RBO, and CBO has less demanding requirements on programmers than RBO, it saves programmers the debugging time to select an optimal solution from multiple possible "execution plans", but in some cases there will also be problems.

Typical problems are: Sometimes, it indicates that an index is created, but the query process obviously does not use the relevant index. As a result, the query process takes a long time and consumes a large amount of resources. What is the problem? Find the cause in the following order.

　　Steps for finding the cause

First, determine the optimization mode in which the database runs. The corresponding parameter is optimizer_mode. Run "show parameter optimizer_mode" in svrmgrl to view the information. Since Oracle V7, the default setting should be "choose". That is, if you want to query the analyzed tables, select CBO. Otherwise, select RBO. If this parameter is set to "rule", RBO is used no matter whether the table has been analyzed or not, unless it is forced by hint in the statement.

Secondly, check whether the indexed column or the first column of the composite index appears in the WHERE clause of the PL/SQL statement. This is a necessary condition for the "Execution Plan" to use the relevant index.

Third, check which type of connection is used. Oracle supports sort merge join (smj), hash join (HJ), and nested loop join (NL ). When two tables are connected and the target column of the internal table has an index, only the nested loop can effectively use the index. Even if an index is built on the relevant column, smj can only avoid data sorting because of the existence of the index. Due to hash calculation, the existence of Indexes has almost no impact on the data query speed.

Fourth, check whether related indexes are allowed in the connection sequence. Assume that the deptno column of the table EMP has an index, and the deptno column of the table dept has no index. The where statement has the condition EMP. deptno = Dept. deptno. During the NL connection, EMP is first accessed as the External table. Due to the connection mechanism, the External table data is accessed in full table scan and EMP. the index on deptno is obviously not used. A full index scan or quick full index scan can be performed on deptno.

Fifth, whether to use the system data dictionary table or view. Because the system data dictionary tables have not been analyzed, the execution plan may be very poor ". However, do not analyze the data dictionary tables without authorization. Otherwise, a deadlock may occur or the system performance may degrade.

Sixth, whether the index column is a function parameter. If so, indexes cannot be used during queries.

7. Whether there is a potential data type conversion. For example, if you compare the numeric data with the numeric data, Oracle will automatically convert the numeric data using the to_number () function, resulting in the occurrence of the sixth phenomenon.

Eighth, whether to collect sufficient statistics for tables and related indexes. It is recommended that you analyze tables and indexes on a regular basis for tables with frequent data additions, deletions, and changes. You can use the SQL statement "analyze table XXXX compute statistics for all indexes ;". Oracle can make the right choice only when it fully reflects the actual statistical data.

Ninth, the selection of index columns is not high.

We assume that the EMP table contains 1 million rows of data, but the EMP. deptno column contains only four different values, such as 10, 20, 30, and 40. Although EMP has many data rows, Oracle determines that the values of the columns in the table are evenly distributed across all data rows by default. That is to say, each deptno value corresponds to 0.25 million data rows. Assuming that the SQL Search Condition deptno = 10, using the index on the deptno column for data search efficiency is often no higher than the full table scan, Oracle naturally turns a blind eye to the index ", the index is not highly selective.

However, in another case, if 1 million data rows are not evenly distributed among the four deptno values, 0.99 million rows correspond to values of 10, rows correspond to values of 20, rows correspond to values of 30, and rows correspond to values of 40. In this data distribution pattern, when searching for other deptno values except 10, there is no doubt that if the index can be applied, the efficiency will be much higher. We can analyze the index column separately, or use the analyze statement to create a histogram for the column to collect sufficient statistics for the column, enables Oracle to index highly selective search values.

10. Whether the index column value can be null ). If the index column value can be null, indexes, such as Count (*), are not used in SQL statements for operations that require null values to be returned. Instead, full table scan is used. This is because the stored values in the index cannot be empty.

11th. Check whether parallel query (pqo) is useful ). Indexes are not used for parallel queries.

12th. Check whether bind variables are useful in PL/SQL statements. Because the database does not know the specific value of the Bind Variable, such as "<", ">", and "like" during non-equal connections. Oracle will reference the default value, which may affect the execution plan in some cases.

If no reason can be found from the above aspects, we have to use the hint method in the statement to force Oracle to use the optimal "Execution Plan ".

Hint adopts the annotation method, which can be line comment or segment comment.

If you want to use the ind_col1 index of Table A, you can use the following methods:

"Select/* + index (A ind_col1) */* from a where col1 = xxx ;"

Note: The annotator must be followed by the Select clause, and the "+" in the comment must be followed by the annotator "/*" or "--". Otherwise, the hint is considered as a general comment, the execution of PL/SQL statements does not have any impact.

　　Two effective tracking and debugging methods

Oracle provides two effective tools to track and debug the execution plans of PL/SQL statements.

One is the explain table method. You must first create a plan_table table in your schema. each step of the execution plan is recorded in this table, the SQL script for table creation is utlxplan under $ {ORACLE_HOME}/rdbms/admin. SQL.

Open SQL * Plus, enter "set autotrace on", and then run the SQL statement to be debugged. After the query result is displayed, Oracle displays the corresponding "Execution Plan ", it includes the optimizer type, execution cost, connection mode, connection sequence, data search path, and corresponding resource costs such as continuous read and physical read.

If we cannot determine the specific SQL statement to be tracked, for example, after an application is used for a period of time, the response speed suddenly slows down. We can use tkprof, another powerful tool provided by Oracle, to track the entire process of application execution.

In the system view v $ session, locate the corresponding Sid and serial # Based on the userid or machine #.

Connect to the database using sys or other users that execute the dbms_system package and execute "execute dbms_system.set_ SQL _trace_in_session (SID, serial #, true );".

Then run the application. On the server side, the ora _ XXXX will be generated under the directory indicated by the database parameter "user_dump_dest. TRC file, where XXXX is the operating system process Number of the application to be tracked.

After the application is executed, use the tkprof command to analyze the file. Command example: "tkprof tracefile outputfile explain = USERID/password ". If you are an oracle user in the operating system, type "tkprof" to provide detailed Command help. The output file outputfile after analysis contains the "Execution Plan", CPU usage, physical reads, logical reads, and execution duration of each PL/SQL statement. Based on the output file information, we can quickly find out which PL/SQL statement in the application is the crux of the problem.