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:
1.select * from TB where name is like '%xx% ';
2.select * 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://wenku.baidu.com/link?url= 7c70-3mtzekx2idvjedhghkk8m5elje5cyypnivfzj4w8eu9snrbnmnmrfoh6kpz-g150bxjvijahcihstw-i9ergmtmwrigrzkxp-ralmk
To solve the problem of the knowledge points:
Related information: See http://czmmiao.iteye.com/blog/1484298
2. Indexing policy
Related to the introduction of the degree Niang.
Related information: See http://www.cnblogs.com/dongzhiquan/archive/2012/01/20/2328365.html
Cluster in 4.oracle--Aggregation in Oracle
Related information: See http://blog.csdn.net/thunder09/article/details/5003675
Work problem Summary One: from the data query slow to discuss the fuzzy query efficiency problem of like and InStr functions in Oracle