Instructs the Optimizer on the test system to use the random AMP samples from the target (production) system for emulations.
where:
|
Syntax element … |
Specifies … |
|
ALL |
All random AMP statistical samples for the specified scope level are to be disabled. |
|
target_system_ |
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: DIAGNOSTIC SET SAMPLES target_system_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 |
|
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. |
DIAGNOSTIC SET SAMPLES is a Teradata extension to the ANSI SQL:2011 standard.
You must have SELECT privileges on SystemFE.Opt_RAS_Table to set samples on or off.
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.
You cannot perform DIAGNOSTIC SET SAMPLES in ANSI session mode or within explicit transaction boundaries in Teradata session mode.
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.
Random AMP statistical samples can be emulated for a maximum of 128 tables at a time per scope level.
You cannot create or drop any of the following indexes for a table while that table is subject to random AMP sample emulation:
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” on page 617.
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.
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 |
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;
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;
Disable all random AMP statistical samples for all tables enabled at the SYSTEM scope level.
DIAGNOSTIC SET ALL SAMPLES
NOT ON FOR SYSTEM;
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 about random AMP samples, see: