Oracle8i's process based on rule-based optimization mechanisms

The Oracle Optimizer will evaluate the expression in any possible time, and convert a specific syntax structure into an equivalent structure. The reason for this is:

· Either a result expression can be faster than source expressions

· Ordered source expressions just one equivalent semantic structure of the result expression

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

The following will discuss how the optimizer evaluates the following situations and expressions: Constant Like Operator IN Operator Any and Some Operator All Operators BetWeen Operator NOT Operation Passivity (DETERMINISTIC) Function

constant

The calculation of constants is done once when the statement is optimized, not at each execution. Below 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 into a second.

Note: Optimizer does not simplify expressions across comparison characters, such as third statements, in view of this, application developers should try to retrieve expressions with frequently compared to fields without placing fields in expressions.

LIKE operator

The optimizer converts the retrieval expression of the LIKE operator and a expression that does not have wildcard to a "=" operator expression.

For example: the optimizer converts expression ename like 'smith' to ename = 'smith'

Optimizer can only convert an expression involving a variable length data type, in the previous example, if the type of ename field is char (10), the optimizer will not do any conversion.

In operator

The optimizer replaces the retrieval expression using the IN comparator to use the "=" and "OR" operator retrieval expression.

For example, an optimizer will replace the expression ename in ('Smith', 'King', 'Jones')

ENAME = 'smith' or ename = 'king' or ename = 'Jones'

Any and Some operators

The optimizer will replace the ANY and Some retrieve conditions of the FOLLOWING value list with the equivalent operator and "OR" expression.

For example, an optimizer will replace the first statement as follows:

· Sal> any (: first_sal,: second_sal)

· Sal>: first_sal or sal>: second_sal

The optimizer converts the ANY and SOME search conditions of the subquery into the retrieval expression consisting of "exists" and a corresponding subquery.

For example, an optimizer will replace the first statement as follows:

· X> Analy (Select Sal from Emp where Job = 'Analyst')

· EXISTS (SELECT SAL from Emp where job = 'analysis "and x> sal)

ALL operator

The optimizer replaces the ALL operator of the following value list with the expression of equivalent "=" and "AND".

For example, Sal> all (: first_sal,: second_sal) Expression is replaced with: Sal>: first_sal and sal>: second_sal

For the ALL expression of the subqueries, the optimizer is replaced with an expression of Any and another suitable comparison.

For example, an optimizer will replace the expression X> All (Select Sal from Emp where deptno = 10):

NOT (x <= any (select sal from emp where deptno = 10)))

Next, the optimizer converts the second expression to the ANY expression conversion rule to the following expression:

NOT EXISTS (SELECT SAL FROM EMP Where Deptno = 10 and x <= SAL)

BetWeen operator

The optimizer always uses "> =" and "<=" to compare the Equivalent of the BetWeen operator.

For example, an optimizer will replace the expression Sal BetWeen 2000 and 3000 with Sal> = 2000 and Sal <= 3000.

NOT operator

The optimizer always tries to simplify the search condition to eliminate the "Not" logic operator, which will involve the "NOT" operator elimination and the corresponding comparison operator.

For example, an optimizer will use the second statement by the following first statement:

· Not deptno = (Select Deptno from Emp where ename = 'Taylor')

· Deptno <> (SELECT Deptno from Emp where ename = 'taylor')

Normally, a statement containing the NOT operator has many different ways, the optimizer's conversion principle is to make the clause of the "Not" operator as simple as possible, even if it may make the result expression contain more "" NOT "operator.

For example, the optimizer will use the first statement as shown in the second statement:

· Not (Sal <1000 or Comm is Null)

· NOT SAL <1000 and Comm is not null sal> = 1000 and common is not null

Transitivity

If the two retrieval conditions of the "WHERE" clause involve a common field, the optimizer sometimes infers the third search criteria based on the transfer principle, which can then optimize the statement, infer the conditions according to this inferred condition. A potential interface path that may be activated without activation (Access Path).

Note: Transfer is only used in an optimization based on cost-based.

Suppose there is a "WHERE" clause containing two retrieval conditions: WHERE field 1 constant and field 1 = field 2, in this example, the optimizer will infer a new search criterion: Field 2 Constant. Here, is any one of the comparison operators =,! =, ^ =, <>,>, <= Or> =, means that any one involves operators, SQL functions, text, tie A constant expression of a Bind variables or a correlating variables (CORRELATION VARIABLES). For example, consider a query of a WHERE clause containing two retrieval conditions using field Emp.Deptno:

Select * from Emp, Dept where Emp.deptno = 20 and Emp.deptno = dept.deptno;

With delivery optimization, the optimizer will infer as follows: dept.deptno = 20

If the index is stored on the EMP.DEPTNO field, this condition will make the interface path that calls this index is valid.

Note: The optimizer can only deliver the expression of the field associated constant, not the expression of the field associated field. For example, a WHERE clause containing such conditions: Field 1 field 3 and field 1 = field 2, this condition cannot be inferred: Field 2 field 3.

Deterministic function

In some cases, the optimizer can use the previous function to return the result instead of re-executing the user-defined function, this is only effective for those functions performed in a limitation. These functions must have the same return value for any input. The result of the function must not vary depending on the package variable, database or session parameters (such as NLS parameters), if the function is redefined in the future, The return value must still be the same as the previous return value for any parameters. The creator of the function can use the DETERMINISTIC keyword to declare the function to determine the function, and the database does not verify the legality of the deterministic function according to the requirements of the DETERMINISTIC keyword, or the database. Even if a function uses a package variable or operates a database, it can still be defined as a deterministic function, which means how security legitimate use and definition deterministic functions are programmers.

When the deterministic function is called multiple times in the same query, or when it is called based on a function-based index or a materialized view, it is possible to replace it with a calculated value.