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

This article introduces the relevant knowledge of "how to avoid SQL full table query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Fuzzy query efficiency is very low:

Reason: like itself is relatively inefficient, we should try to avoid using like; for query conditions such as like'%.%'(fully fuzzy), the index cannot be used, and the natural efficiency of full table scanning is very low; in addition, due to the matching algorithm, the larger the field length of the fuzzy query, the lower the efficiency of the fuzzy query.

Solution: first of all, try to avoid fuzzy query, if you must use fuzzy query because of business needs, then at least make sure not to use full fuzzy query, for right fuzzy query, that is, like '… %', index will be used; left fuzzy like

'%.' You can't use an index directly, but you can change it to like'by using the form of reverse + function index. %'; full fuzziness cannot be optimized, so consider using a search engine if necessary. In order to reduce the load of the server, reduce the fuzzy query of the database as much as possible.

2. The select statement with is null in the query condition executes slowly

Reason: in 9i, the single index fails when querying the field is null, resulting in a full table scan.

Solution: using NULL in SQL syntax will have a lot of trouble, preferably index columns are NOT NULL; for is null, you can set up a composite index, nvl (field, 0), after analyse on tables and indexes, is null query can re-enable index lookup, but the efficiency is not worth affirming; indexes are never used in is not null. Generally, tables with a large amount of data should not be queried by is null.

3. Select statements that are not equal to the operator (,! =) are used in the query condition to execute slowly.

Reason: in SQL, the unequal operator restricts the index, causing a full table scan, even if there is an index on the compared field

Solution: by changing the unequal operator to or, indexes can be used to avoid full table scans. For example, you can use an index by changing column'aaa', to column'aaa',.

4. Using a combined index, if there is no leading column in the query condition, the index will not work and will cause a full table scan; but starting from Oracle9i, the index skip scan feature has been introduced, which allows the optimizer to use a combined index, even if the leading column of the index does not appear in the WHERE clause. For example: create index skip1 on emp5 (job,empno); full index scan select count (*) from emp5 where empno=7900; index skip scan select / * + index (emp5 skip1) * / count (*) from emp5 where empno=7900; the former is a full table scan, and the latter uses a combined index.

5. Improper use of or statement will cause full table scan.

Reason: two conditions are compared in the where clause, one with an index and one without an index. Using or will cause a full table scan. For example, if there is an index on where indexer An and there is no index on B, then a full table scan will be restarted when comparing Broad Vista 2.

6, combined index, sorting should be sorted according to the order of the columns in the combined index, even if only one column in the index is to be sorted, otherwise the sorting performance will be poor. For example: create index skip1 on emp5 (job,empno,date); select job,empno from emp5 where job='manager'and empno='10' order by job,empno,date desc; is actually just querying the records that meet the job='manager'and empno='10' criteria and sorting them in descending order of date, but the performance of writing as order by date desc is poor.

7. Update statement, if you change only 1 or 2 fields, do not Update all fields, otherwise frequent calls will cause obvious performance consumption and a large number of logs.

8. For multiple tables with a large amount of data (a few hundred are considered large here), JOIN should be paged first and then JOIN, otherwise the logical reading will be very high and the performance will be very poor.

9. Select count (*) from table;, a count without any conditions, will cause full table scanning and does not have any business significance, so it must be eliminated.

10. The where condition of sql should be bound with variables, such as where column=:1, and should not be written as where column='aaa',. This will cause re-analysis every time it is executed, wasting CPU and memory resources.

"how to avoid SQL full table query" content is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.