Shulou(Shulou.com)06/01 Report--

This article will explain in detail the methods and matters needing attention on the use of mandatory indexing in Oracle. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

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.

On the use of Oracle mandatory indexing methods and points for attention to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.