Hi Sean

Are you referring to the StockLevel_PK indexes? If so I have tried
reversing the order of these (makes sense) however performance has not
changed.

......
CONSTRAINT Stocklevel_Pk PRIMARY KEY(RegionRef, ItemRef)

I also have individual indexes on both RegionRef and ItemRef however
these don't seem to be used (looking at the plan).


Rob


Leyne, Sean wrote:

> Robert,
>
> > I need this as the ItemRef and regionref form a composite primary key
> > into StockLevel. The following plan is created....
> >
> > PLAN JOIN (CLIENTSTOCK INDEX
> >
> (CLIENTSTOCK_PSLIPLINE,CLIENTSTOCK_PSLIPREF,CLIENTSTOCK_CONGNLINE,CLIENT
> ST
> > OCK_TRANSREF,CLIENTSTOCK_CONSIGNREF),STOCKLEVEL
> > INDEX (STOCKLEVEL_PK),ITEMNAME INDEX (ITEMNAME_ITEMREF),CLIENTORDERS
> > INDEX (CLIENTORDERS_CLORDERREF),CLIENT INDEX (CLIENT_ENTITYREF))
> >
> > StockLevel_PK is ItemRef, RegionRef
>
> Can you change the order of the columns?
>
> If your example is indicated of a general way you define your SELECT
> (i.e. limiting by RegionRef) then re-ordering the columns in the index
> will make a big difference.
>
> If you can't, then I would create a new index with the columns
> re-ordered.
>
>
> Sean

[Non-text portions of this message have been removed]