Re: Oracle query optimizer

In a word, yes. The rule-based optimizer is pretty good, but sometimes one way looks just as good as another. Then you have to apply what special knowledge you might have about the table to tune the query for optimal results. If you are using the cost-based optimizer, then you must run statistics on a regular basis.

For example, I had a table with 5 indexes. To the rule-based optimizer, index _I4 looked as good as _I3. But I knew different, and I forced it to use _I3. The query ran much faster.

The optimizer, in the absence of better information, will generally join tables last first (in the order specified in the FROM clause). But I've seen it depart from this rule of thumb when it could compute a unique index scan for one or more tables. It only does things in the predictable manner when the cost computation comes up the same either way.

I always run an Explain Plan and tune my queries by hand. It takes longer, but hey, that's why they pay me the big bucks! --
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.

Roger Tomas <tomasr_at_agcs.com> wrote in article <359AADF7.40ACDF81_at_agcs.com>...
> Am new to Oracle and am wondering how smart the query
> optimizer is. Will I have to care about table order, column
> order, etc.?
>
> I saw one post that mentioned joining the largest table last.
> Do I really have to worry about these kinds of issues?
>
> Roger Tomas
> AG Communication Systems
>
>
>
Received on Wed Jul 01 1998 - 16:04:46 CDT