Some practical suggestions on Oracle SQL Execution Efficiency

The following articles mainly introduce some useful suggestions for improving the execution efficiency of Oracle SQL. The following describes the specific solution and hopes to help you in your future studies. To improve the efficiency of Oracle SQL Execution. We 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('2007-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 FROM clause is written in the base table of the last table, and the driving table is processed first. If the FROM clause contains multiple tables, you must select a table with the least number of records as the base table. If there are more than three join queries, You need to select the cross table intersection table as the basic table. The cross table refers to the table referenced by other tables;

Bind variables

Do not use OR in WHERE

Use EXISTS to replace IN and not exists to replace not in;

Avoid using computation on index columns:

 
 

  
  
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='123' 
 
 

Due to implicit data type conversion, to_char (EMPNO) = '000000'. Therefore, no index is used. Generally, dynamic Oracle 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

 
 

  
  
select * from customer where zipcode like "98___" 
 
 

Even if an index is created on zipcode, sequential scanning is used in this case. If you change the statement

 
 

  
  
select * from customer where zipcode>"98000" 
 
 

Indexes are used to query queries during query execution, which obviously greatly improves the speed;

Complete Oracle 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.