Detailed explanation of the methods and precautions for using forced indexing in Oracle

This article mainly introduces to you the methods and precautions for using forced indexing in Oracle. The article introduces it in great detail through sample code. It has certain reference learning value for everyone's study or work. Friends who need it follow the small Let’s learn together. Hope it helps everyone.

Oracle uses forced indexing

In some scenarios, ORACLE may not automatically index. At this time, if it is clear to the business, you can Try using forced indexing to test the performance of the query statement.

Take the EMP table as an example:

First create a unique index in the EMP table, as shown in the figure.

Normal search:

SELECT * FROM EMP T

View execution plan:

As you can see, it is a full table scan.

Use forced index, add /*.......*/ after SELECT and add the index attribute in the middle, the code is as follows:

SELECT /*+index(t pk_emp)*/* FROM EMP T 

--强制索引，/*.....*/第一个星星后不能有空格，里边内容结构为：加号index(表名 空格 索引名)。
--如果表用了别名，注释里的表也要使用别名。

You can see that this is the index PK_EMP.

Precautions for using forced index in Oracle

Recently verified the effective conditions of Oracle's SQL index, and found the following rules, which are recorded as follows:

1. The index validity is related to the number of records

a. 2016-01-01~2016-11-30 data volume 402518, The index is valid

b. 2016-01-01~2016-12-30 The data volume is 444844, the index is not valid

SELECT 
 * 
FROM 
 T_MAINS 
WHERE 
 date > TO_DATE (--备注今天是2017-01-23 
  '2016-01-01', 
  'yyyy-mm-dd hh24:mi:ss' 
 ) 
AND date < TO_DATE ( 
 &#39;2016-11-30&#39;, 
 &#39;yyyy-mm-dd hh24:mi:ss&#39; 
);

2. T_MAINS has been partitioned according to a certain time type field. If a certain partition can be accurately located in the query conditions, the execution efficiency of SQL can be improved.

Related recommendations:

Batch file for stopping and starting mysql and oracle database

How to import table files to MySQL database or Oracle

oracle uses HINT method to force index

The above is the detailed content of Detailed explanation of the methods and precautions for using forced indexing in Oracle. For more information, please follow other related articles on the PHP Chinese website!