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

This article will explain in detail how to use reverse index to optimize like statements, the editor thinks it is very practical, so share it for you to do a reference, I hope you can get something after reading this article.

The original SQL is as follows:

The index of the pcc_cust_infonew table is as follows

The implementation design is as follows:

Here you can see that SQL executes 3.96 seconds, and in the execution plan, you can also find that the PCC_CUST_INFONEW table scans TABLE ACCESS FULL and returns about 380k of data, and then INDEX UNIQUE SCAN with the primary key PK_PCC_CUST_CONTRACTCOMPANY of the PCC_CUST_ transaction table. Here slow is mainly due to the execution of the step of 3 in the plan.

We all know that in Oracle, when using like, the index can be used if it is the last% (like '138%), and if it is the first% (like' 8'), the index cannot be used properly. So how to make the first% also available to indexes? the answer is that they can be optimized in the form of reverse indexes.

The optimization is as follows:

Modify the index

Adjust SQL to

After the above adjustment, the SQL execution efficiency is improved to the millisecond level.

The adjusted implementation plan is as follows:

So after using the reverse function index, how on earth can you use the index? when you see the predicate information under the execution plan, you must know the answer.

In fact, it is to use the reverse function index to reverse the index data, and the where condition in SQL to reverse the value, thus changing the first% to the last%.

On "how to use reverse index to optimize like statements" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out 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.