Suggestions for improving SQL Execution Efficiency

Suggestions for improving SQL Execution efficiency:

◆ Try not to include subqueries in the where clause;

Do not write the Time query as follows: where to_char (dif_date, 'yyyy-mm-dd') = to_char ('2017-07-01 ', 'yyyy-mm-dd ');

◆ In the filter condition, the condition for filtering the maximum number of records must be placed at the end of the WHERE clause;

The base table (Driving table) Written In The from clause will be first processed. When the from clause contains multiple tables, you must select a table with the least number of records as the base table. If more than three join queries exist, you need to select an intersection table as the base table, which is the table referenced by other tables;

◆ Bind variables

◆ Do not use or in where

◆ Replace in with exists and not exists instead of not in;

◆ Avoid calculation on the index column: where Sal * 12> 25000;

◆ Replace or with in: Where loc_id = 10 or loc_id = 15 or loc_id = 20

◆ Avoid using is null and is not null in the index column;

◆ Always use the first column of the index;

◆ Replace union with Union-all;

◆ Avoid changing the index column type: select... from EMP where empno = '000000'. Due to implicit data type conversion, to_char (empno) = '000000', no index is used, generally, dynamic SQL statements are concatenated by strings;

◆ '! = 'No index is used;

◆ Optimize group;

◆ Avoid wildcards with the like parameter. Like '4ye % 'uses indexes, but like' % ye 'does not use indexes.

◆ Avoid using difficult regular expressions, such as select * from customer where zipcode like "98 ___". Even if an index is created on zipcode, in this case, sequential scanning is also used. If you change the statement to select * from customer where zipcode> "98000", the query will be executed using the index, which will obviously increase the speed;

◆ Complete SQL statements as clearly as possible and minimize database work. For example, when writing a SELECT statement, you must explicitly specify the table name of the queried field. Try not to use the select * Statement. Organize SQL statements according to database habits as much as possible