Common SQL Tuning "one"

1. Use the index to avoid using calculations on indexed columns.

2. Replace > <= with >= <

3. Replace or with union (for indexed columns)

4. Replace or with in, or replace in with exists

5 Replace order by with where. The ORDER by clause uses the index only under two strict conditions.
All columns in an order by must be in the same index and remain in the order in which they are arranged in the index. All columns in the ORDER by must be defined as non-empty.

6. Note The connection order in the WHERE clause. Oracle uses a bottom-up sequential parsing where clause, according to which the connection between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.

7. Avoid using ' * ' in the SELECT clause

8. Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table.

9. Use cursors with caution. Cursors are a very large drain on database resources (especially memory and lock resources) by placing the result set in server memory and by looping through a single processing record. Try to use an explicit cursor (CURSORs). With an implicit cursor, two operations will be performed.

10. Use larger buffer (e.g. 10MB, 10,240,000) to increase the speed of export and import

11. Alias for use table when you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.

12. Improve SQL efficiency with intrinsic functions.

13. Reduce queries to tables especially in SQL statements that contain subqueries, pay particular attention to reducing queries on tables

14. Whenever possible, use commit as much as you can in the program, so that the performance of the program is improved and the requirements are reduced by the resources freed by the commit:

15. Replace Delete with Truncate when deleting table data to free up physical space

16. The most efficient way to delete duplicate records (because ROWID is used)
DELETE from EMP E
WHERE E.rowid >; (SELECT MIN (X.ROWID)
From EMP X
WHERE x.emp_no = e.emp_no);

17. Avoid using large print segments

18.count (*) is slightly faster than count (1), of course, if you can retrieve by index, the count of indexed columns is still the fastest. 19. Replace the HAVING clause with a WHERE clause to avoid having a HAVING clause, having The result set is filtered only after all records have been retrieved

19. Avoid using like ' * ' to avoid using is null or

20.

Note:

A, programmers pay attention to the amount of data in each table.

B, the coding process and the unit test process as far as possible with a large amount of data database testing, it is best to use actual data testing.

C, each SQL statement as simple as possible

D, do not update data for tables that have triggers frequently

E, note the limitations of database functions and their performance

F. Use the Tkprof tool to query SQL performance status

G. Parsing SQL statements with explain PLAN

Common SQL Tuning "one"