Summary of recent SQL optimizations

recently do SQL optimization, found some problems, feel it is necessary to pull out to say, do not involve how advanced technology problems, or not much to do with technology, most of them are thought-aware problems, more think more to see should be solved.

there is, of course, a question of cost and benefit, some years ago I mentioned a similar problem to some people, but the time and cost In order to stop the back, this I can understand, after all, do the project is to make money, a loss of business no one to do, a project just a little money, there is no way.

1. The issue of binding variables.

The main is that many statements do not have bound variables, mainly written in the Java statement, mainly caused by the framework, different framework there are different ways of writing, in the final analysis is not familiar with the framework, more understanding should be able to avoid.

2. The problem of implicit conversions.

many occur in the date, number, CHAR/VARCHAR2 type and its sub-type, one is the design is unreasonable, and the other is the question of thought cognition, This is a commonplace topic.

3. Reduce the number of access to the table.

The main is that some statements repeatedly use some of the tables, you can consider using with statements, decode, case, etc. to replace.

4. The problem of intermediate result set size.

Is that the first join connection, after the filter conditions? or first filter condition, after join connection problem.

5. The problem of doing arithmetic on the column.

in particular, indexed columns, such as non-empty, case-insensitive processing. This should be done at the application layer and then into the database, rather than having the database handle it.

6. Special attention should be paid to the order by, group BY, distinct, union and other keywords.

Before using to think about, in the end should not use, how to use reasonable, whether there are other alternatives, do not use 3,801 up.

7. Be good at using Oracle's functions.

Such as: Commonly used decode, case, analysis functions, logic clear, powerful, more than their brains to write a heap of nonsense.

8. The aggregation operation takes unnecessary columns, and the columns are manipulated (or sorted).

such as: select COUNT (1) from (select T.empno,t.ename,... from Scott.emp e ...

where ... order by 1 ... ）;

Do you need this?

9. Problem with row contention.

frequent DML operations on some tables (incremental deletions) can cause serious row contention. The first is that the table design is unreasonable, and the other is the inefficient execution of the related statement . To reduce, one is to divide these DML operations into intermediate temporary tables, current tables, history tables, and to improve the quality of the statements in the design.

10. The handling of business logic is complex, not to speak of.

to sum up, the idea of SQL optimization is very important, of course, money is also very important (project management of four major elements, time, cost, quality, scope, that and money does not have a relationship), so in time and cost is not the premise of the problem, I think it can still look, if the time and cost is a problem, then the trouble.

This article is from the "srsunbing" blog, make sure to keep this source http://srsunbing.blog.51cto.com/3221858/1636652

Summary of recent SQL optimizations