we have T12 , and i do not have any stats defined on the below tables which i got as recommendations from blogs:
My Questions are related to your "Dropping MC Stats" Article & David Roth's Similar Article.
(a) You mentioned that the Data Content's Length matter, not the Column Length. Meaning, say Stats collected on (Col_1,Col_2) with Col_1 is Varchar(30) & Col_2 is Integer. So, What Matters is whether the Data in Col_1 exceeds 16 Byte or not, rather than the Column Length[Which is already more than 16 Bytes @ Varchar(30)].
In which case, ('ABCDEFHGIJKLMNOPQ',14) & ('ABCD',14) are Different.
(b) Stats(Col_1) and Stats(Col_1,Col_2) exists, with Col_1 nearly Unique[>= 95%]. David recommend Dropping Stats(Col_1,Col_2) based on Explain Capture on "Select Distinct(Col_1,Col_2) From Tablename", which is a Single-Table Operation.
As per you, if these 02 Columns are used in Joining Conditions or Aggregate Operations, these would be required for Unique Values Calculation, which in Independent of Truncation.
So, Is the Approach of Dropping Stats based on the David's Experiment alone [Single Table Experiment] Justified ? Cause, if both David and you are Correct, Dropping a MC Stats would require David's Approach PLUS digging around SQLs to verify that those Columns are not used in Joining or Aggregation Operations, which is an extremely difficult job.
(c) Assuming that Stats(Col_1,Col_2,Col_3) exists and all these 03 Columns are not used in Joining or Aggregate Operations, then the Dropping MC Stats' preferential order of approach would be:
(1) If first 16 Bytes same for (Col_1,Col_2,Col_3), Drop the Stats.
(2) If Unique(Col_1) >= say,95%, Drop Stats(Col_1,Col_2,Col_3) [Collect on Col_1 instead].
Meaning, first I check (1) and then (2).
Is there any query which can identify which defined MC crosses 16 byte convention and can be dropped :) at database or table level. Also, incase we get some MC which can be dropped , how we can analyze the benefits i.e CPU/IO etc?