How to evaluate and optimize the optimizer in Oracle

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)

All operator

The optimizer replaces the all operator that follows the list of values with an expression that is equivalent to "=" and "and".

For example, Sal > All (: First_sal,: second_sal) expressions are replaced by:sal > :first_sal AND sal > :second_sal

For the all expression that follows the subquery, the optimizer replaces the expression consisting of any and another appropriate comparison character. For example, the optimizer replaces expression x > All (SELECT sal-from emp WHERE DEPTNO = 10) with:NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = 10))

The optimizer then converts the second expression to the following expression, which applies to the conversion rule of any expression:NOT EXISTS (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal)

BETWEEN operator

The optimizer always replaces the between operator with the ">=" and "<=" comparison characters. For example, the optimizer replaces the expression Sal BETWEEN and 3000 with Sal >= and Sal <= 3000来.

Not operator

The optimizer always attempts to simplify the retrieval criteria to eliminate the effect of the "not" logical operator, which involves the elimination of the "not" operator and substituting 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')

Usually a statement containing the NOT operator has many different ways of writing, and the optimizer's conversion principle is to make the clause behind the "not" operator as simple as possible, even if the resulting expression contains more "not" operators. For example, the optimizer replaces the first statement shown below with the second statement:· NOT (sal < 1000 OR comm IS NULL)

· NOT sal < 1000 AND comm IS NOT NULL sal >= 1000 AND comm IS NOT NULL

Transfer (transitivity)

If the two search criteria for a "WHERE" clause involve a common field, the optimizer sometimes infers a third search condition based on the principle of passing, and then optimizes the statement based on the inferred condition. The inferred condition may activate a potential interface path (access path) where the original search condition has not been activated. Note: Delivery is only used in cost-based optimization (cost-based).

Suppose there is a "where" clause that contains two search criteria: where field 1 constant and field 1 = Field 2, in this case the optimizer infers a new retrieval condition: Field 2 constant. Here, any of the comparison operators =,!=, ^=, <>, >, <=, or >=, constants refer to any one involving operators, SQL functions, literals, bound variables (bind variables) or a constant expression of an associated variable (correlation variables).

For example, consider a query that contains two WHERE clauses that each use the search criteria of a field Emp.deptno:SELECT * FROM emp, dept WHERE emp.deptno = 20 AND emp.deptno = dept.deptno;

Using transfer optimization, the optimizer infers the following conditions: Dept.deptno = 20. If an index exists on the Emp.deptno field, this condition will make the interface path that calls this index valid. Note: The optimizer can only infer an expression for a field-associated constant, not an expression of the field's associated field. For example, where clause containing such a condition: Field 1 Field 3 and field 1 = Field 2, this cannot infer an expression: Field 2 < comp_oper> field 3.

Deterministic (deterministic) functions

In some cases, the optimizer can use previous functions to return results instead of performing user-defined functions, which is only valid for functions that are executed in a limited way. These functions must have the same return value for any input, and the result of the function must not vary depending on the package (PACKAGE) variable, database or session (for example, NLS parameters), if the function is redefined in the future, The return value must remain the same as the previous return value for any parameter. The creator of the function can use the Deterministic keyword to declare the function to be deterministic when the function is declared with a CREATE function, create package, or create type, based on the above requirements. The database does not validate the legality of deterministic functions, even if a function apparently uses a package variable or operates a database, it can still be defined as a deterministic function, which means that it is the programmer's responsibility to use and define deterministic functions safely and legitimately.

When a deterministic function is called multiple times in the same query, or is invoked by a function based index or materialized view (materialized view), it is possible to be replaced by a value that has already been computed.