Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 26 November 2012

Cost Based Optimizer (CBO): Where Clause Order in Oracle

Generally in case of multicolumn index definition we are putting the most selective column first. The sole purpose of indexes is to make SQL queries run faster. If the optimizer detects an index that matches part of the WHERE clause of the query, then the optimizer will use the index to avoid having to read every row in the table.
Oracle always interrogates the WHERE clause of the SQL statement to see if a matching index exists and then evaluates the cost to see of the index is the lowest-cost access method. By using function-based indexes, the Oracle designer can create a matching index that exactly matches the predicates within the SQL where clause. This ensures that the query is retrieved with a minimal amount of disk I/O and the fastest possible speed. 

Usually it is giving better performance but did the ordering of where clause conditions have performance impact with the query. Suppose we had to run a SELECT on a fairly large table to check for a particular value. The search query I wrote had 2 WHERE clauses in it. One a DATE column and another was a VARCHAR2.
SELECT * FROM PAY_EMPLOYEE_PERSONAL_INFO
WHERE LATIN_NAME LIKE '%SHAHID%' AND LAST_SAL_DATE < SYSDATE - 30;

SELECT * FROM PAY_EMPLOYEE_PERSONAL_INFO
WHERE  LAST_SAL_DATE < SYSDATE - 30 AND LATIN_NAME LIKE '%SH%';

Oracle uses the following steps to evaluate the order of SQL predicates:
§         Sub queries are evaluated before the outer Boolean conditions in the WHERE clause.
§         All Boolean conditions without built-in functions or sub-queries are evaluated in reverse from the order they are found in the WHERE clause, with the last predicate being evaluated first.
§         Boolean predicates with built-in functions of each predicate are evaluated in increasing order of their estimated evaluation costs.
The problem is that the Oracle SQL optimizer might re-arrange the order of the where clause predicates, causing sub-optimal execution plans.
If you experiment with changing the order of predicates in the WHERE clause you will notice changes to the execution plan. The cost based optimizer (CBO) is rather insensitive to the ordering of where clauses, it assigns costs in order to determine what to do first and how to do things. The old unsupported rule based optimizer (RBO) was sensitive to the ordering, but not so the CBO.
So what that means here that the order in the where clause has nothing to do with how fast the query will execute. The cost-based optimizer ignores the order of the predicates and parses the query as a whole.

However, there is some evidence that this is not complete true, as evidenced by the order_predicates SQL hint. If the optimizer gets confused or cannot find an appropriate index that matches the WHERE clause, the optimizer will read every row in the table. Hence large-table full-table scans often indicate a missing index or a sub-optimal choice of optimizer goal.

1 comments:

  1. Shahid, thanks so much for posting this. Really helped me understand the concept. Another good one:

    Cost based optimization

    ReplyDelete