Performance -- Order in a Where clause 2

[mdl2]

Oracle 9i

Should your most restrictive clause be the last clause in your where statement or does it matter?

I thought oracle processed from the last clause back
but my colleague thinks the most restrictive should be the first. I have searched google but have not found an answer.

Any Thoughts?

 

[Thargy]

mdl2,

it's sort of irrelevant. Oracle has an optimiser which takes care of that sort of thing for you. When you run a query, the optimiser assesses its "cost" and runs what it considers to be the most economical way of obtaining the desired information.

You might query in a certain way, but unbeknownst to you, the optimiser might well do something different.

A classic example is where there's an index on a table and the Cost Based Optimiser (CBO) ignores it and does a full table scan. That's because it has recognised that the table is small, and a FTS will be quicker than accessing records via indexes.

Make sure your stats are up to date, and then let the optimiser do its thing. It is possible to give optimiser hints in sql, but it doesn't sound as though you need to do this yet.

Regards

Tharg

Grinding away at things Oracular

 

[Beilstwh]

Oracle has two types of Optimizer. The first is Rule based. It was used in Oracle 7 and below and is available in oracle 8 and 9. In Rule Based, the placement of a clause in the where clause was critical. It would go from the last line to the first (assuming no parens). IN all current versions, the default is cost based, which is nicely explained by Tharg.

Bill
Oracle DBA/Developer
New York State, USA

 

[karluk]

This appears to be a rather complicated subject, but I found some references that clearly state that the order of predicates is, indeed, important even when using cost based optimization. Take a look at these excerpts from Metalink note 276877.1

Most of the information here applies to any query in any version using Cost-Based Optimization.
.
.
.
Some of the things that influence the order of application of the predicates are:
- Their position in the WHERE clause.
.
.
.
A few things to try before talking to support are:
If the execution plan is not optimal then review Note 199083.1.
If the execution plan is optimal but the order of predicates is not then:

- Rewrite the query and place the predicates in the order they should be applied.

As far as your question about the order of the predicates, I've always believed it should be most restrictive last, so I would be interested in where your colleague got his information. The following link seems to agree with the "most restrictive last" approach.

http://dba.ipbhost.com/index.php?s=...pid=22874&mode=threaded&show=&st=&#entry22874