Dozens of rules to improve SQL query performance

Record detailed requirement documents

Before writing SQL statements, you must clarify the requirements, specific data to be taken, and specific constraints. In the data warehouse environment, you can also consider the specific reports corresponding to this requirement, what is the corresponding basic table. in the development environment, you can consider the businesses (pages) that the SQL service is applied to and the call frequency.

Do not create wheels again

For some mature solutions with SQL examples, you must be good at using existing SQL and existing templates.

Reduce statement complexity

Some students may prefer to use cool technologies and cool SQL to solve the problem. however, it should be noted that the use of technologies that are too complex and too new will only make the SQL difficult to maintain if it cannot bring benefits in performance (and other aspects, this makes it difficult for other related personnel to understand.

Handle NULL with caution

NULL is a very special value in the Oracle database. It is not equal to any value, so if your SQL returns a small number of values, or not at all. It is likely that there is a problem with NULL .. common situations are:

1. the condition colx = xxx is directly used for query, and the colx contains NULL values. In this case, the returned results of the query do not contain records corresponding to NULL values, if you want to query the records corresponding to NULL, you need to use colx is null (is not null ).

2. when var is null, assign a value to var in plsql, var: = var + xxx; in this case, the value of var will always be null, which requires special attention, I also made this mistake several times.

Self-check data type

In the where condition, check the data type carefully. Due to the invisible conversion problem, Oracle cannot correctly use the index when the data type is incorrect, which may cause the SQL operation to be very slow.

Careful processing of duplicate data

If you do not care whether duplicate records exist or you know that duplicate data does not exist, use Union All instead of Union for query, union involves expensive sorting operations.

Avoid unnecessary optimization operations

SQL Performance Tuning can be very interesting and exciting, but it is of little significance in many cases. For example, for queries that only use one time, you may seldom care whether it is the end of 1 second or the end of 2 seconds ..

However, some basic optimization rules still need to be used:

Only query the fields you need, instead of using select * for all queries.

When it is more appropriate to query by index, you should specify the indexed field in the query condition as much as possible. (when the number of returned records is very small, the query results can be obtained more quickly using indexes. do not avoid table join. relational databases are designed to connect tables.

Bind variables whenever possible

Bind variables to the SQL statements used in the development environment as much as possible, which can greatly reduce the consumption of SQL parsing in the Oracle database and greatly improve the scalability of the database.

Use source code control tools

It is best to use source code control tools such as CVS/SVN to manage your SQL/PLSQL code, which will be of great help for later maintenance, it also helps others better understand your intention to write this SQL statement.

Test, test, and test.

After the SQL statement is written, perform in-depth tests to ensure its normal operation.

Original article title: How to Improve SQL query skills

Link: http://www.dbthink.com /? P = 172