DIAGNOSTIC SET SAMPLES - Teradata Database

Purpose

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

Required Privileges

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

Syntax - General Form

Syntax - Disable All Samples Form

Syntax Elements

ALL

All random AMP statistical samples for the specified scope level are to be disabled.

target_system_name

Name of the target (production) system to be emulated. For information on object naming, 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

Disable sample selection.

SESSION

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

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

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

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.

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.

For more information, see SQL Request and Transaction Processing .

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:

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 to determine which samples have been set, then use DIAGNOSTIC SET SAMPLES to disable them. See “DIAGNOSTIC HELP SAMPLES”.

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:

Example: Enable Imported Random AMP Statistical Samples

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 Imported Random AMP Statistical Samples

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 Random AMP Statistical Samples at the SYSTEM Level

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 Random AMP Statistical Samples at the SESSION Level

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

     DIAGNOSTIC SET ALL SAMPLES
     NOT ON FOR SESSION;