Benefits of optimizing SQL statements in Oracle

If you want to better understand Oracle-optimized SQL statements, you must understand which SQL statements are frequently used in Oracle, the following is a detailed description of Oracle optimization SQL statements. I hope you will understand this article after reading it.

Replace order by with WHERE:

The order by clause only uses indexes under two strict conditions.

Any column in order by must be included in the same index and maintained in the ORDER of the index.

Any column in order by must be defined as non-empty.

The index used BY the WHERE clause and the index used in the order by clause cannot be tied together.

For example, the DEPT table contains the following columns:

 
 

  
  
DEPT_CODE PK NOT NULL  
  
  
DEPT_DESC NOT NULL  
  
  
DEPT_TYPE NULL  
 
 

Inefficiency: (indexes are not used)

 
 

  
  
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE 
 
 

Efficiency: (using indexes)

 
 

  
  
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0 
 
 

Avoid changing the index column type in Oracle-optimized SQL statements:

When comparing data of different data types, Oracle automatically performs simple type conversion on columns. Assume that EMPNO is a numeric index column: SELECT... From emp where empno = '20140901 '. In fact, after Oracle type conversion, the statement is converted to: SELECT... From emp where empno = TO_NUMBER ('123 ').

Fortunately, the type conversion does not occur on the index column, and the purpose of the index is not changed. Now, assume that EMP_TYPE is a character-type index column: SELECT... From emp where EMP_TYPE = 123.

This statement is converted to: SELECT… by Oracle... From emp WHERETO_NUMBER (EMP_TYPE) = 123. This index will not be used because of internal type conversion! To avoid implicit type conversion for your SQL statements, it is best to explicitly display the type conversion. Note that when comparing characters and values, Oracle converts the value type to the character type first.

WHERE clause to be careful:

The WHERE clause in some SELECT statements does not use indexes. Here are some examples:

(1 )'! = 'No index is used. Remember, indexes only tell you what exists in the table, but not what does not exist in the table.

(2) '|' is a character concatenation function. As with other functions, indexes are disabled.

(3) ''is a mathematical function. As with other mathematical functions, indexes are disabled.

(4) The same index Columns cannot be compared with each other, which enables full table scan.

32) a. If you retrieve more than 30% of the records in a table, using indexes will not significantly improve the efficiency.

B. In certain situations, using indexes may be slower than full table scanning, but this is an order of magnitude difference. In general, using an index is several times or even several thousand times more than a full table scan!

Avoid resource-consuming operations:

SQL statements with DISTINCT, UNION, MINUS, INTERSECT, and order by enable the SQL engine to execute resource-consuming sorting (SORT. DISTINCT requires a sorting operation, while other operations require at least two sorting operations. Generally, SQL statements with UNION, MINUS, and INTERSECT can be rewritten in other ways. Assume that the SORT_AREA_SIZE of your database is well configured.

The use of UNION, MINUS, and INTERSECT can also be considered. After all, they are highly readable.

Oracle optimized group:

Improve the efficiency of group by statements BY filtering out unnecessary records before group. The following two queries return the same results, but the second one is much faster.

Inefficient: select job, AVG (SAL) from emp group job having job = 'President 'or job = 'manager' efficiency: select job, AVG (SAL) from emp where job = 'President 'or job = 'manager' GROUP JOB