Oracle UNION ALL

Oracle has a lot to learn about. Here we mainly introduce Oracle union all, including UNION and other aspects. 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” 
 
 

Inefficiency:

 
 

  
  
SELECT LOC_ID ，LOC_DESC ，REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 
 
 

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); 
 
 

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, B value 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.

Always use the first column of the index:

If the index is created on multiple columns, the optimizer selects to use 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.

Replace UNION with Oracle union all (if possible ):

When an SQL statement needs to UNION two query result sets, these two result sets are merged in Oracle UNION ALL mode and sorted before the final result is output. If Oracle union all is used to replace UNION, sorting is unnecessary. The efficiency will be improved accordingly. Note that Oracle union all repeatedly outputs the same records in the two result sets. Therefore, you need to analyze the feasibility of using Oracle union all based on your business needs. UNION sorts the result set. This operation uses SORT_AREA_SIZE memory. The optimization of this memory is also very important.

1. Oracle Exists usage
2. Oracle FROM clause
3. Simple Analysis of Oracle Having clauses
4. Explore how Oracle uses LogMiner
5. Analyze Oracle reproduction logs for instances