All Forums UDA
rluebke 65 posts Joined 11/05
22 Sep 2008
Table Design

I have a couple of tables that are very wide. I am thinking about breaking them up into a star-schema-ish configuration (ETL, grouping, etc). For some of our legacy apps, I would need a view that re-creates the very wide table. My question is, would a multi-table join on primary indexes be as fast as the single, wide table? The data is these tables is very compressible (lots of Y/N indicators).R

marcmc 112 posts Joined 12/05
30 Sep 2008

There is obviously more processing involved when running a multi-table join even using PIs but this does not necesarily mean it has to be slower.Investigate:1) Can you balance the data across the amps on new tables so as to use the maximum level of parralelism.2) Are there columns you can drop thus saving space and capacity.3) are there any unused NUSI indices you can drop.3) Are there any columns that are not used on all records eg. TblAWideRow# ColA ColBRow1 NULL value (8bytes)Row2 value NULL (8bytes)vs TblAShort Row# ColARow1 value (4bytes) TblBShortRow# ColBRow2 value (4bytes)You've just cut the table size in half by splitting the tables, if there is a pattern in the data.The trade off between space, splitting the table, future capacity, affect of any further scans on the wide table from other apps/ETL process.Hope this helps and I'm sure your reports would run better off a smaller table.

rluebke 65 posts Joined 11/05
02 Oct 2008

Thanks. This has been one of those grey areas that I think I will just have to create both ways and see which way works out better. I appreciate you input.

You must sign in to leave a comment.