Oracle Where clause research conclusion

Before giving you a detailed introduction to the Oracle Where clause, let's first take a look at the order by clause, and then introduce how to replace order by with the Oracle Where clause:
◆ 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 sorting in the index.
◆ Any column in order by must be defined as non-empty.

The indexes used BY the Oracle Where clause and those 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 
 
 

Avoid changing the index column type:

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.

Oracle Where clause that requires caution:

Oracle Where clauses in some SELECT statements do 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.
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.

1. Oracle LogMiner
2. Unveil the Oracle LogMiner mystery
3. Brief description of Oracle Clob type
4. Overview Oracle 10g Server
5. Introduction to Oracle xmltype