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

Editor to share with you how to use Oracle mandatory index, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Oracle uses mandatory indexing

In some scenarios, ORACLE may not automatically walk the index. At this time, if the business is clear, you can try to use the mandatory index to test the performance of the query statement.

Take EMP table as an example:

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

General search:

SELECT * FROM EMP T

View the execution plan:

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

Using a mandatory index, add the attribute of the index after / *. * / in the middle of the SELECT, as follows:

SELECT / * + index (t pk_emp) * / * FROM EMP T-force indexing, / *. * / No spaces after the first star, the content structure is: plus sign index (table name space index name). If the table uses an alias, the table in the comment should also use an alias.

As you can see, this is going to be the index PK_EMP.

Considerations for Oracle to use mandatory indexing

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

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

A, 2016-01-01-01 / 2016-11-30 data volume 402518, the index is effective

B, 2016-01-01-01-2016-12-30 data volume 444844, the index is not valid

SELECT * FROM T_MAINS WHERE date > TO_DATE (--Note today is 2017-01-23 '2016-01-01-01) AND date < TO_DATE (' 2016-11-30, 'yyyy-mm-dd hh34:mi:ss')

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

The above is all the content of the article "how Oracle uses mandatory indexing". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.