Re: BOM application

I'm just back from the US and catching up on the newsgroup - so I've just found the big '256 byte key' thread. So I see what you mean.

A couple of practical points:

1. If you have a synthetic key and something else constrained to be the 'real unique thing' then you have two indexes - which is a maintenance overhead. (Significant since the maintenance of an ordinary index is mostly expended in infrastructure and overheads, not in the raw data cost).
2. If you use 'the real unique thing' then data is cascaded down from parent to child automatically through the key, so some queries will be able to avoid joins that would be made necessary by synthetic keys.
3. Range partitioning (for partition elimination) requires any unique constraint to contain the partitioning columns. So either the real indexed thing or the synthetic index is global rather than local.

I have to say that the argument

    "The business will change what they perceive to     be the primary key, I will use a synthetic one"

sounds vaguely similar to the argument:

    "because airbags will deploy eventually, I will drive     with mine deployed"

There is no perfect implementation strategy for the real/synthetic divide - only a considered examination of costs and benefits.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Paul Brewer wrote in message
<3ddfe044$1_3_at_mk-nntp-1.news.uk.worldonline.com>...


>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

>news:arobtl$cj8$2$8300dec7_at_news.demon.co.uk...

>>

><snip>

>I tend to prefer meaningful keys to synthetic ones.

><snip>

>

>So I'm not in a minority of one, after all.

>

>Regards,

>Paul

>

>

>