NCR NR0-017 Teradata Masters Update V2R5 Practice Exam Set 4

Which two pieces of information from Database Query Log (DBQL) may be used to define a workload policy for Teradata Dynamic Query Manager (TDQM)? (Choose two.)


Options are :

  • the size of the tables involved in queries during the defined time period
  • the names of the tables accessed during a specific timeframe (Correct)
  • the typical processing time for queries during the defined timeframe (Correct)
  • the total system CPU activity for a specific timeframe

Answer : the names of the tables accessed during a specific timeframe the typical processing time for queries during the defined timeframe

In Teradata Visual Explain, which pull-down menu selection displays nested views used by a query?


Options are :

  • Nested Views
  • Display Objects
  • View Definitions
  • Object Definitions (Correct)
  • Show Definitions

Answer : Object Definitions

What are three reasons why you may want to mini-batch load into a staging table before loading the actual target table? (Choose three.)


Options are :

  • surrogate key assignments (Correct)
  • to provide a backup copy of the data
  • to MultiLoad without counting against the maximum load utility limit
  • precalculations and scoring (Correct)
  • data cleansing and transformation (Correct)

Answer : surrogate key assignments precalculations and scoring data cleansing and transformation

In Teradata Visual Explain, which pull-down menu selection displays nested views used by a query?


Options are :

  • Show Definitions
  • Object Definitions (Correct)
  • Nested Views
  • View Definitions

Answer : Object Definitions

In multi-value compression, which two may be compressed? (Choose two.)


Options are :

  • identity columns
  • columns that are part of a secondary index (Correct)
  • volatile table columns
  • columns that are in the primary index
  • BYTE data type columns (Correct)

Answer : columns that are part of a secondary index BYTE data type columns

While using ordered analytic functions, which three SQL options can be used within a window definition? (Choose three.)


Options are :

  • ORDER BY (Correct)
  • ROWS (Correct)
  • GROUP BY
  • PARTITION BY (Correct)

Answer : ORDER BY ROWS PARTITION BY

Which three milestones can be defined for a performance period? (Choose three.)


Options are :

  • weight
  • day-of-month
  • time-of-day (Correct)
  • query (Correct)
  • session (Correct)

Answer : time-of-day query session

What can improve performance when primary index retrievals from a partitioned primary index (PPI) table run poorly?


Options are :

  • create a secondary index on the columns of the primary index (Correct)
  • collect statistics on the partitioning columns
  • collect statistics on the primary index columns individually
  • create a non-unique secondary index (NUSI) on the partitioning columns

Answer : create a secondary index on the columns of the primary index

Which two functions does the Teradata Statistics Wizard perform? (Choose two.)


Options are :

  • executes the statistics collection recommendations (Correct)
  • recommends recollection of statistics based on table skew (Correct)
  • displays current versus captured statistics information
  • recommends secondary indexes
  • reproduces Optimizer-related issues in a test environment

Answer : executes the statistics collection recommendations recommends recollection of statistics based on table skew

Which three things can you do to improve a query's performance without restarting Teradata? (Choose three.)


Options are :

  • modify the weights assigned to the user's resource partition (Correct)
  • modify the weights assigned to the user's allocation group (Correct)
  • move the user running the query to another performance period
  • change the priority of the user's session (Correct)
  • modify the amount of CPU available to Priority Scheduler

Answer : modify the weights assigned to the user's resource partition modify the weights assigned to the user's allocation group change the priority of the user's session

What do BEGIN QUERY LOGGING options allow you to exclude from logging?


Options are :

  • statements with fewer than a specified number of characters of SQL text
  • queries that run in fewer than a specified number of seconds (Correct)
  • queries that access fewer than a specified number of tables
  • statements that access data dictionary tables

Answer : queries that run in fewer than a specified number of seconds

For the following table definition: CREATE TABLE t7 (d1 DATE, i1 INTEGER) PRIMARY INDEX (i1); Which statement creates an empty table with the same table definition?


Options are :

  • CREATE TABLE t9 AS t7;
  • CREATE TABLE t9 AS t7 WITHOUT DATA;
  • CREATE TABLE t9 AS t7 WITH NO DATA; (Correct)
  • CREATE TABLE t9 AS (SELECT * FROM t7) WITH NO DATA;

Answer : CREATE TABLE t9 AS t7 WITH NO DATA;

Teradata Manager's utilities can be used for which three activities? (Choose three.)


Options are :

  • running remote console commands (Correct)
  • performance monitoring (Correct)
  • workload analysis (Correct)
  • distributing Teradata application software to remote clients
  • archiving and restoring data

Answer : running remote console commands performance monitoring workload analysis

What are two restrictions on the partitioning columns when using MultiLoad or TPump to update partitioned primary index (PPI) tables? (Choose two.)


Options are :

  • They must be specified. (Correct)
  • They must NOT be updated. (Correct)
  • They must NOT be specified.
  • They must be updated.

Answer : They must be specified. They must NOT be updated.

Which two statements are true when comparing MultiLoad and TPump? (Choose two.)


Options are :

  • TPump performs better on a low volume of changes. (Correct)
  • MultiLoad uses table locks. (Correct)
  • TPump updates tables from a work table.
  • MultiLoad uses multiple statement requests to improve performance.

Answer : TPump performs better on a low volume of changes. MultiLoad uses table locks.

An EXPLAIN output contains the following phrase:"with high confidence" Which two statements are true? (Choose two.)


Options are :

  • Restricting conditions exist on an index with collected statistics but is OR'ed with another column with collected statistics.
  • One input relation has high confidence and the other has low confidence.
  • Restricting conditions exist on an index or column that has collected statistics. (Correct)
  • One input relation has high confidence and the other has high or index join confidence. (Correct)

Answer : Restricting conditions exist on an index or column that has collected statistics. One input relation has high confidence and the other has high or index join confidence.

Which two options are available when workload limits are applied by the Teradata Dynamic Query Manager (TDQM)? (Choose two.)


Options are :

  • change the priority of the query
  • log the query in the TDQM log file
  • delay the query (Correct)
  • reject the query (Correct)

Answer : delay the query reject the query

Which three statements about collecting statistics are true? (Choose three.)


Options are :

  • Multi-column statistics can be collected without creating an index. (Correct)
  • Statistics can be collected on a maximum of 16 columns and indexes per table.
  • Statistics can be collected on a maximum of 512 columns and indexes per table. (Correct)
  • Statistics can be collected using a sample of the table data. (Correct)
  • Statistics can be useful in determining which AMP(s) will be accessed for query processing.

Answer : Multi-column statistics can be collected without creating an index. Statistics can be collected on a maximum of 512 columns and indexes per table. Statistics can be collected using a sample of the table data.

An EXPLAIN output contains the following: "by way of a RowHash match scan Which type of join will be performed?


Options are :

  • hash
  • outer
  • inner
  • merge (Correct)
  • rowID

Answer : merge

Which two statements are true about join indexes? (Choose two.)


Options are :

  • They can substitute for summary tables when used with aggregation (Correct)
  • They can only be used with three table joins or fewer, otherwise the Optimizer will not use them.
  • They can provide an alternate path to a table if there are more than one commonly used column(s) to access the table. (Correct)
  • They can be defined using a full outer join.

Answer : They can substitute for summary tables when used with aggregation They can provide an alternate path to a table if there are more than one commonly used column(s) to access the table.

Which two statements about the use of soft referential integrity (RI) are true? (Choose two.)


Options are :

  • It is more costly to use than traditional RI
  • It can only be implemented at the column level.
  • It is not enforced by the database. (Correct)
  • It can be used for join elimination. (Correct)

Answer : It is not enforced by the database. It can be used for join elimination.

When using the Priority Scheduler, which three types of milestones may be specified for a performance period? (Choose three.)


Options are :

  • time-of-day (Correct)
  • query CPU seconds (Correct)
  • session disk I/O count
  • session CPU seconds (Correct)
  • query disk I/O count

Answer : time-of-day query CPU seconds session CPU seconds

An EXPLAIN output contains the following phrase: "by way of a traversal of index #12 extracting row ids only" Which statement is true?


Options are :

  • The query is using values found in a join index.
  • The query is using values found in a primary index.
  • The EXPLAIN plan will never contain this output.
  • The query is using values found in a secondary index. (Correct)

Answer : The query is using values found in a secondary index.

In Priority Scheduler, which two tasks are important in planning the allocation of system resources? (Choose two.)


Options are :

  • recognizing the goal of using Priority Scheduler (Correct)
  • calculating how many CPU cycles are available
  • gaining an understanding of the current workload (Correct)
  • defining at least four resource partitions for workload balance

Answer : recognizing the goal of using Priority Scheduler gaining an understanding of the current workload

Stored procedures may be used to perform which three tasks? (Choose three.)


Options are :

  • drop tables in multiple databases (Correct)
  • begin logging on users provided in an argument to the procedure (Correct)
  • show statistics on tables provided in an argument to the procedure
  • display EXPLAIN output for a query provided in an argument to the procedure
  • collect statistics on tables provided in an argument to the procedure (Correct)

Answer : drop tables in multiple databases begin logging on users provided in an argument to the procedure collect statistics on tables provided in an argument to the procedure

Which two Priority Scheduler policies define a fixed limit on the resources available to a user? (Choose two.)


Options are :

  • restrictive
  • absolute (Correct)
  • relative (Correct)
  • immediate
  • default

Answer : absolute relative

Which three functions are supported by the Teradata Dynamic Query Manager (TDQM)? (Choose three.)


Options are :

  • limiting access to host groups
  • limiting the number of reserved AMP worker tasks
  • limiting access to database objects (Correct)
  • limiting queries with specific join types (Correct)
  • delaying query initiation (Correct)

Answer : limiting access to database objects limiting queries with specific join types delaying query initiation

What are two functions of the Teradata Index Wizard? (Choose two.)


Options are :

  • allows for "what-if" analysis (Correct)
  • allows you to schedule index creation (Correct)
  • allows importing of workload from Teradata Manager
  • allows for a visual explanation of the index creation plan

Answer : allows for "what-if" analysis allows you to schedule index creation

Consider the following statement. CREATE TABLE Sales_Revenue ( store_id integer not null, item_id integer not null,total_revenue decimal(9,2)) UNIQUE PRIMARY INDEX (store id, item id) PARTITION BY CASE_N( total_revenue < 2000, total_revenue < 4000, NO CASE, UNKNOWN);What is true?


Options are :

  • NO CASE must be specified before the first inequality test.
  • A unique primary index (UPI) is not allowed because the partitioning column is not part of the primary index. (Correct)
  • A unique primary index (UPI) is not allowed because NO CASE may result in a partition with too many rows.
  • This statement is valid.
  • The use of NO CASE and UNKNOWN is mutually exclusive.

Answer : A unique primary index (UPI) is not allowed because the partitioning column is not part of the primary index.

Which two can be used to validate the effectiveness and usefulness of statistics on a given query? (Choose two.)


Options are :

  • Teradata Statistics Wizard (Correct)
  • checking the confidence level in the EXPLAIN output (Correct)
  • checking how recently the statistics were refreshed
  • HELP STATISTICS

Answer : Teradata Statistics Wizard checking the confidence level in the EXPLAIN output

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions