How to evaluate and optimize the optimizer in Oracle

The Oracle optimizer evaluates the expression whenever possible and converts a specific syntax structure to an equivalent structure. The reason for doing this is: either the result expression can be faster than the source expression. Either the source expression is only an equivalent Semantic Structure of the result expression. Different SQL structures sometimes have the same operation (for example,

The Oracle optimizer evaluates the expression whenever possible and converts a specific syntax structure to an equivalent structure. The reason for doing this is: either the result expression can be faster than the source expression. Either the source expression is only an equivalent Semantic Structure of the result expression. Different SQL structures sometimes have the same operation (for example, =

The Oracle optimizer evaluates the expression whenever possible and converts a specific syntax structure to an equivalent structure. The reason for doing this is:

· Either the result expression can be faster than the source expression.

· Either the source expression is only an equivalent Semantic Structure of the result expression.

Different SQL structures sometimes have the same operation (for example, = ANY (subquery) and IN (subquery). Oracle maps them to a single semantic structure.

The following describes how the optimizer evaluates and optimizes the following conditions and expressions:

Constant LIKE operator IN operator ANY and SOME operator ALL operator BETWEEN operator NOT operator

Pass (transitiistic) DETERMINISTIC (DETERMINISTIC) Function

Constant

Constant calculation is completed at one time when the statement is optimized, rather than at each execution. The following is an expression for retrieving 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 case.

Note: The optimizer does not simplify expressions that span comparison characters. For example, in the third statement, application developers should try to write expressions that are searched by comparing constants with fields, do not place fields in expressions.

LIKE Operator

The optimizer converts a search expression consisting of the LIKE operator and an expression without wildcards into a "=" operator expression.

For example, the optimizer converts the expression ename LIKE 'Smith 'to ename = 'Smith'. The optimizer can only convert expressions that involve variable-length data types. In the previous example ,, if the type of the ENAME field is CHAR (10), the optimizer will not convert it.

IN Operator

The optimizer replaces the search expression using the IN comparison operator with the equivalent search expression using the "=" and "OR" operators. For example, the optimizer replaces the expression ename IN ('Smith ', 'King', 'Jones'): ename = 'Smith 'OR ename = 'King' OR ename = 'Jones'

ANY and SOME operators

The optimizer replaces ANY and SOME search conditions in the following (following) Value List with expressions consisting of equivalent operators and "OR.

For example, the optimizer replaces the first statement with the second statement: · sal> ANY (: first_sal,: second_sal)

· Sal>: first_sal OR sal>: second_sal

The optimizer converts ANY and SOME search conditions that follow subqueries into a search expression consisting of "EXISTS" and a corresponding subquery.

For example, the optimizer replaces the first statement with the second statement as follows: · x> ANY (SELECT sal FROM emp WHERE job = 'analyst ')

· EXISTS (SELECT sal FROM emp WHERE job = 'analyst' AND x> sal)

ALL Operator

The optimizer replaces the ALL operator following the Value List with an equivalent expression consisting of "=" AND.

For example, the expression sal> ALL (: first_sal,: second_sal) is replaced with: sal>: first_sal AND sal>: second_sal.

For the ALL expression that follows the subquery, the optimizer replaces it with an expression consisting of ANY and another suitable comparison operator. For example, the optimizer replaces the expression x> ALL (SELECT sal FROM emp WHERE deptno = 10) with NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = 10 ))

Next, the optimizer converts the conversion rule of the second expression that applies to the ANY expression to the following expression: not exists (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal)

BETWEEN Operator

The optimizer always uses the comparison operators "> =" and "<=" to replace the BETWEEN operator. For example, the optimizer replaces the expression sal BETWEEN 2000 AND 3000 with sal> = 2000 AND sal <= 3000.

NOT Operator

The optimizer always tries to simplify the search conditions to eliminate the impact of the "NOT" logical operator, which involves the elimination of the "NOT" operator and the generation of the corresponding comparison operator.

For example, the optimizer replaces the following first statement with the second statement: · NOT deptno = (SELECT deptno FROM emp WHERE ename = 'taylor ')

· Deptno <> (SELECT deptno FROM emp WHERE ename = 'taylor ')

Generally, there are many different statements containing the NOT operator. The optimizer's conversion principle is to make the clauses behind the "NOT" operator as simple as possible, even if the result expression may contain more "NOT" operators. For example, the optimizer replaces the first statement with the second statement as follows: · NOT (sal <1000 OR comm is null)

· NOT sal <1000 AND comm is not null sal> = 1000 AND comm IS NOT NULL

Transfer)

If the two search conditions in the WHERE clause involve a common field, the optimizer sometimes deduce the third Search Condition Based on the transfer principle, then, the statement can be optimized based on the inferred condition. The inferred condition may activate a potential interface path (access path) where the original search condition is not activated ). Note: The transfer is only used in cost-based optimization.

Suppose there is a "WHERE" clause that contains two search conditions: WHERE field 1 constant AND Field 1 = Field 2. In this example, the optimizer will infer the new search condition: field 2 constant. Here, the comparison operator = ,! =, ^ =, <>,>, <= Or> =. A constant is a constant that involves operators, SQL functions, text, bind variables) or the constant expression of the correlated variable (correlation variables.

For example, consider that the two fields respectively use EMP. query of the WHERE clause of the DEPTNO Search Condition: SELECT * FROM emp, dept WHERE emp. deptno = 20 AND emp. deptno = dept. deptno;

With Transfer optimization, the optimizer will infer the following conditions: dept. deptno = 20. If an index exists in the EMP. DEPTNO field, this condition will make the interface path that calls this index valid. Note: The optimizer can only deduce the expression of the field associated constant, rather than the expression of the field associated with the field. For example, if the WHERE clause contains the following conditions: Field 1 Field 3 AND Field 1 = Field 2, the expression cannot be inferred: Field 2 <comp_1_> Field 3.

DETERMINISTIC Functions

In some cases, the optimizer can use previous functions to return results rather than re-Execute user-defined functions, which is only effective for functions executed in a restricted manner. These functions must return the same value for any input, and the result of the function must not be a result of PACKAGE variables, database or SESSION parameters (such as NLS parameters) if the function is redefined in the future, the return value must be the same for any parameter as the previous return value. When declaring a FUNCTION using create function, create package, or create type, the FUNCTION creator can use the DETERMINISTIC keyword to declare the FUNCTION to the database as a deterministic function, the database does not validate the validity of a deterministic function. Even if a function explicitly uses a package variable or operates the database, it can still be defined as a deterministic function, this means that it is the programmer's responsibility to safely and legally use and define deterministic functions.

When a deterministic function is called multiple times in the same query, or called by a function-based index or materialized view, it may be replaced by a calculated value.