Lisa Spielman wrote:
>
> 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.
This sounds so mainframe-ish. Adding "intelligence" to a column by use
og "magic" bit shifting is a recipe for disaster. You would be better
to revisit the data model and rethink what needs to be done.
>
> 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.
A table of that many columns could be considered large. Yes, there
could be a pereformance degrade - this depends on your block size. I
would bet you have block chaining and possibly row migration going on.
If so, you are in I/O hell. I would look again at the data model and
redesign.
>
> Thanks for any help,
>
> Lisa
See my comments above...
--
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.
Received on Fri Oct 26 2001 - 12:25:42 CDT
