Re: How to: compound key w/o duplicating data

IMHO, you should either create your "special" columns, or split the originals into parts. Surely, one of the tasks that one undertakes _before_ normalisation is to ensure 1 attribute = 1 fact. If the first 3 digits of zip code has a meaning in itself, it should be in a column by itself.

Leave the other way to IBM mainframe types.

-- 
Andrew - Wizzard

barnetaj_at_bp.com

Roger Loeb <rloeb_at_martech.com> wrote in article
<6f8n3u$1s8$1_at_news1.rmi.net>...


> I'm struggling with a design issue and could use some advice.

> 

> I have a VERY large table (300 million rows; greater than 70 megs raw)

 that


> I need to index on two compound keys.  (One of these is also used to

 cluster


> and partition the table.)  The compund keys are formed from data found in

> several columns, but only a piece of the column.  For example, one of the

> keys begins with the first three digits of Zipcode, which is a CHAR

 column


> that has 13 bytes in it.  The complete keys have several such elements;

 each


> totals about 35 bytes.

> 

> I can construct two special columns that contain just the necessary key

> data, then build an index on each of those columns.  However, since the

> entire row is only about 200 bytes, this results in a 35% increase in

> required storage space just for the rows.  Of course, all that data will

 be


> duplicated in the indices.

> 

> Is there a way to construct a compound index on parts of a column, e.g.,

> using one of the string functions to extract the relevant pieces?  I've

> never seen this done, and can't locate an example of it.  Is there a

 smarter


> way to do this?

> 

> Thanks,

> 

> Rog

> 

> --

> roger@_delete_this_to_reply_.martech.com

> 

> 

>