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

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.