It is often assumed that the order of the predicates in the WHERE clause of an Oracle SELECT statement does not matter or that it does not affect the query performance. This is not entirely true.
The Oracle cost-based optimizer may re-order the predicates for better performance. Since it was first introduced in Oracle 7, its algorithms have improved significantly but sometimes it still makes the wrong decision on evaluating the cost of each predicate.
Without system statistics enabled, you may sometimes notice differences in query performance simply by moving the order of predicates in the WHERE clause around.
The optimizer uses next steps to evaluate the order of the 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
By enabling system statistics the optimizer gets a more accurate picture of how the system really performs, and therefore it can produce a better match between estimated and actual query execution time.
For instance, gathering statistics for table LR_INSTANCE can be done by running the next command in sqlplus:
execute dbms_stats.gather_table_stats(
user,'LR_INSTANCE',cascade=>true);
A good way to get an idea of your query’s performance, and for tuning it, is by using Oracle’s dbms_xplan package. This generates a cost report that contains some more details than autotrace.
EXPLAIN PLAN FOR
select *
from lr_instance a,
lr_instance_meter b,
meter c
where a.lr_instance_id = b.lr_instance_id
and b.meter_id = c.meter_id;
SET LINESIZE 130;
SET PAGESIZE 0;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 1395189457
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12719 | 3403K| 41 (5)| 00:00:01 |
|* 1 | HASH JOIN | | 12719 | 3403K| 41 (5)| 00:00:01 |
| 2 | TABLE ACCESS FULL | METER | 1496 | 137K| 9 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 12719 | 2235K| 31 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL| LR_INSTANCE | 433 | 59754 | 5 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| LR_INSTANCE_METER | 12719 | 521K| 26 (4)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."METER_ID"="C"."METER_ID")
3 - access("A"."LR_INSTANCE_ID"="B"."LR_INSTANCE_ID")