Re: Composite Primary key or 1 primary key column with all values

I thought about a sequence but for speed I flattened out most of my tables and each one of the columns is needed in the child table for business reasons. Also, the 1 column primary key would really be a "symbology" name of the entire record. Personally I do not like the 1 column approach. The sequence would not work. I have other databases that needs info from my database and they would not have the sequence number, unless I have a lookup table for each id.

I am really looking for speed; if the developer has all 6 columns then use the symbolgy, else look up each column manually.

thanks
"Richard Foote" <Richard.Foote_at_oracle.com> wrote in message news:3D56F7DB.ADD59361_at_oracle.com...
> Hi Marc,
>
> My opinion (vote) for what it's worth.
>
> I would definitely go for option 1. To concatenate them would be a right
> royal pain when you want to select the date in col 3. If you plan to
> duplicate these values in separate fields and have the concatenated PK,
> then there are obvious overheads in maintaining the duplicated data.
>
> I would go for option 1 and consider a PK based on a sequenced number
> (and appropriate constraints for cols 1-6).
>
> Also, if you have several other tables with the same col1-6 PK, then
> your data model needs to be questioned. The reasons for not storing the
> data in the same table (whatever they might be) needs to be addressed.
>
> I totally agree with you that tuning for speed needs to be considered at
> this stage, I'm just not too sure your current data model is designed
> appropriately to support this.
>
> Good Luck
>
> Richard
>
> Marc wrote:
> >
> > Which is faster? 6 columns or 1 column with all keys. Speed is the
only
> > issue.
> > col1 varchar(3) = aaa
> > col2 varchar(3) =bbb
> > col3 date =12/31/01
> > col4 number(1) =1
> > col5 number(1) = 0
> > col6 number(1) =1
> >
> > OR
> >
> > one_column varchar2(20) = "aaabbb12312001101"
> >
> > I have a few tables with the same primary key. For perfomce only. does

> > anyone know? MultiMillion row table.
Received on Sun Aug 11 2002 - 20:22:41 CDT