DIAGNOSTIC SET SAMPLES

Purpose

Instructs the Optimizer on the test system to use the random AMP samples from the target (production) system for emulations.

Syntax - General Form

Syntax - Disable All Samples Form

where:

Syntax element …	Specifies …
ALL	that all random AMP statistical samples for the specified scope level are to be disabled.
target_system_name	the name of the target (production) system to be emulated. For information on naming objects, see SQL Fundamentals. target_system_name is not case sensitive and can be up to 63 characters in length. Names that contain pad characters or nonstandard characters must be enclosed in APOSTROPHE characters. target_system_name must be defined in SystemFE.Opt_RAS_Table before you can perform this request.
NOT	to disable sample selection.
SESSION	that sample values for the target system are in effect for the duration of the current session until one of the following events occurs: You log off the session You restart the database You change the samples in effect by performing the following request: DIAGNOSTIC SET SAMPLES target_system_name NOT ON FOR SESSION TABLE table_name
SYSTEM	that sample values for the target system are in effect for all sessions across all restarts and reboots until you perform the following request: DIAGNOSTIC SET SAMPLES target_system_name NOT ON FOR SYSTEM TABLE table_name
database_name	the name of the database in which table_name is defined. If the name contains pad characters or non-standard characters, it must be demarcated by APOSTROPHE characters.
table_name	the name of the table for which random AMP sample statistics emulation is to be performed. If the name contains pad characters or non-standard characters, it must be demarcated by APOSTROPHE characters.

ANSI Compliance

DIAGNOSTIC SET SAMPLES is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges

You must have SELECT privileges on SystemFE.Opt_RAS_Table to set samples on or off.

Target Level Emulation and DIAGNOSTIC SET SAMPLES

DIAGNOSTIC SET SAMPLES requires Target Level Emulation to be enabled in order to set scope level cost estimation data.

See SQL Request and Transaction Processing for more information.

Session Mode Restrictions

You cannot perform DIAGNOSTIC SET SAMPLES in ANSI session mode or within explicit transaction boundaries in Teradata session mode.

Scope Level Precedence

Scope levels have a sequential precedence. The following table lists the precedence ratings for the various scope levels for DIAGNOSTIC SET SAMPLES:

Precedence	Option
1	SESSION
2	SYSTEM

Note that DIAGNOSTIC SET SAMPLES does not offer scope levels of IFP or REQUEST.

Limits on Sample Emulation

Random AMP statistical samples can be emulated for a maximum of 128 tables at a time per scope level.

Restrictions on Creating Or Dropping an Index on a Table Concurrent With Random AMP Sample Emulation on That Table

You cannot create or drop any of the following indexes for a table while that table is subject to random AMP sample emulation:

Hash (see “CREATE HASH INDEX” and “DROP HASH INDEX” in SQL Data Definition Language)

Join (see “CREATE JOIN INDEX” and “DROP JOIN INDEX” in SQL Data Definition Language)

Secondary (see “CREATE INDEX” and “DROP INDEX” in SQL Data Definition Language)

Otherwise, the system returns an error.

To disable random AMP sampling, use the DIAGNOSTIC HELP SAMPLES request (see “DIAGNOSTIC HELP SAMPLES” on page 636) to determine which samples have been set, then use DIAGNOSTIC SET SAMPLES to disable them.

To use random AMP sampling on the table with the index change, you must use the following general procedure:

1 Make the index change on the target system.

2 Extract a fresh random AMP sample from the target system.

3 Apply the fresh sample to the source system.

Examples

The following examples require that a corresponding row exist in the SystemFE.Opt_RAS_Table with the column values listed below:

Column Name	Value Name
System Name	test_1
Database Name	employee
Table Name	payroll

Example

Enable the imported random AMP statistical samples for the payroll table in database employee on test machine test_1.

     DIAGNOSTIC SET SAMPLES test_1

     ON FOR SESSION TABLE employee.payroll;

Example

Disable the imported random AMP statistical samples for the payroll table in database employee on test machine test_1.

     DIAGNOSTIC SET SAMPLES test_1

     NOT ON FOR SESSION TABLE employee.payroll;

Example

Disable all random AMP statistical samples for all tables enabled at the SYSTEM scope level.

     DIAGNOSTIC SET ALL SAMPLES

     NOT ON FOR SYSTEM;

Example

Disable all random AMP statistical samples for all tables enabled at the SESSION scope level.

     DIAGNOSTIC SET ALL SAMPLES

     NOT ON FOR SESSION;

For More Information

For more information about random AMP samples, see:

“DIAGNOSTIC DUMP SAMPLES” on page 634

“DIAGNOSTIC HELP SAMPLES” on page 636

SQL Request and Transaction Processing