Examples - Teradata Database

Example: Collect Statistics on a Single-Column NUSI

This example collects statistics on a single-column NUSI named orderDateNUPI on the order_date column from a random sample of 10 percent of the rows in the orders table and writes them to the TableStatistics table of the QCD database named MyQCD.

     COLLECT STATISTICS FOR SAMPLE 10 PERCENT
     ON orders INDEX orderDateNUPI
     INTO MyQCD;

Example: Collect Statistics On Index Using an Alternate Syntax

This example collects sampled statistics on the same index as Example: Collect Statistics on a Single-Column NUSI using different syntax:

     COLLECT STATISTICS FOR SAMPLE 10 PERCENT
     ON orders INDEX (order_date)
     INTO MyQCD;

Example: Collecting Single-Column PARTITION Statistics

This example collects statistics on the system-derived PARTITION column for the row-partitioned orders table and writes them to a user-defined QCD called myqcd.

Even though you have specified a sampling percentage of 30 percent, the system ignores it and uses a value of 100 percent. See Collecting QCD Statistics on the PARTITION Column of a Table.

     COLLECT STATISTICS FOR SAMPLE 30 PERCENT
     ON orders COLUMN PARTITION
     INTO myqcd;

Example: Collecting Multicolumn PARTITION Statistics

This example collects multicolumn sampled statistics on the following column set for the row-partitioned orders table and writes them to a user-defined QCD called myqcd:

• System-derived PARTITION column
• quant_ord
• quant_shpd

Because you are requesting multicolumn statistics, the system honors the specified sampling percentage of 20 percent, unlike the case for single-column PARTITION statistics. See Collecting QCD Statistics on the PARTITION Column of a Table and Example: Collect Statistics on a Single-Column NUSI.

     COLLECT STATISTICS FOR SAMPLE 20 PERCENT
     ON orders COLUMN (quant_ord, PARTITION, quant_shpd)
     INTO myqcd;