5 segment SQL can test your knowledge of the performance optimization of SQL how much

Label SQLperformance optimization
2096 people read comment(0) Collection Report
Classification:
Database is very powerful, it can achieve the performance of ultra - expected performance. But a lot of people are very easy to write can not play the most efficient algorithm SQL query statement, which is also easy to produce the performance results can not be expected.

The following UncleToo for everyone to sorted out 5 SQL, we usually write the SQL, but slightly after some optimization, or change the kind of writing, perhaps his execution efficiency will be higher than several times, you can also control their usual writing to see whether they still continue to learn.

This paper describes the SQL is based on the ORACLE database.

1, check all the rows in 2014


A SQL optimization experience may be asked, write this no problem ah, will appear in the condition of field indexed, can play a role of SQL optimization, how are we going to have a problem? There is such a question of a friend to database optimization or some understanding, but you ignore the point:Whether it is ORACLE or other database, once the field is put in the function, the index will not play the role of the field.So the correct method is as follows:



2, check the latest date of the line of data


This is a classic SQL, the development of many places are likely to use such a query. Yes, this SQL is not to be optimized, it can be implemented in accordance with the index of the Top-N query, its efficiency with UNIQUE SCAN INDEX (unique index scan) is equivalent, so it will be very fast.

Two, 3 query statements, through an ordinary column query:




This is very simple, only the first SQL to use the index, and the second are unable to use the index. In fact, this is the difference between the single index and the composite index.

4, string fuzzy query


In the previous article, as we have said statements like how to use wildcard characters in order to improve the efficiency of (8 a SQL statement commonly used optimization techniques). When conditions encountered in like statements, then whether the field is set indexing results are the same, because index meet like will not be able to play its effectiveness, and there is no better solution, it is recommended that you unless you absolutely have to, otherwise not easily use like sentence.

5, check the number of records under a condition


Above two queries, first may detect thousands or tens of thousands of records, and the second statement as much a condition may be detected only several or dozens of records, maybe you will be think the second statement efficiency, faster. In fact, just the opposite, the first statement of the efficiency of the implementation of faster. Because of the first statement, the index covers all the query fields, and the B condition in the second SQL is not indexed.
top
Zero
step on
Zero
Guess you're looking for
View comments
* the above user comments only represent their personal views, does not represent the views or position of the CSDN website
    personal data
    • Visit400204 times
    • Integral:Four thousand seven hundred and thirty-three
    • Grade
    • Rank:3296th name
    • Original104
    • Reproduced:82
    • Translation:0
    • Comments:1083 article
    contact us
    Contact: Yao Xi
    Tel: 51661202-381
    Mailbox: yaoxi@PROG3.COM
    QQ number: 2411559571
    Club website: http://prog3.com/sbdm/student