Re: Urgent help need for SQL statement tuning

In article <6mbm7d$sp8$1_at_client3.news.psi.net>, johnj_at_dbbasics.com wrote:

> I have the following problem: I have a Customer table and an Order
table, both with
> customer_id to join on. I write the following statement.
>
> select a.customer_id
> from customer a, order b
> where a.customer_id=b.customer_id and
> b.rec_date between '01-JUN-98' and '31-JUN-98' and
> a.Country_cd = 1 and
> a.state_cd in ('NC','SC','VA','MD','FL')
>
> when the where clause contains references to Customer and Order it takes
for ever
> to run. If the where clause has only Customer or only Order references,
it comes
> back immediately. Both tables have about 250,000 records. I am missing
something.
> respond to johnj_at_dbbasics.com or news.
>
> John Jones
> DB Basics Inc.
> johnj_at_dbbasics.com

1. If customer(customer_id) is not declared as a primary key (or unique), make sure that it is.
2. Do you have an index on customet(stat_cd)? If so, the optimiser may be choosing to use this instead of / as well as customer.customer_id. This would be bad news because of the 5 implicit OR's, each one of which results in the creation (by the optimiser) of a separate access tree.
3. You should probably have an index on order(customer_id,rec_date) rather than separate indexes on the two columns.
4. If the worst comes to the worst, try using optimiser_mode RULE (either as a system or session parameter or as an in-line hint).

HTH --
Chrysalis Received on Fri Jun 19 1998 - 04:40:36 CDT