0 - 22 of 22 tags for collect statistics

I have been experimenting with using MAXVALUELENGTH to make additional columns in multi-column stats significant. I have not been able to find anywhere a definition for what the maximum value is for this stats attribute.

Hi All,
 
I have a table that has 203 columns.The first three together are the PI for the table (say A,B,C).The other 200 columns are in the following pattern
D001,
DOO1_TXT,
D002,
D002_TXT,
.
.
.
.
.
D100,
D100_TXT
 

Hi,
I need to delete and reload a large table every day. Though there may not be huge change on the data. If I use below statement will it work? My understancding is collecting unnecessary stats can degrade overall performance. I don't wnat to collect stats if there is not more than 10% change in data.

Hello,
I have recently encountered an issue where after running a COLLECT STATS statement on a table, the statistics displayed in the HELP STATS results aren't updating. I found that if I DROP STATS on the index or column and then re-collect stats, it will correctly display.

Hi,
What are differences between summary statistics and satistics? When we should opt for summary statistics instead of statistics?

The accuracy of sample statistics has been greatly improved over the last Teradata releases. So I usually try to use sample stats on most of the big tables and I found them to be reliable on many columns, not only the officially recommended unique or nearly-unique columns, e.g. on DATEs.

Statistical information is vital for the optimizer when it builds query plans. But collecting statistics can involve time and resources. By understanding and combining several different statistics gathering techniques, users of Teradata can find the correct balance between good query plans and the time required to ensure adequate statistical information is always available.

This recently-updated compilation of statistics collection recommendations are intended for sites that are on any of the Teradata Database 14.10, 15.0, or 15.10 software release levels. Some of these recommendations apply to releases earlier than Teradata Database 14.10 and some rely on new features available starting in Teradata Database 14.10.  Statistics collection functionality in the Teradata Database works the same in 14.10, 15.0 and 15.10 releases.

For greater detail on collecting statistics for Teradata Database 14.10, see the orange book titled:  Teradata Database 14.10 Statistics Enhancements by Rama Krishna Korlapati.

Contributors: Carrie Ballinger, Rama Krishna Korlapati, Paul Sinclair, September 11, 2014

An earlier blog post focused on simple steps to get started using the Teradata 14.10 Automated Statistics Management (AutoStats) feature.  One of the new capabilities that AutoStats relies on when it streamlines statistics collection is the new “Threshold” option.  Threshold applies some intelligence about when statistics actually need to be re-collected, allowing the optimizer to skip some recollections.  

Although you will probably want to begin relying on AutoStats when you get to 14.10, you don’t have to be using AutoStats to take advantage of threshold, as the two features are independent from one another.  This post will give you a simple explanation of what the threshold feature is, what default threshold activity you can expect when you get on 14.10, and what the options having to do with threshold do for you.  And you’ll get some suggestions on how you can get acquainted with threshold a step at a time.  

Database Version: Teradata 13.10.0609  13.10.06.09A
Provider Version: ODBC 14.00.00.04
 
CREATE TABLE MY_TABLE AS (
SELECT COL1
 , COL2
 , COL3
FROM DB_TABLE
)WITH DATA
PRIMARY INDEX (COL1);
COLLECT STATISTICS MY_TABLE INDEX(COL1);
 

I have a long query generated from Microstrategy with lots of joins which was running our of spool space. I checked the query and confirmed that we had stats collected on all the join columns and stats were current. Then there was one column on which I dropped stats, it had both single and multi column stats after which the query started to run fine.

Hi
Can i collect statistics  for Volatile tables&Global temp tables.
Please explain more details about those tables statistics collections information
 
Thanks
Gopal

We have a stored procedure which will look at a table and collect statistics on different columns and indexes based on types etc.  We use this inside of other stored procedures after loading staging tables.

For some reason I keep getting the following error:

Hi All !!!

In Teradata 12, collect statistics step fails with spool space error for the below mentioned table. Skewness of the table is under the acceptable range. PFB the table structure and collect statistics step:

CREATE SET TABLE DB_1.TABLE_1, NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
COL_A DECIMAL(11,0)
, COL_B CHAR(3)

From Books:

When doing collect stats on fields/indexes , system collects the information like: total row counts of the table, how many distinct values are there in the column, how many rows per value, is the column indexed, if so unique or non unique etc

The above information are known as statistics.
___________________________________________________________________________
Example:
Table "Emp_Details" has the primary index "Emp_Name". I use this index "Emp_Name" in my joining condition/Where clause.

case1: I dont have collect stats on "Emp_Details" table.

I’ve mentioned it before, Marcio has blogged about it, customers have brought it up at the Partners Conferences. It’s cheap, fast, risk-free, with immediate results. But some of you are still not getting it. Or it could be you’re one of the few who truly don’t need it.

Collecting full statistics involves scanning the base table and performing a sort to compute the number of occurrences for each distinct value. For some users, the time and resources required to adequately collect statistics and keep them up-to-date is a challenge, particularly on large tables.

Please  note:  This posting is relevant only to software releases prior to Teradata Database 14.0.  Ignore this recommendation if you are on 14.0 or greater software.

This article delves into the potential excessive use of Multi-Column (MC) Statistics and a process for identifying those that are not providing benefit and should be removed. This will reduce complexity and save resources on the re-collection of these redundant statistics.

You’re in a time crunch. Maybe you’ve got an undersized backup environment that is incredibly slow, or you just have important stuff happening and you need to keep the software upgrade window as short as possible.

Authors: Carrie Ballinger, Rama Krishna Korlapati, Paul Sinclair

Looking for a fresh perspective on collecting statistics, or just want a confirmation that you’re on the right track? Either way, you’ll want to read this quick summary of recommendations for Teradata 12 stats collection.

Until you have Teradata V13 Statistics Wizard Easy Feature available for your Teradata installation, here is a set of stored procedures that will generate and/or run collect statistics statements based on a set of index, column and referential integrity rules.