Principles for optimizing SQL in Oracle

1. The statements that have been validated are exactly the same as those already in the shared pool

2. Variable names as consistent as possible

3. Rational use of outer joins

4. Use fewer layers of nesting

5. Multi-use concurrency

The optimization steps for a statement are generally:

1. The SGA area is adjusted so that the SGA area is optimally used.

2. optimization of the SQL statement itself, tools have explain,sql trace, etc.

3. Database Structure Tuning

4. Project Structure Adjustment

The experience of writing statements:

1. Use indexes on queries for large tables

2, less use in,exist and so on

3. Use Set operation

1. For columns in a large table query, try to avoid transformations such as To_char,to_date,to_number

2. Indexes are indexed as far as possible, the conditions useful to the index are written in the front if possible and necessary to establish some indexes

3. Avoid all table scans as much as possible, so that you can search faster to find the data you want to query