Conditional Optimization Technology

Condition:

The expression used to filter and connect tuples,Is the expression that appears in the where/join-on/having clause.

1. Conditional push-down

Place the conditions related to a single table in the Process of scanning a single table.

2. Condition simplification

For: the where, having, and join-on conditions are composed of many expressions, and in some cases,There is a certain relationship between them.

(1) Incorporate having conditions into where conditions

Not all having conditions can be incorporated into the where condition in any case, and only the having condition does not exist in the SQL statement.The having condition and the where condition can be combined only when the groupby condition or clustering function is used.And.

Benefits: it is easy to unify and centralize condition clauses, saving time for multiple resolutions

(2) Remove redundant parentheses in the expression

This reduces the levels of and or trees generated during syntax analysis. --- Reduce CPU consumption

(A and B) and (C and D) = "A and B and C and D

(3) constant transfer

For different relationships, we can effectively implement "select push-down" after condition separation, which can greatly reduceThe scale of the inter-relationship.

Col_1 = col_2 and col_2 = 3 => col_1 = 3 and col_2 = 3

Any of the operators "=, <,>, <=, >=, <>, <=>, and like", in the "col_1 <Operator> Constant transfer may occur in col_2 conditions.

(4) eliminate dead code

Simplify conditions to remove unnecessary conditions

Where (0> 1 and S1 = 5) = where S1 = 5

(5) expression Calculation

Where col_1 = 1 + 2 = "where col_1 = 3

(6) Equality Transformation

Simplify conditions (such as reversing the order of the operands of Relational operators) to change the access path of some tables.

-A = 3 = "a =-3, which can be indexed

(7) Inequality Transformation

Simplify conditions to remove unnecessary duplicate conditions.

A> 10 and B = 6 and a> 2 =, B = 6 and a> 10

(8) Boolean expression Transformation A. predicate transfer Closure

B. the Boolean expression is converted into an equivalent CNF)

C. Index Utilization