Evaluating Expressions and Conditions

The optimizer fully evaluated expressions whenever possible and translates certain syntactic constructs into equivalent constructs. The optimizer performs some of these evaluations because ORACLE can more quickly evaluate the resulting expressions than the original expression. Like

The optimizer simplifies conditions that use LIKE comparison operator to compare an expression with no wildcard characters into an equivalent condition that uses an equality operator instead.

For Example

ename LIKE 'SMITH' is simplified as

ename ='SMITH'

The optimizer expands a condition that uses the IN comparison operator equivalent condition that uses equality comparison operators and OR logical operators.

For example

ename IN('SMITH','KING','JONES') is simplified as

ename ='SMITH' OR ename ='KING' OR ename ='JONES'

The optimizer expands a condition that uses the ANY or SOME comparison operator followed by a parenthesized list of values to an equivalent condition that uses equally comparison operators and OR logical operators.

For example

sal > ANY(:first-sal,:second-sal) is simplified as

sal > first_sal OR sal > : second-sal

The optimizer expands a condition that uses the ALL comparison operator followed by a parenthesized list of values to an equivalent.

For example

sal > ALL):first_sal, :second-sal) is simplified as

sal > :first_sal AND sal > :second-sal
Between

The optimizer always replaces a condition that uses the BETWEEN comparison operator with an equivalent condition that uses the > =and< - comparison operators.

For example

sal BETWEEN 2000 AND 3000 is simplified as

sal > = 2000 AND sal < = 3000

The optimizer always simplifies a condition to eliminate the NOT logical operator. The simplification involves removing the NOT logical operator and replacing a comparison operator with its possible comparison operator.

For example

NOT deptno = (SELECT deptno FROM emp WHERE ename = 'NITHISH')

deptno < > (SELECT deptno FROM emp WHERE ename = 'NITHISH')

The optimizer transforms a complex statement into a join statement whenever such a transformation results in a join statement that is guaranteed to return exactly the same rows as the complex statement.

Considers this complex statement that selects all rows from the ACCOUNTS table whose owners appear in the CUSTOMERS table:

SELECT*

FROM accounts
WHERE custno IN
(SELECT custno FROM customers)

The optimizer can transform the complex query into this Join statement that is guaranteed to return the - same data:

SELECT accounts."

FROM accounts, customers
WHERE accounts,custno = customers.cusno

Optimizing statements that Access Views

Consider this view of all employees who work in department 10:

CREATE VIEW emp_10 AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE deptno = 10

Consider this query that access the view. The query select the ids greater than 7800 of employees who work in department 10 :

SELECT empno
FROM emp_10
WHERE cmpnoi > 7800

The optimizer transforms the query into the following query that access the view's base tables:

SELECT empno FROM emp
WHERE deptno = 10 AND empno > 7800

To choose the execution plan for a compound query the optimizer chooses an execution plan for each of. its component queries and then combines the resulting row sources with the union, intersection, or minus operation, depending on the set operator used in the compound query.

The execution plan for this statement that uses the UNION ALL operator to select all occurrences of all parts in either the ORDERS I table of the ORDERS2 table:

SELECT part FROM orders1
UNION ALL
SELECT part FROM orders2

The above statement can written according to the execution plan as

-SELECT part FROM orders 1
UNION
SELECT part FROM orders2

This execution plan is identical to the one for the UNION-ALL operator except that in this case ORACLE uses the SORT operation to eliminate the duplicates returned by the UNION-ALL operation.

Domain Name Search