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.
- step on
- Guess you're looking for