Work problem Summary One: from the data query slow to discuss the fuzzy query efficiency problem of like and InStr functions in Oracle

Source: Internet
Author: User
Tags sql using

Today around 10 o'clock in the morning, boss told us that the production environment a customer in a function of the input information, the loading data is very slow situation, because the serious impact on customer use, let us hurriedly verify the reason, the leader and I based on the leadership provided by the user login system, to find the problem of the function,  Select buyer and bank information after the load limit information when the leader's computer response is the browser card dead, my computer is to go to the toilet back to sit down before loading out, I am about 3 minutes of time, Crash!!! Quickly check the code through the query code found that the load of data is from tens of millions of levels (there are more than 2321w) of the table query, query statements using the LIKE operator, the study found that it is really because the query SQL using LIKE operator caused. Query SQL as follows:

Select SUM (t.a*t.b) from the table t where t.c = ' a ' and t.d like ' am% ' and t.e = ' B ' ....

Where the D field value of table T at the beginning of the 2 letter expression of some type of data has am, BM, CM and other types of data, and the other bits of the D field are 0-9 digits

Below are the steps to analyze the problem:
1. The index policy of the table is queried, the table is D > C > E (note: D, C, E are the fields of table tables)
2. The index of D is invalidated and then queried with the above statement, but the use of the SELECT * from table t where t.d = ' AM1245 ' will undoubtedly be a disaster because it becomes a full table retrieval
3. Then make the index of D effective, the default index policy for querying the table is still d > C > E
4. Use InStr (T.D, ' AM ') >0 replace the t.d like ' am% ' in the SQL statement and find the efficiency is fast. --the result of validation in the production environment is that using a like query for three minutes is an instant query with InStr.
This problem leads to the focus of this paper, that is, the fuzzy query efficiency problem of InStr and like

After asking the mother know:

Use Oracle's InStr function with index mates to improve the efficiency of fuzzy queries.

In general,In the Oracle database, we use the following two ways to make a fuzzy query on the name field of the TB table: * from TB where name is like '%xx% '; * from TB where InStr (name, ' XX ') >0;
If there is no index on the name field, the efficiency is almost the same, basically no difference.
To increase efficiency, we can add a non-unique index to the name field:
CREATE INDEX Idx_tb_name on TB (name);
This way, then use SELECT * from TB where InStr (name, ' XX ') >0;Such a statement query, the efficiency can be improved a lot, the larger the amount of table data when the difference between the two (we are faced with the problem is this problem). But also take into accountThe Name field is indexed after the DML statement affects the index data reordering.

This article see article: Http:// 7c70-3mtzekx2idvjedhghkk8m5elje5cyypnivfzj4w8eu9snrbnmnmrfoh6kpz-g150bxjvijahcihstw-i9ergmtmwrigrzkxp-ralmk

To solve the problem of the knowledge points: histogram

Related information: See

2. Indexing policy

Related to the introduction of the degree Niang. Optimizer

Related information: See

Cluster in in Oracle

Related information: See

Work problem Summary One: from the data query slow to discuss the fuzzy query efficiency problem of like and InStr functions in Oracle

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.