Re: Order of key fields

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Most definitely read the white paper Birgitta supplied a link to...

You'll probably find that you need them both ways...

If you ORDER BY lot, case, piece
then you'll probably want an index by that

If you doing JOINs or WHEREs using (lot, case, piece)
then you'll probably find an index over piece, case, lot provides the
best performance...

Charles

On Thu, Sep 1, 2011 at 4:50 PM, George Kinney <GKinney@xxxxxxxxxxxxxxxx> wrote:
> Here's the situation, I have a table that has a 12 field index. Some of
> those fields are far more variable than others, and at the moment they
> are specified in the DDS in the order they appear in the source data.
>
> What I'm wondering is if there is a signifigant performance advantage
> (or penalty) to ordering them according to their variability. In other
> words, given a situation where you have a lot#, case#, piece# with many
> pieces# per case, per lot, would a key order of (lot, case, piece)
> affect performance differently than (piece, case, lot)?
>
> Anybody know, or have any pointers to enlightening online resources?