Collect Statistics in Teradata – Maintenance
1. Maintenance Milestones before you decide
Having defined, collected, and evaluated all pertinent statistics, it is now time to devise maintenance procedures.
Part of the Teradata Statistics and Cardinality Estimation guide.
What are the velocity and fluctuation of your Teradata data warehouse entities, and which time unit is most pertinent for their measurement?
Your data is in a state of either gradual progression or sudden and unforeseeable upheavals, akin to earthquakes without warning.
How frequently are you allocated time slots for statistical maintenance? How consistent and reliable are these slots, and how many topics require discussion? What level of priority does this task hold in comparison to others?
The answers to these inquiries determine your maintenance task’s complexity level.
Remember that any of the following approaches are not due to a collect statement’s nature but your environmental constraints.
2. Aging and Recollection
When should statistics be updated, as they can become outdated?
Updating Teradata table statistics is necessary either due to regular quantitative changes in table content or significant structural modifications. The former occurs during typical business operations, whereas the latter involves business-driven table reconstructions, such as adding or removing table attributes or changes in data leading to new values. Physical data model changes may also be required, such as repartitioning or index redefinition.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookThese are the maintenance techniques we have either personally employed or come across:
- Ad-hoc recollection of the entire collect combination set every couple of days or when someone “feels the need”.
- The complete recollection of the whole collect combination set every n days or weeks.
- A partial recollection of the whole collection combination during a calendar time window to continue on the next calendar time window until one round is done.
- Triggered partial recollection based on the collection date.
- Triggered partial recollection based on the relative table size change.
We advise against creating a rigid timetable for data refreshes since statistics become outdated not solely due to the passage of time. Rather, modifications such as alterations in table size, insertion or deletion of data layers that contain new values for specific fields, or recreation of tables caused by physical data model changes must prompt immediate action.
The triggered relative size method is a (semi-)automated technique that distributes recall over time, using smaller time windows, of which there are typically more. When executed automatically, it achieves the evenest distribution possible.
What criteria trigger this?
It is time to reflect on instances of a significant disparity between estimated query subsets and step figures and the factual outcome.
3. A Collection statement register
Prefer supported dictionary views and statistics automation for a general inventory. The following historical register illustrates a small manual inventory, not a complete modern catalog.
CREATE MULTISET TABLE P_XYZ_A.DM_STATS_REGISTER
(
DatabaseName VARCHAR(128) NOT NULL,
TableName VARCHAR(128) NOT NULL,
CollectCombination VARCHAR(1000) NOT NULL
) PRIMARY INDEX (DatabaseName, TableName);
Populate the database name as well as the table name and combination; the following historical rows show only the latter two fields.
T1111_ENTITY1 L_ID
T1111_ENTITY1 PARTITION,L_ID
T1111_ENTITY1 L_ID,C_DT
T1111_ENTITY1 PARTITION
T1111_ENTITY1 C_DT
4. A triggered partial recollection specification:
By examining the content of the register table, one can create views that display statistics that are either outdated or absent. Additionally, collection statement templates can be generated for targeted recollection.
Deriving the current statistics collection state from the dbc tables can be challenging.
After obtaining the dbc input, it is necessary to determine the timing and content of the collection or retrieval process.
To facilitate recollection, it is necessary to utilize the following procedure: distribute the required information evenly over time.
Record a table’s dimensions and the corresponding date in a distinct registry as a component of the daily operations.
Refer to the dbc statistics to determine the most recent data collection date for any statistic in the table.
Compare the table size on the latest collection date to its current size.
Treat a 10% table-size change as a historical scheduling heuristic, not a correctness threshold. Stable row counts can conceal major distribution changes; use predicate estimates, actual counts and workload regressions as additional triggers.
The essential elements of this specification for recollection are:
- Only a part of the entire set of tables is ready for recollection at any time.
- A table reappears for recollection until it is done. As the specification will remind you, you do not have to remember what you did.
- A size-based rule can identify growth, but it can miss distribution changes in stable-size tables. Review those tables independently.
However, there is a downside.
Selective and spontaneous recollection may result in outdated statistics going unnoticed, as the most recent recollection becomes the primary reference point.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book