Optimize SQL statements in Oracle

We all know that indexes are related to table concepts and mainly improve the efficiency of data retrieval. When Oracle uses a complex self-balancing B-tree structure. We generally query data through indexes faster than full table scans. When Oracle finds the best path for executing the query and Update statements, Oracle optimization uses indexes. Also used when joining multiple tables

We all know that indexes are related to table concepts and mainly improve the efficiency of data retrieval. When Oracle uses a complex self-balancing B-tree structure. We generally query data through indexes faster than full table scans. When Oracle finds the best path for executing the query and Update statements, Oracle optimization uses indexes. Also used when joining multiple tables

We all know that indexes are related to table concepts and mainly improve the efficiency of data retrieval. When Oracle uses a complex self-balancing B-tree structure. We generally query data through indexes faster than full table scans. When Oracle finds the best path for executing the query and Update statements, Oracle optimization uses indexes. Using indexes to join multiple tables also improves efficiency.

Another advantage of using an index is that it provides uniqueness verification of the primary key. For those LONG or long raw data types, You Can index almost any column. Generally, using indexes in large tables is particularly effective. Of course, you will also find that using indexes in small tables can also improve efficiency. Although the index can improve the query efficiency, we must pay attention to its cost.

The index requires space for storage and regular maintenance. The index itself is also modified whenever a record is increased or decreased in the table or the index column is modified. This means that the INSERT, DELETE, and UPDATE operations for each record will pay four or five more disk I/O operations. Because indexes require additional storage space and processing, unnecessary indexes will slow the query response time. Regular index reconstruction is necessary:

ALTER INDEX REBUILD

1. Replace DISTINCT with EXISTS:

When you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using DISTINCT in the SELECT clause. In general, we can consider replacing it with EXIST. EXISTS makes the query more rapid, because the RDBMS core module will return results immediately after the subquery conditions are met. Example:

(Inefficient): select distinct DEPT_NO, DEPT_NAME from dept d, emp e where d. DEPT_NO = E. DEPT_NO (efficient): SELECT DEPT_NO, DEPT_NAME from dept d where exists (SELECT 'x' from emp e where e. DEPT_NO = D. DEPT_NO );

2. SQL statements are written in upper case, because Oracle always parses SQL statements first, converts lowercase letters into upper case letters, and then executes them.

3. Use the connector "+" to connect strings as little as possible in Java code.

4. Avoid using NOT in the index column. Avoid using NOT in the index column. NOT will have the same effect as using the function in the index column. When Oracle "Encounters" NOT ", it will stop using indexes and then perform full table scanning.

5. Avoid using computation on index columns. In the WHERE clause, if the index column is a part of the function. The Oracle optimizer uses full table scanning instead of using indexes.

Example:

Inefficient: SELECT... From dept where sal * 12> 25000; efficiency: SELECT... From dept where sal> 25000/12;

6. Replace with> =>:

Efficient: SELECT * from emp where deptno> = 4 inefficient: SELECT * from emp where deptno> 3

The difference between the two lies in that the former DBMS will directly jump to the first record with DEPT equal to 4, while the latter will first locate the record with DEPTNO = 3 and scan forward to the record with the first DEPT greater than 3.

7. replace OR with UNION (applicable to index columns ):

In general, replacing OR in the WHERE clause with UNION will produce better results. Using OR for an index column will cause a full table scan. Note that the preceding rules are only valid for multiple index columns. If a column is not indexed, the query efficiency may be reduced because you did not select OR. In the following example, both LOC_ID and REGION have indexes.

Efficient: SELECT LOC_ID. LOC_DESC, region from location where LOC_ID = 10 union select LOC_ID, LOC_DESC, region from location where region = "MELBOURNE"

Inefficient: SELECT LOC_ID, LOC_DESC, region from location where LOC_ID = 10 or region = "MELBOURNE"

8. replace OR with IN:

This is a simple and easy-to-remember rule, but the actual execution results must be tested. in Oracle8i, the execution paths of the two seem to be the same:

Inefficiency:

SELECT .... From location where LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30

Efficient:

SELECT... From location where LOC_IN IN (10, 20, 30 );

9. Avoid using is null and is not null in the index column:

To avoid using any column that can be empty in the index, Oracle will not be able to use the index. For a single column index, if the column contains a null value, this record will not exist in the index. For a composite index, if each column is empty, this record does not exist in the index. If at least one column is not empty, the record is stored in the index. For example, if the unique index is created in column A and column B of the table, and the and B values of A record exist in the table are (123, null ), oracle will not accept the next record with the same A and B values (123, null) (insert ).

However, if any index column is empty, Oracle considers that the entire key value is null and null is not equal to null. Therefore, you can insert 1000 records with the same key value. Of course, they are empty! Because the null value does not exist in the index column, the Null Value Comparison of the index column in The WHERE clause will disable Oracle.

Inefficiency: (index failure)

 
 

  
  
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 
 
 

Efficient: (the index is valid)

 
 

  
  
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0; 
 
 

10. Always use the first column of the index:

If the index is created on multiple columns, the Oracle optimizer selects the index only when its first column (leading column) is referenced by the where clause. This is also a simple and important rule. When only the second column of the index is referenced, the optimizer uses a full table scan and ignores the index.

11. Replace UNION with UNION-ALL (if possible ):

When an SQL statement needs to UNION two query result sets, these two result sets are merged in the form of UNION-ALL and sorted before the final result is output. If union all is used to replace UNION, sorting is unnecessary. The efficiency will be improved accordingly. Note that union all will repeatedly output the same records in the two result sets. Therefore, you still need to analyze the feasibility of using union all from the business needs. UNION sorts the result set. This operation uses SORT_AREA_SIZE memory. Oracle Optimization for this memory is also very important. The following SQL statements can be used to query the consumption of sorting:

Inefficient: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95 'union select ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' efficiency: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95 'union all select ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95'

If you are a beginner in Oracle multi-Table query optimization and do not know much about the actual application of Oracle multi-Table query optimization, the following articles will provide you with more comprehensive knowledge. The following is a detailed description of the article.

Oracle uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join between tables must be written before other WHERE conditions. The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

For example:

(Inefficient, execution time: 156.3 seconds)

 
 

  
  
SELECT … 
  
  
FROM EMP E 
  
  
WHERE SAL >; 50000 
  
  
AND JOB = ‘MANAGER’ 
  
  
AND 25 < (SELECT COUNT(*) FROM EMP 
  
  
WHERE MGR=E.EMPNO); 
 
 

(Efficient, execution time: 10.6 seconds)

 
 

  
  
SELECT … 
  
  
FROM EMP E 
  
  
WHERE 25 < (SELECT COUNT(*) FROM EMP 
  
  
WHERE MGR=E.EMPNO) 
  
  
AND SAL >; 50000 
  
  
AND JOB = ‘MANAGER’; 
 
 

Focus on 3: Avoid using '*' in the SELECT clause. Focus on

When you want to list all columns in the SELECT clause, using dynamic SQL COLUMN reference '*' is a convenient method. unfortunately, this is a very inefficient method. in fact, Oracle converts '*' into all column names in sequence during parsing. This task is completed by querying the data dictionary in Oracle Multiple tables, this means that it will take more time.

7. Reduce the number of database accesses

When each SQL statement is executed, Oracle performs a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks. it can be seen that reducing the number of visits to the database can actually reduce the workload of Oracle.

For example,

There are three ways to retrieve employees with employee numbers equal to 0342 or 0291.

Method 1 (most inefficient)

 
 

  
  
SELECT EMP_NAME , SALARY , GRADE 
  
  
FROM EMP 
  
  
WHERE EMP_NO = 342; 
  
  
SELECT EMP_NAME , SALARY , GRADE 
  
  
FROM EMP 
  
  
WHERE EMP_NO = 291; 
 
 

Method 2 (low efficiency)

 
 

  
  
DECLARE 
  
  
CURSOR C1 (E_NO NUMBER) IS 
  
  
SELECT EMP_NAME,SALARY,GRADE 
  
  
FROM EMP 
  
  
WHERE EMP_NO = E_NO; 
  
  
BEGIN 
  
  
OPEN C1(342); 
  
  
FETCH C1 INTO …,..,.. ; 
  
  
OPEN C1(291); 
  
  
FETCH C1 INTO …,..,.. ; 
  
  
CLOSE C1; 
  
  
END; 
 
 

The above content is an introduction to Oracle multi-Table query optimization. I hope you will find some gains.