5 SQL tips for DBAs to fall in love with you

A colleague of mine, Martin Masarik,sqlde's CEO, talked to me about one of his DBA friends who managed an Oracle database for an international bank with a data size of about 2TB. Martin Masarik once asked him: "What kind of SQL problem can make you angry to hair-up?" He summed up the following points, all of which are experience:

First, do not invoke function on the index column

Doing so will prevent the database from using this index, which can even affect the partition table, because doing so will not read the data from the specified partition, but instead scans the entire table space. For data tables with large data volumes, this would be a major disaster for performance.

Do not do this:

wheretime_id+14 > To_number (To_char (sysdate, ' J '))

Should do this:

wheretime_id > To_number (To_char (sysdate-14, ' J '))

Second, using analyze to optimize the complex SQL

If you do not do this, it will mean that you refuse to use the database's query optimizer and have lost the opportunity to use optimized connections. Suppose you create a temporary table with 1 million records, and if you do not parse it, the optimizer will not be able to get the actual contents of the table from the existing thread, so it can only decide to scan the data table row by line using a nested loop join, and if the amount of data is small, we may not feel the loss of performance, But as the data set grows, your database performance is getting worse.

It is recommended to do this:

ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS

Three, the complex SQL into a few steps to execute

Think of SQL as a pizza, and I guess you won't have to swallow the whole pizza in your mouth all at once.

For creating a complex SQL query, we'd better divide it into 3-4 steps, and the simpler the SQL, the better the optimizer will be, and the easier it is to debug the data in each table.

Iv. use of distinct only when necessary

This is a very good rule of thumb, and distinct is often used in SQL queries that return 2 or more duplicate records, using distinct to filter out duplicate data records. But the purpose of using distinct must be clear when you are sure that the records returned must be unique, such as the user ID. Misuse of distinct will cause unpredictable errors, such as multi-table connection queries.

V. Rational creation of indexes

The last point is to reasonably create the table index, simply put, if there is a 100,000 records of the data table, you may often query such information: "One of my customer information in this table?" ”。 If an index is used, retrieving this customer information will be very rapid, otherwise the database optimizer will choose a full table scan, which is a nightmare in the case of large data volumes.

From: Code Farming Network

Links: http://www.codeceo.com/article/5-sql-tips-dba-love-you.html

5 SQL tips for DBAs to fall in love with you