Why ORACLE cannot use Indexes

Cause Analysis of ORACLE's inability to use indexes 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. Www.2cto.com second, 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.