Why Oracle databases cannot use Indexes

Sometimes Oracle databases cannot use indexes. There are many reasons for this phenomenon. How can we locate it? This article mainly introduces this part.
First, determine the optimization mode in which the database runs. The corresponding parameter is: optimizer_mode. You can run "show parameter optimizer_mode" in svrmgrl to view it. 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 an "Execution Plan. Www.2cto.com
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, and most of it is used.
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.
Sixth, 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. Www.2cto.com
7. Check whether sufficient statistics are collected 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.
Eighth, the selection of index columns is not high. We assume that the table emp has a total of 1 million rows of data, but the emp. the deptno column has 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. Assume that the SQL Search Condition DEPTNO = 10 and the efficiency of data search by using the index on the deptno column is usually not higher than that of the full table scan. ORACLE does not evenly allocate the four types of deptno values, there are 0.99 million rows corresponding to the value 10,500 0 rows corresponding to the value 20,300 0 rows corresponding to the value 30,200 0 rows corresponding to the value 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.
Ninth, 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.
10. Check whether parallel query (PQO) is useful ). Indexes are not used for parallel queries. If you want to use the IND_COL1 "SELECT/* + INDEX (A IND_COL1) */* from a where COL1 = XXX;" note that the annotator must be behind SELECT, the "+" in the annotation must follow the annotation start character "/*" or "--". Otherwise, hint is considered as a general annotation and does not affect the execution of PL/SQL statements. Www.2cto.com is an explicit 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 ", this includes the optimizer type, execution cost, connection method, connection sequence, and data search path. 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 #. run "EXECUTE www.2cto.com 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". Enter "tkprof" under the ORACLE user of the operating system, which provides 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. Here we will introduce the reason why Oracle databases cannot use indexes. We hope this introduction will help you.
This article is from the China IT lab