RE: Design table with many columns

Certainly testing validation will be needed, including a single table, one table with all the likely filter predicate columns and three index oriented tables (probably these at 254 columns each with the index being the pk), four tables in a cluster, and four regular tables (but one of them with the most likely filter columns and room to grow.) I would double check the number of columns that drives automatic row pieces, as rare as this comes up I have to check whether 254 fit. The number and placement of “long” columns becomes interesting, as well as where to put any nullable columns together with the frequency pattern of null values if any are allowed, most especially if any nullable columns are used with a sparse index.  

If creation date/time is one of the columns, that seems a likely partitioning column that would likely be repeated for each member “table” of the long row.  

Please notice I did NOT comment on the likelihood the analysis resulting in 900+ attributes being needed in a transaction table is correct.  

Testing as indicated certainly should be done, and IF a single table is ordered optimally, even though Oracle has to do multiple row pieces, without specific advantages of your particular table Oracle that you can capitalize on, probably Oracle will do the best job for transaction purposes. There is a much higher probability that you can do better for reporting purposes if you know in advance that a much smaller set of columns will be reported on most of the time. For reporting, then, it might be best to again put those columns and filter columns in one “physical” table of the combined whole.  

That’s how I would look at the problem aside from being a skeptic for the need for that many columns in a single table.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lok P Sent: Thursday, July 04, 2024 3:40 PM
To: Oracle L
Subject: Design table with many columns  

Hello,

In one of the applications we are getting transactions in messages/events format and also in files and then they are getting parsed and stored into the relational database. The number of attributes/columns each transaction has is ~900+. Logically they are part of one single transaction and should be stored in one table as one single row. There will be ~500million such transactions each day coming into the system. And there will be approx ~10K peak write TPS and 5K read TPS in target state.

Initially when the system was designed the number of attributes per transaction was <100 but slowly the business wants to keep/persist other attributes too in the current system and the number of columns keep growing.

However, as worked with some database systems , we get few suggestions from DBA's to not have many columns in a single table. For example in oracle they say not to go beyond ~255 columns as then row chaining and row migration type of things are going to hunt us. Also we are afraid concurrent DMLS on the table may cause this as a contention point. So I wanted to understand , in such a situation what would be the best design approach we should use irrespective of databases? Or say, what is the maximum number of columns per table we should restrict? Should we break the single transaction into multiple tables like one main table and other addenda tables with the same primary key to join and fetch the results wherever necessary?  

Note- This system is planned to have a postgres database as a "source of truth" or OLTP store. And then data moves to snowflakes for the olap store.  

Regards

Lok

--
http://www.freelists.org/webpage/oracle-l