Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> How to: compound key w/o duplicating data
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.comReceived on Tue Mar 24 1998 - 00:00:00 CST