All Forums Database
spirosmike 6 posts Joined 08/15
11 Aug 2015
Tricky stats collection

Hi All,
 
I have a table that has 203 columns.The first three together are the PI for the table (say A,B,C).The other 200 columns are in the following pattern
D001,
DOO1_TXT,
D002,
D002_TXT,
.
.
.
.
.
D100,
D100_TXT
 
We use the following table in a SQL that runs very frequently.This sql uses any of the pair of  "D" columns (say D010,D010_TEXT OR D035,D035_TEXT).In this case collecting stats on this pair helps the SQl a lot by retuning it in just few seconds but i dont know if collecting stats on this 100 pairs is allowed or is a good idea or not and even if it is I DONT WANT A TABLE WITH STATS ON 100 COLUMN PAIRS.
 
Can someone please suggest on how to deal with cases like this. i know this info might not be enough ,i would love  to answer more questions to clarify the issue.
 
Thanks
Mike

dnoeth 4628 posts Joined 11/04
12 Aug 2015

Hi Mike,
of course you can collect 100 stats on a single table if you actually need them, but afaik there's a limit of 32 multicolumn stats on a single table.
Do you really need both columns, looks like there's a relation between them?
 
These column names look denormalized, did you ever think about normalizing the table?

Dieter

spirosmike 6 posts Joined 08/15
12 Aug 2015

Dieter,
 
These are not part of the PI and hence will count as the multicolumn stats ( if i am not wrong).
The the column D100 is a Number to represent the BRANCH and D100_TXT is the description of the branch on which we filter the results.hence there is a soft relation between them but nothing in terms of data are they related.
 
I havent thought about normalizing this ,as this the project is too deep into production that would be the last option that i can keep to solve this.
 

dnoeth 4628 posts Joined 11/04
12 Aug 2015

As you're limited to 32 multicolumn stats you might try if single column stats might work, too.

Dieter

You must sign in to leave a comment.