Composite Primary key or 1 primary key column with all values

Composite Primary key or 1 primary key column with all values

Post by Sybrand Bakke » Sun, 11 Aug 2002 23:13:05





Quote:>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.

If you take the second route you will soon regret that, and it will
create a messy application, as you will soon need to 'parse'
one_column *everywhere*. So : speed is not a consideration here,
maintainability is. Choose for the second route and prepare to travel
to hell.
And storing dates as strings is definitely a *BAD* idea.
A few tables with the same primary key also looks like you have severe
problems in your datamodel.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

 
 
 

Composite Primary key or 1 primary key column with all values

Post by kope » Mon, 12 Aug 2002 02:49:55


I can not understand why in the world you want to create 6 columns in
a table when one would suffice. A simple select.. from.. where col1=?
will turn into select..from.. where col1=?, col2=? etc., etc... The
SQL will take longer to parse. How much better speed-wise a single
column design is over the 6-columns one, I can not say. The 6-column
one will not out-perform the single-column one, that I can say.

- K.M.


> 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.


 
 
 

Composite Primary key or 1 primary key column with all values

Post by Sybrand Bakke » Mon, 12 Aug 2002 03:26:07



Quote:>I can not understand why in the world you want to create 6 columns in
>a table when one would suffice. A simple select.. from.. where col1=?
>will turn into select..from.. where col1=?, col2=? etc., etc... The
>SQL will take longer to parse. How much better speed-wise a single
>column design is over the 6-columns one, I can not say. The 6-column
>one will not out-perform the single-column one, that I can say.

That single column is a column made up several columns. If he uses
your advice he will be forced to use substr's all over the place to
get that information out.
Strictly following your advice any table can be made up of one single
column, because multiple columns will take longer to parse.
This observation is simply completely incorrect and your advice
unprofessional.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

 
 
 

Composite Primary key or 1 primary key column with all values

Post by Marc » Mon, 12 Aug 2002 08:24:26


I forgot to mention that I if I went with the 1 column approach, then I
would have all 7 columns.  1 for the combined primary key and the other 6
columns separated out.  The six will be kept so I can search on 1 particular
value if needed and display the values would be easier.  The 1 primary key
will be only used to lookup the particular row.  I would probably have a
trigger to perform the concatenation.  I will never use substring on the
primary key column.  BTW, this is a dss system, no transactions.  Speed is
the issue.


Quote:> 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.

 
 
 

Composite Primary key or 1 primary key column with all values

Post by Marc » Mon, 12 Aug 2002 08:24:40


I forgot to mention that I if I went with the 1 column approach, then I
would have all 7 columns.  1 for the combined primary key and the other 6
columns separated out.  The six will be kept so I can search on 1 particular
value if needed and display the values would be easier.  The 1 primary key
will be only used to lookup the particular row.  I would probably have a
trigger to perform the concatenation.  I will never use substring on the
primary key column.  BTW, this is a dss system, no transactions.  Speed is
the issue.




> >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.

> If you take the second route you will soon regret that, and it will
> create a messy application, as you will soon need to 'parse'
> one_column *everywhere*. So : speed is not a consideration here,
> maintainability is. Choose for the second route and prepare to travel
> to hell.
> And storing dates as strings is definitely a *BAD* idea.
> A few tables with the same primary key also looks like you have severe
> problems in your datamodel.

> Regards

> Sybrand Bakker, Senior Oracle DBA

> To reply remove -verwijderdit from my e-mail address

 
 
 

Composite Primary key or 1 primary key column with all values

Post by Marc » Mon, 12 Aug 2002 08:24:57


I forgot to mention that I if I went with the 1 column approach, then I
would have all 7 columns.  1 for the combined primary key and the other 6
columns separated out.  The six will be kept so I can search on 1 particular
value if needed and display the values would be easier.  The 1 primary key
will be only used to lookup the particular row.  I would probably have a
trigger to perform the concatenation.  I will never use substring on the
primary key column.  BTW, this is a dss system, no transactions.  Speed is
the issue.



> >I can not understand why in the world you want to create 6 columns in
> >a table when one would suffice. A simple select.. from.. where col1=?
> >will turn into select..from.. where col1=?, col2=? etc., etc... The
> >SQL will take longer to parse. How much better speed-wise a single
> >column design is over the 6-columns one, I can not say. The 6-column
> >one will not out-perform the single-column one, that I can say.

> That single column is a column made up several columns. If he uses
> your advice he will be forced to use substr's all over the place to
> get that information out.
> Strictly following your advice any table can be made up of one single
> column, because multiple columns will take longer to parse.
> This observation is simply completely incorrect and your advice
> unprofessional.

> Regards

> Sybrand Bakker, Senior Oracle DBA

> To reply remove -verwijderdit from my e-mail address

 
 
 

Composite Primary key or 1 primary key column with all values

Post by dudeste » Mon, 12 Aug 2002 09:27:57


and you had to post this three times...why ?

> I forgot to mention that I if I went with the 1 column approach, then I
> would have all 7 columns.  1 for the combined primary key and the other 6
> columns separated out.  The six will be kept so I can search on 1 particular
> value if needed and display the values would be easier.  The 1 primary key
> will be only used to lookup the particular row.  I would probably have a
> trigger to perform the concatenation.  I will never use substring on the
> primary key column.  BTW, this is a dss system, no transactions.  Speed is
> the issue.



>>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.

 
 
 

Composite Primary key or 1 primary key column with all values

Post by Carsten Alexande » Tue, 13 Aug 2002 01:24:10


Hi Marc,


Quote:> 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"

What would about using a single counter as primary key and build a unique
constraint for the "keyfields"? This makes the selects much more easier (Insted
of ... WHERE col1 = value1 and col = value2, ... now where key = value). So you
use a "simple" reference to other tables and get much more performance. How you
think about that?

--
Regards,
Carsten

 
 
 

Composite Primary key or 1 primary key column with all values

Post by Richard Foot » Tue, 13 Aug 2002 08:48:43


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


> 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.

  Richard.Foote.vcf
< 1K Download
 
 
 

Composite Primary key or 1 primary key column with all values

Post by Marc » Tue, 13 Aug 2002 10:22:41


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

> 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


> > 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.