The Oracle Optimizer evaluates expressions whenever possible and converts a particular syntax structure to an equivalent structure, and this is because:
• Either the resulting expression can have a faster speed than the source expression.
• Either the source expression is just an equivalent semantic structure of the result expression.
Different SQL structures sometimes have the same operations (for example: = any (subquery) and in (subquery)), and Oracle maps them to a single semantic structure.
The following discusses how the optimizer evaluates the scenarios and expressions that are optimized as follows:
Constant like operator in operator any and some operator all operator BETWEEN operator not operator
Transitive (transitivity) deterministic (deterministic) function
Constant
A constant is evaluated as a one-time completion when the statement is optimized, rather than at each execution time. The following is an expression that retrieves a monthly salary greater than 2000:· sal > 24000/12
· sal > 2000
· sal*12 > 24000
If the SQL statement includes the first case, the optimizer simply converts it to the second.
Note: The optimizer does not simplify expressions that span comparison characters, such as the third statement, so the application developer should try to write an expression that is retrieved with a constant and a field, rather than placing the field in an expression.
Like operator
The optimizer converts a retrieval expression that uses the LIKE operator and an expression that has no wildcard character to an "=" operator expression.
For example, the optimizer converts expression ename like ' smith ' to ename = ' Smith ' optimizer can only convert expressions involving variable-length data types, in the previous example, if the type of the Ename field is char (10), then the optimizer will not do any conversion.
In operator
The optimizer replaces the retrieval expression using the in-comparison character with the equivalent retrieval expression using the "=" and "or" operators. For example, the optimizer replaces an expression ename in (' SMITH ', ' KING ', ' JONES ') with:ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES'
Any and some operators
The optimizer will follow any and some retrieval conditions of the (following) value list with equivalent equivalents and "or" expressions.
For example, the optimizer replaces the first statement shown below with the second statement:· sal > ANY (:first_sal, :second_sal)
· sal > :first_sal OR sal > :second_sal
The optimizer converts any and some retrieval criteria that follow the subquery into a retrieval expression consisting of "EXISTS" and a corresponding subquery.
For example, the optimizer replaces the first statement shown below with the second statement:· x > ANY (SELECT sal FROM emp WHERE job = 'ANALYST')
· EXISTS (SELECT sal FROM emp WHERE job = 'ANALYST' AND x > sal)