Tricky stats collection

Database
Enthusiast

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

3 REPLIES
Senior Apprentice

Re: Tricky stats collection

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?

Enthusiast

Re: Tricky stats collection

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.

Senior Apprentice

Re: Tricky stats collection

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