fbpx

When the degree to which a given set of statistics is a good representation of the current table demographics is under scrutiny, I often experience that people rely solely on the collection date.

Some tables will not have Teradata stale statistics even if you never refresh them. Just think about lookup tables, which are quite static. Various degrees of changes can occur for other tables, with some fields having ever-expanding value ranges and others a constant share of only a few codes. How can you get a better picture of statistics and find out if they are stale?  There are several possibilities. Maybe the easiest could be to just run a simple query on the table and using a WHERE CONDITION on the column, which should be tested.

SELECT * FROM TABLE WHERE TEST_COLUMN = ‘X’;

Compare the number of estimated records (you can see this number in the Teradata Administrator) with the real number returned, and you know if the statistics are stale.

Note that you cannot expect the optimizer to be right down to the individual row on any occasion. Still, the divergence should not be larger than a small one-digit percentage,  depending on the overall table size.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • Hi Alan,

    One example comes to my mind:

    In case the Teradata system configuration changes (for example, by adding nodes) and rows of a PPI Table are distributed to new partitions.

    This happens for example if you are using the HASHING functions of Teradata in the PARTITIONING definition of your table DDL:

    HASHAMP(HASHBUCKET(HASHROW(column)))

    As a bigger system has more AMPS, rows are distributed differently to the partitions and the statistics become stale and have to be recollected.

    This is an example, where the table content stayed the same but statistics became stale.

  • I was once advised to recollect statistics on all the static ‘lookup’ tables held in a database, on a monthly basis, ‘just in case’ the statistics needed to be refreshed…needless to say, I never saw a reason to do this as it never had any impact. Would there ever be a case where statistics become ‘stale’ and therefore ignored by the optimizer in such circumstances?

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >