a very large table?

I have a table with 116 integer columns that I need to increase to 228. The table has anywhere from 1 million - 5 million rows, depending on the installation. The table is always accessed by the index which is 1 integer. The index is a unique value although it isn't defined as unique in the database.

I don't necessarily need to increase the number of columns by 112, I can be smarter about what I store by using generic columns rather than data specific columns, and use a 'mask' integer to know what I am storing where. (There are a possible 32 sets of data, each set is 7 columns). If the greatest percentage of these rows contain 16 or less sets of non-zero data, then for those that do need to have stored more than 16, I can use 2 rows.

note: this is a Pro-C application, transaction-based system. This table is accessed and updated in every transaction; not every column has to be updated, just those involved in the transaction If I go the route of a 'where used' mask, I would want to select the mask in a query so I can use those fields to build another query to select the columns that I need. Is there an efficient way of doing this? Can row id be used for the second select?

If I do go ahead and increase the size of my table to 228 integers, is this considered very large? Will this degrade performance? Is it better to split the row into 2 rows, each having 116 integer columns ( 4 integers are header type info), or is it worse to have to use a cursor for the select.

Thanks for any help,

      Lisa Received on Fri Oct 26 2001 - 10:22:03 CDT