How to evaluate and optimize the optimizer in Oracle

Source: Internet
Author: User
Tags comparison constant expression

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.