Oracle's SQL statement optimization

Today is the last day of work in April, the holiday will start tomorrow! ~ Three days! I hope I can make use of these days to fill a good professional knowledge, this evening began to learn SQL optimization course!

General optimization

1: Try to avoid using * list all columns, because the system will consume a certain amount of time to resolve the *, which makes SQL slow

2:truncate instead of delete, using truncate instead of Delete will be quicker when the data is determined, and he will not be able to put the data into the undo table space, of course, rollback recovery cannot be used with truncate.

TRUNCATE TABLE table_name DROP STORAGE;

3: In the case of ensuring integrity, more commit, timely release of resources, can be released: Rollback segment data, the lock obtained by program statements, space in redo log buffer, Oracle to manage the internal cost of the above resources

4: Minimize the number of queries to the table

5: Replace in with exists

In subqueries, the IN clause performs an internal sort and merge, resulting in inefficiency

Table Connection Optimization

1: Selection of the driver table

Driver table: The first accessed table (full scan mode access), usually followed by a full scan from the

The order of 2:where sentence connections

The connection between tables is written in front, filtering the maximum unloading end

Fair use Index

This article is from the "8424270" blog, please be sure to keep this source http://8434270.blog.51cto.com/8424270/1769141

Oracle's SQL statement optimization