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

This article will explain in detail the example analysis of fuzzy query index in the database. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

1. Problem description:

The use of fuzzy query SQL statements such as like'% A%'in a production environment causes full table scans to slow down the system.

2. Scene recurrence:

Use scott's emp2 (emp copy) table as the test table to query all employees who contain the letter O in the ename field.

Prerequisite: the index create index ind_emp2_ename on emp2 (ename) has been established in the ename field of the emp2 table

SCOTT > explain plan for select * from emp2 where ename like'S%'

SCOTT > @? / rdbms/admin/utlxplp

SCOTT > explain plan for select * from emp2 where ename like'% O%'

SCOTT > @? / rdbms/admin/utlxplp

Conclusion: fuzzy query does not take the index if the first one is a wildcard.

Suggestion: fuzzy query, if you want the index to take effect, give the previous string

This is the end of the article on "example Analysis of Fuzzy query Index in Database". I hope the above content can be helpful to you, so that you can learn more knowledge. If you think the article is good, please 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.