Considerations for a very large table with unique indexes

I am in the process of redesigning an existing database, in which I must create a single table with 5 fields, 3 of which must be individually unique. There are currently just under 1 million rows, and the table will grow by as much as 200,000 rows per month. Each new entry must have each of the three fields evaluated for uniqueness prior to or as part of the insertion into the table. The three fields are currently varchar's 2 each 8 characters long and 1 each 12 characters long. I could convert them to a numeric (binary, or other appropriate) type if this would enhance indexing efficiency. There will be significant query activity looking up records. In general, the values within these three fields will be progressing (only less significant parts of the field will be changing), but at any time a 'wild' record may be entered out of sequence into the table. Because it is crucial that no duplicates of any of the three fields are entered later in time, these indexes must be maintained for all records forever....sigh.

What are my options within Oracle 8 to make lookups and maintenance as reasonable as possible? Can the table be somehow effectively broken into sub-tables, or is it better to leave it one huge table?

I'm not familiar with design of such large tables. I would appreciate any wisdom available from this forum.

Thanks,

Stan Zieg Received on Tue Oct 05 1999 - 19:08:46 CDT