Some suggestions for improving SQL execution efficiency

Several suggestions for improving SQL execution efficiency:

Try not to include subqueries in the Where;

About the time of the query, try not to write: where To_char (dif_date, ' yyyy ') =to_char (' 2007-07-01 ', ' yyyy ');

In the filter condition, the condition that can filter out the maximum number of records must be placed at the end of the WHERE clause;

The last table (the underlying table, driving table), which is written in the FROM clause, will be processed first, and in the case where multiple tables are included in the FROM clause, you must select the table with the least number of records as the underlying table. If there are more than three connection queries, it is necessary to select the Crosstab table (intersection table) as the underlying table, which is the table referenced by the other tables;

Using binding variables

Try not to use or in a where

Substituting exists instead of in, using not exists instead of in;

Avoid using calculations on indexed columns: where sal*12>25000;

Replace Or:where loc_id=10 or loc_id=15 or loc_id=20 with in

Avoid using is null and is not NULL on indexed columns;

Always use the first column of the index;

substituting Union-all for union;

Avoid changing the type of indexed columns: SELECT ... From EMP where empno= ' 123 ', because of the implicit data type conversion, TO_CHAR (EMPNO) = ' 123 ', therefore, will not use the index, generally in the use of string patchwork dynamic SQL statements appear;

'!= ' will not use the index;

Optimize GROUP by;

Avoid wildcard characters with like parameters, like ' 4ye% ' uses indexes, but like '%ye ' does not use indexes

Avoid the use of difficult formal expressions, such as SELECT * from customer where zipcode like "98___", even if an index is established on ZipCode, in which case the sequential scan is used. If the statement is changed to select * from customer where zipcode> "98000", the query will be executed using the index to query, obviously greatly improve the speed;

Make the SQL statements as clear as possible, and minimize the database work. For example, when you write a SELECT statement, you need to explicitly specify the table name for the query's fields. Try not to use the SELECT * statement. Organize SQL statements as much as possible according to database habits