Re: Order of key fields

Lets say you have 10,000 items, and these items are spread evenly across:
- 4 colors
- 12 sizes
- 40 package possibilities
- and 500 patterns

The most efficient search is to do the most restrictive choice first.

If you choose a specific pattern first then you have immediately narrowed your search to 10,000/500, or only 20 items left to choose from.

If you selected color first, you would only narrow your choices to 10,000/4, or 2,500 left to narrow down.

Is that the question you are asking?





On 9/1/2011 4:59 PM, Gary Thompson wrote:

George wrote:

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)?


My understanding is the most frequently uses fields should appear before
any others in the key list.

The idea is that row selection, think SQL WHERE clause, is a main factor
in access path selection;
therefore those fields more frequently used as WHERE criteria should
appear before others.

I do think there is a general recommendation for fewer access paths that
are "wider" as
opposed to many "narrow" access paths.