Why sometimes Oracle databases don't use indexes to find data

oracle| Data | database | index
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.

The execution step of the SQL statement.

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.
VI, is there a potential data type conversion. If you compare character data with numeric data, ORACLE automatically converts character types with the To_number () function, which causes the sixth phenomenon to occur.


Seven, whether to collect sufficient statistical data for the table and related indexes. The table and index are best regularly analyzed by the tables and indexes that are often added, deleted and modified by the data, and SQL statements "analyze table XXXX compute statistics for all indexes;". ORACLE has the ability to fully reflect the actual statistical data to make the right choice.

The selectivity of index columns is not high. We assume that in typical cases, there are 1 million rows of the EMP, but the Emp.deptno column, the data is only 4 different values, such as 10, 20, 30, 40. Although there are many EMP data rows, the value of the columns in the ORACLE default determination table is evenly distributed across all data rows, i.e. each DEPTNO value has 250,000 rows of data corresponding to it. Assuming that the SQL search conditions deptno=10, using the index on the DEPTNO column for data search efficiency, often not higher than the full table scan, ORACLE naturally to the index "blind", that the index is not high selectivity. But we consider the other case, if the 1 million data row is not actually among 4 kinds of deptno values evenly divided between 990,000 rows corresponding to the value 10, 5000 rows corresponding to 20, 3000 row corresponding value 30, 2000 row corresponding value 40. In this data distribution pattern when searching for other deptno values except the value 10, there is no doubt that if the index can be applied, the efficiency will be much higher. We can use the indexed column for a separate analysis, or use the Analyze statement to establish a histogram of the column, collect enough statistics on the column, so that ORACLE in search selectivity higher values can be indexed.

Ninth, whether the indexed column value can be empty (null). If an indexed column value can be a null value, those operations that need to return a null value in an SQL statement will not use the index, such as COUNT (*), but rather a full table scan. This is because the stored values in the index cannot be full empty.

11th, see if it is useful to parallel queries (PQO). Parallel queries will not use the index. If we want to use the Ind_col1 Index of table A, we can use the following methods:

"Select/*+ INDEX (a ind_col1) * * from WHERE COL1 = XXX;"

Note that the annotation must follow the SELECT, and that the "+" in the note should follow the comment starter "/*" or "--", otherwise hint is considered a general comment and has no effect on the execution of the Pl/sql statement.

One is the EXPLAIN TABLE method. The user must first establish the Plan_table table under his own schema, and every step of the execution plan is recorded in the table, and the table SQL script is utlxplan.sql under ${oracle_home}/rdbms/admin/.

Open Sql*plus, enter "SET autotrace on", and run the SQL statement to be debugged. After the query results are given, ORACLE displays the corresponding execution plan, including the optimizer type, execution cost, connection mode, connection order, data search path, and corresponding resource costs such as continuous read, physical reading, and so on.

If we are unsure of the specific SQL statement that needs to be tracked, such as when an application is used for a period of time, the response speed slows down. We can then use another powerful tool provided by ORACLE to tkprof the application's execution process.

First, in the system view v$session, we can find the corresponding SID and serial# according to USERID or MACHINE.

Execute "execute Dbms_system" with the SYS or other user who has the Execute Dbms_system package to connect to the database. Set_sql_trace_in_session (SID, serial#, TRUE); ".

The application is then run, at which point the Ora__xxxx.trc file is generated on the server side, in the directory indicated by the database parameter "User_dump_dest", where xxxx is the operating system process number for which the application is being tracked.

After the application executes, the file is parsed with the command tkprof. command example: "Tkprof tracefile outputfile Explain=userid/password". Under the operating system ORACLE user, type "tkprof" with detailed command help. After the analysis of the output file outputfile, there are important information such as the "execution plan" of each PL/SQL statement, CPU occupancy, physical number of reads, logical read times, and execution length. Based on the information of the output file, we can quickly discover which Pl/sql statement in the application is the crux of the problem.