Re: Urgent help need for SQL statement tuning

In article <cellis-ya02408000R1906981040360001_at_news.clubi.ie>, Chrysalis wrote:
>
> In article <6mbm7d$sp8$1_at_client3.news.psi.net>, johnj_at_dbbasics.com wrote:

> > 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.

If residual conditions are specified for only one table in the join, it becomes the driving table, and the join is satisfied by indexes. With residual conditions on both tables, you need to access inner-table data blocks for each match. The solution is to create indexes that cover the residual conditions on both tables, thus no need to access data blocks.

> 3) You should probably have an index on order(customer_id,rec_date) rather
> than separate indexes on the two columns.

I would create the following indexes:

    customer: (customer_id, state_cd, Country_cd)     order: (rec_date, customer_id)

This should result in an access path where the index on "order" is scanned to select those customers placing orders in the given month, and that set joined via the index on "customer" with the residual conditions coveredby the index. You may need a hint to force this access path.

This is based on the assumptions that (1) customers will have many orders, (2) customers will not have many orders in a given month, and (3) a relatively small percentage (under 100% is small here) of customers will have orders in a given month. These assumptions suggest that date is a more selective access path.

-kdg Received on Tue Jun 23 1998 - 11:05:11 CDT