Re: optimum datatype for primary key column O9i

From: mcstock <mcstock_at_enquery.com>
Date: Thu, 30 Oct 2003 09:54:42 -0500
Message-ID: <6oCdndEE7f7KuzyiRVn-gQ_at_comcast.com>


jug, no one speaks for 'the group', though some do like to speak louder.

datatype is not an issue in the strictest sense

using updatable values in primary keys, or values that have a meaning beyond identification, are issues

creating an index on a DNAME column in a departments table (a real table, not Scott's 4-row table) is not an issue if the index is being created to support searches on DNAME or to support uniqueness

however, if i use DNAME as a PK (since my analysis tells me it has to be unique) instead of a system-assigned, or even a user-assigned number i will have the following issues:

[_] larger values in the index
[_] potential updates as department names change
[_] cascading updates to other tables with FK's referencing the updated PK
[_] potential table structure revisions as business rules change (if
department names are no longer unique, it's a lot easier to drop a unique constraint than to restructure the table with a new PK -- and new FK's) [_] etc., etc.

and even if i use a short user-assigned mnemonic (which i sometimes will do), we run the risk of PK updates (to be religiously avoided) or of imbedding intelligence in the PK. for example, why is one department called S023 and another called M023? did we miss an attribute that should group the departments by business unit or such, i.e., SALES and MANUFACTURING (or is that M for MARKETING?)

some of these issues are important data modeling issues, others are implementation and tuning issues

where to look in the manuals? read up in the Concepts, DBA, and Tuning manuals. chapter and verse? interesting analogy. sometimes issues have to be dealt with based on breadth and depth of experience and familiarity with a number of concepts, principals, and scenarios -- when you've got serious concerns you need to research relevant topics (in this case indexes, datatypes, optimizer, constraints) and do some of your own testing (see Jonathan Lewis' website www.jcomp.demon.co.uk for some excellent info on research, testing, and logical analysis of Oracle issues). then you'll also be able to benefit more from other people's observations, and filter our what is relevant and what is not.

  • mcs

"Jug Ray" <jray_76_at_yahoo.com> wrote in message news:fd754911.0310300430.43b5b018_at_posting.google.com...
> Frank <fvanbortel_at_netscape.net> wrote in message
 news:<bnpdbo$ndq$1_at_news1.tilbu1.nb.home.nl>...
> > Because it's a non-issue. All the answers are there (OK, OK - some
> > coding was actually left to the OP).
> >
> > Give a man something to eat, and he will be hungry tomorrow,
> > teach him how to fidh, and he'll never be hungry again.
> > Or words the like.
> >
> > Generally, we're not here to hold hands...
>
> is the datatype an issue in indexes or not
> i am looking also for an answer to that one
> for a long time, tell me where it is in the manual
> chapter, verses, book of ... as you seem to speak
> for the group at large
>
> what are you trying to say in "Give a man..."
> I know its all there but can make sense of what you say
>
> Jug
Received on Thu Oct 30 2003 - 15:54:42 CET

Original text of this message