 |
 |
Archives of the TeradataForum
Message Posted: Wed, 18 May 2005 @ 20:19:58 GMT
| Subj: | | Re: Questions about the advisability of manual modification of interval statistics |
| |
| From: | | Dieter Noeth |
Hi Frank,
| | "Is this a good way of doing things?" | |
Why not, if it fits your needs and you do it right...
| | "What are the caveats and dangers we should be aware of?" | |
Simply don't supply the optimizer with wrong stats...
| | "Is there a way to 'manually' update the interval statistics using some sort of BATCH method?" | |
The Stats Wizards (or the stats tools within Teradata Manager) simply submits a COLLECT STATS with an undocumented option. If you export some
stats using the System Emulation Tool and check the *.col file, you'll find something like that:
COLLECT STATISTICS "TPCD10G"."LINEITEM" COLUMN "L_SHIPDATE"
VALUES
(
/* TimeStamp 04/03/23 17:21:48 */
/* Version 2 */ /* Sampled YES */ /* SampleSize 2 */
/* Col 0,(L_SHIPDATE): 17, DA, 4, 4, 0, YY/MM/DD */
/* Interval[0] */
/* NumNulls */ 0,
/* NumIntervals */ 100,
/* Table Summary */
/* Min. Value */ DATE '1992-01-02',
/* Mode Value */ DATE '1996-03-29',
/* Mode Freq. */ 28683,
/* Total Values */ 126227,
/* Total Rows */ 59951396,
/* Interval[1] */
/* Max. Value */ DATE '1992-03-17',
/* Mode Value */ DATE '1992-03-17',
/* Mode Freq. */ 16490,
/* # Other Val. */ 3748,
/* # Other Rows */ 582065,
/* Interval[2] */ DATE '1992-04-20', DATE '1992-04-13', 23936, 1649, 637882,
/* Interval[3] */ DATE '1992-05-17', DATE '1992-05-07', 27184, 1299, 633435,
...
Undocumented within the manuals, but lots of nice comments :-)
| | "Would it be possible to calculate our own fake 'complete' stats for these dates and put them in?" | |
You just have to create that VALUES(...) automatically, should be easy with Excel ;-)
Maybe somebody already wrote a small programm for that purpose, let's see...
Dieter
| |
