Collect Statistics on Table

Database
Enthusiast

Collect Statistics on Table

What is the Usefulness of Collect Statistics Statement on Table Columns or Index?
In what senario it should be used?
1 REPLY
Enthusiast

Re: Collect Statistics on Table

Optimizer relies heavily on statistics to generate a query plan. You should collect stats on NUSI, Partition columns, Any columns used in Joins.

Once you collect stats on a column, Teradata knows how many unique values are there and how they are distributed. It creates a histogram internally so that it knows how many records are present in each range. It also understands is index is high or low selective. All these help in coming up with better plan.

Note that collecting stats does full table scan. If table is huge, collecting statistics may take a while. You can also do sample stats, it is better than having no stats. In explains, you will see "no confidence" if stats are not present.

Hope this helps.