in black and white
Main menu
Share a book About us Home
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 116 117 118 119 120 121 < 122 > 123 124 125 126 127 128 .. 241 >> Next


COLLECT STATISTICS is an extension to ANSI. If used, it is flagged when the SQL flagger is enabled.


Lon J


COLUMN — col_name1 — TTTe"
—INDEX - (I co_name21) —
INDEX name



Syntax Element... Description
tname Specifies the name of the table for which statistics are to be collected.
col_name1 Indicates that the collection is for a single column. Specifies the name of a column for which statistics are to be collected.
col_name2 Indicates that the collection is for one or more columns that comprise an index on the table. Specifies the names of columns for which statistics are to be collected.
name Means collect statistics on a named index. In this case, no column names are needed.

COLLECT STATISTICS can be useful in situations where data is to Why Collect Statistics? be accessed using a column or index that may have an uneven

distribution of values.


Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide





Collecting Statistics When No COLUMN or INDEX Is Specified

Single Column INDEXES

Also, current statistics can enable the SQL optimizer to better choose the fastest method of data access and selection, particularly in cases of complex queries or queries that involve joins.

NCR strongly recommends that statistics be collected on the primary index of small tables involved in joins.

You must have the DROP privilege on a table in order to collect statistics for the table.

The following lists rules for using COLLECT STATISTICS:

• COLLECT STATISTICS is a DDL statement; as such, it must be entered as a single-statement request, as the only statement in a macro, or as the only or last statement in a transaction that is enclosed by BEGIN and END TRANSACTION statements.

• When a COLLECT STATISTICS statement is executed, an ACCESS lock is placed on the table.

• COLLECT STATISTICS can be run on an empty table. This allows defining the columns and/or indexes for subsequent collection. In previous Teradata SQL releases, COLLECT STATISTICS could only be run on a table with at least one row. When HELP STATISTICS is run, the ‘Unique Value’ column returns the value of 1, the default value.

• If you attempt a COLLECT STATISTICS on a table that has had a DROP STATISTICS run, and if it has not had a COLLECT STATISTICS COLUMN or COLLECT STATISTICS INDEX run on it, an error is reported.

Because COLLECT STATISTICS must do a full file scan, it can take a long time to complete; the duration is dependent on the size of the table, and the load on the system. Columns with no indexes or unique primary indexes take more time; columns with non-unique secondary indexes take less time.

If COLUMN or INDEX is not specified, COLLECT STATISTICS updates statistics for columns or indexes that had been previously collected (provided that a DROP STATISTICS statement has not been entered for one or more of these columns or indexes in the meantime).

If COLUMN or INDEX is not specified, the number of columns and indexes included in tname is restricted to 40 or less.

If an index has a single column col_name, the collection of statistics for COLUMN col_name is the same as that for INDEX(col_name).

Teradata RDBMS for UNIX SQL Reference

Teradata SQL Syntax Guide


If a table has been extensively modified (more than 10% of the rows Keep Statistics Current 8 added or deleted) since statistics were last collected, the outdated

statistics may cause poor access optimization and bad join plans. Therefore, statistics should be periodically refreshed or dropped. Keep statistics current!

The efficacy of collected statistics vary with the types of access used on a table. If performance does not seem to be improved by the statistics collected, the DROP STATISTICS statement may be used to remove the statistics.

Do a HELP STATISTICS to see which columns and indexes currently have statistics, and to see a portion of the collected information.

You can specify either an index name or an index definition in a Using Named Mexes COLLECT (or DROP STATISTICS) statement, but not both.

To collect (or drop statistics) from an unnamed index, you must specify the complete index definition.

The following examples illustrate the use of COLLECT STATISTICS:


Assuming that an index is defined on the EmpNo and Name Example 1 columns of the Employee table, the following statement:


collects statistics for that index.

Subsequent entry of the following statement:

Example 2


refreshes the statistics for the EmpNo/Name index, along with any other statistics previously collected for the Employee table.

Example 3: Named


Indexes ON table_1 INDEX unique_
Previous << 1 .. 116 117 118 119 120 121 < 122 > 123 124 125 126 127 128 .. 241 >> Next