Re: Help! Very bad SQL bug in 7.2.3.0.0

Tom Fielden wrote:
>
> Recipe for bad results:
>
> Join several tables sequentially like so: a-b-c-d-e
> Put a simple on 'e'.
> Observe that the query runs fast.
> Now put a simple condition on 'a'
> observe that the query is 100 times slower! Sheesh!
>
> This is so incredibly bad that Microsoft SQL server looks like a
> viable alternative.
>
> Please don't let me get sucked into the 'Gates' of hell! What is
> the work-around?
>
> Also, this seems like an extremely common type of query to run. In
> fact, a developer down the hall has an almost identical problem with
> the same effect.
>
> If you have any ideas, I would greatly appreciate hearing from you.
>
> Thanks in advance,
>
> Tom Fielden
> Mentor Graphics Corp.
Tom,

You made no mention what version of Oracle your using. Oracle V6? Oracle7 7.0, 7.1, 7.2, 7.3? Makes a big difference. Or whether your using the Rules-Based or Cost-Based optimizer. Or if you've tried to use Explain Plan to review the execution plan of your query.

Some general advice.

The Rules-Based Optimizer has some quirks about table ordering in the From clause when attempting to break a tie between two or more equally weighted driving tables. Try relocating tables in your From clause, largest to smallest and rerun your test.

The Cost-Based Optimizer has numerous hints available to control the results of a query. And table/index statistics, including detailed histograms in 7.3, can be gathered on the entire table or just a sample.

There's a lot of info on this in the Oracle Application Developer's Guide.

-- 
Rich Woods
Technical Field Support Specialist, Oracle Corporation, USA
The above statements and opinions are my own and do not
necessarily represent those of Oracle Corporation.