Re: question ???

Norman,

Don't be in too much of a hurry to change your opinion - the critical paragraph is the one quoted by Dave:

"As a rule of thumb, if a column value could conceivably change, do not use it as a primary key. Almost always a key will be data that has absolutely no purpose or meaning outside of uniquely identifying a row in a table. Numbers are a typical choice."

The first sentence is correct, especially in its use of the phrase 'as a rule of thumb', although the word 'conceivably' is a little strong - it is, after all, conceivable that a law could be passed making it illegal to use any multiple of the number 13 as a form of identifier for a living person.

The second sentence is statistically misleading. Assume I have 100 tables of 10 rows where I use a meaningless key, and one table of 10,000,000 rows where I use a key which has some meaning

Are my keys 'almost always meaningless' because more than 99% of the tables have meaningless keys, or are they 'almost always meaningful' because across my data set only one row in 1,000 has a meaningless key ?

From a purely pragmatic point of view, though: if you are building very large data sets, and hope to use partitioning, and partition pruning in particular, for the performance benefits, then you are likely to run into a problem if you use a meaningless key on the partitioned tables.

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

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

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Norman Dunbar wrote in message ...


>Dave,

>

>belter of an explanation - I sit corrected.