Shulou(Shulou.com)05/31 Report--

This article mainly introduces why the oracle database index is not used in the query, has a certain reference value, interested friends can refer to, I hope you have a lot of gains after reading this article, the following let Xiaobian take you to understand.

quick check

Is there an index on the table?

Check that there is actually a defined index on the table that you think should be accessed through an index. Those indexes may have been deleted or failed at the time of creation-for example, one possible scenario is that after an import or load operation on a table, the index is not created due to software or human error. The following statement can be used to check if an index exists.

SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;

Should indexes be used?

Oracle doesn't use indexes just because they exist. If a query needs to retrieve all the records in the table (for example, join between tables), why access all the data in both the index and the table? In this case, it is faster to access only the table data. For all queries Oracle Optimizer calculates various access paths, including indexes, based on statistical information, to select the *** one.

Problems with the index itself

Is the index column or preceding column of the index in the Where condition of a non-join query?

If not, at least the index leading column needs to be in the query predicate list for the query to use the index. (Exception: See Skip Scan below).

Examples:

A single column index EMPNO_I1 is defined on the column EMP.EMPNO, while a joint index EMPNO_DEPT_I2 is defined on the columns EMP.EMPNO and EMP.DEPT.(EMP.EMPNO is the index leading column). The column EMP.EMPNO must be used in the query predicate list (where clause) for the optimizer to use either index.

SELECT ename, sal, deptno FROM emp WHERE empno

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.