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

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


Options are :

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

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

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 :

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

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

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 options are available when workload limits are applied by the Teradata Dynamic Query Manager (TDQM)? (Choose two.)


Options are :

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

Answer : reject the query delay the query

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


Options are :

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

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

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


Options are :

  • One input relation has high confidence and the other has high or index join confidence. (Correct)
  • 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)

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

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 NOT be specified.
  • They must be specified. (Correct)
  • They must NOT be updated. (Correct)
  • They must be updated.

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

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


Options are :

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

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

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 :

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

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

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


Options are :

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

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 allocation group (Correct)
  • change the priority of the user's session (Correct)
  • modify the amount of CPU available to Priority Scheduler
  • move the user running the query to another performance period
  • modify the weights assigned to the user's resource partition (Correct)

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

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


Options are :

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

Answer : Object Definitions

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


Options are :

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

Answer : running remote console commands workload analysis performance monitoring

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


Options are :

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

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

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


Options are :

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

Answer : absolute relative

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


Options are :

  • collect statistics on the partitioning columns
  • create a secondary index on the columns of the primary index (Correct)
  • 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 three milestones can be defined for a performance period? (Choose three.)


Options are :

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

Answer : time-of-day session query

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


Options are :

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

Answer : Object Definitions

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


Options are :

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

Answer : merge

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


Options are :

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

Answer : ROWS PARTITION BY ORDER BY

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 EXPLAIN plan will never contain this output.
  • The query is using values found in a secondary index. (Correct)
  • The query is using values found in a join index.
  • The query is using values found in a primary index.

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

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


Options are :

  • drop tables in multiple databases (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
  • begin logging on users provided in an argument to the procedure (Correct)
  • 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 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 typical processing time for queries during the defined timeframe (Correct)
  • the total system CPU activity for a specific timeframe
  • the size of the tables involved in queries during the defined time period
  • the names of the tables accessed during a specific timeframe (Correct)

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

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


Options are :

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

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

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


Options are :

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

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

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


Options are :

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

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

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 how recently the statistics were refreshed
  • checking the confidence level in the EXPLAIN output (Correct)
  • HELP STATISTICS

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

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 multiple statement requests to improve performance.
  • TPump updates tables from a work table.
  • MultiLoad uses table locks. (Correct)

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

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


Options are :

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

Answer : session CPU seconds time-of-day query CPU 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 WITH NO DATA; (Correct)
  • CREATE TABLE t9 AS (SELECT * FROM t7) WITH NO DATA;
  • CREATE TABLE t9 AS t7 WITHOUT DATA;
  • CREATE TABLE t9 AS t7;

Answer : CREATE TABLE t9 AS t7 WITH NO DATA;

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions