Table Design

UDA
Enthusiast

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
2 REPLIES
Enthusiast

Re: Table Design

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.

TblAWide

Row# ColA ColB

Row1 NULL value (8bytes)

Row2 value NULL (8bytes)

vs

TblAShort

Row# ColA

Row1 value (4bytes)

TblBShort

Row# ColB

Row2 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.
Enthusiast

Re: Table Design

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.