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

Most queries access a particular table using date ranges by quarter. You consider implementing a partitioned primary index (PPI) on this table that contains daily transactions for the past two years. A weekly batch job deletes the oldest ten days from the table. Which partitioning method provides the greatest performance benefit to the delete job?


Options are :

  • defining a partition for each week
  • defining a partition for each month
  • defining a partition for each quarter
  • defining a partition for each day (Correct)

Answer : defining a partition for each day

Which two can potentially eliminate join steps? (Choose two.)


Options are :

  • single-table join index
  • multi-table join index (Correct)
  • referential integrity (RI) constraints (Correct)
  • matching primary index columns on the join conditions

Answer : multi-table join index referential integrity (RI) constraints

Which three functions can be performed through Teradata Performance Monitor? (Choose three.)


Options are :

  • change access rights
  • display CPU efficiency across the AMPs (Correct)
  • display current spool space and spool limits
  • view text and EXPLAIN of running queries (Correct)
  • display blocked and blocking sessions (Correct)

Answer : display CPU efficiency across the AMPs view text and EXPLAIN of running queries display blocked and blocking sessions

An EXPLAIN output contains the following phrase: "from all partitions of" Which two statements are true? (Choose two.)


Options are :

  • A query has referenced a partitioned table with a join to a non-partitioned primary index (NPPI) table. (Correct)
  • A query has referenced a partitioned table with the primary index value, but not the partitioning value. (Correct)
  • A query has referenced a partitioned table with the PARTITION keyword in the WHERE clause.
  • A query has referenced a partitioned table with the primary index value and the partitioning value.

Answer : A query has referenced a partitioned table with a join to a non-partitioned primary index (NPPI) table. A query has referenced a partitioned table with the primary index value, but not the partitioning value.

The warning mode feature of the Teradata Dynamic Query Manager (TDQM) is only available for which two types of restrictions? (Choose two.)


Options are :

  • query resource restrictions (Correct)
  • object access restrictions (Correct)
  • workload limits
  • access right violations
  • delayed queries

Answer : query resource restrictions object access restrictions

Consider the following SQL statement: SELECT city, state, SAMPLEID FROM stores SAMPLE WITH REPLACEMENT WHEN state = 'WI' THEN 4 ELSE 3 END ORDER BY 3; How many distinct SAMPLEID values are created, assuming there are ten rows from every state in the stores table?


Options are :

  • 7
  • 4
  • 3
  • 2 (Correct)

Answer : 2

A table is currently indexed on location, date, and item. The join index for the table is currently defined with a primary index of date and location. A new application needs to access the table by various dates and items. What can be done to improve the new application's query performance?


Options are :

  • define a partitioned primary index (PPI) on the table, partitioning by date and item
  • define another join index on the table with a primary index of date and item (Correct)
  • define a hash index on the table with a primary index of date and location
  • define a global temporary table, inserting the appropriate dates and items

Answer : define another join index on the table with a primary index of date and item

Which two utilities can update a multiset table with a sparse join index, a value-ordered secondary index, and can update five tables from one source? (Choose two.)


Options are :

  • BTEQ (Correct)
  • MultiLoad
  • FastLoad
  • TPump (Correct)

Answer : BTEQ TPump

What are two application design issues for Active Data Warehouse applications that differ from those found in traditional enterprise data warehouses? (Choose two.)


Options are :

  • mixed workloads (Correct)
  • data integrity
  • data freshness (Correct)
  • fallback protection

Answer : mixed workloads data freshness

Which two referential integrity (RI) options are available in the ALTER TABLE command? (Choose two.)


Options are :

  • DUAL BEFORE JOURNAL
  • DROP INCONSISTENT REFERENCES (Correct)
  • MODIFY PRIMARY INDEX
  • ON COMMIT DELETE ROWS
  • WITH CHECK OPTION (Correct)

Answer : DROP INCONSISTENT REFERENCES WITH CHECK OPTION

How do hash indexes differ from single-table join indexes?


Options are :

  • Hash indexes require different syntax for creation. (Correct)
  • Hash indexes are maintained differently by the system when the base table is updated.
  • Hash indexes must have the same primary index as the base table.
  • Hash indexes use value ordering.

Answer : Hash indexes require different syntax for creation.

When planning workload restrictions using Teradata Dynamic Query Manager (TDQM), what information may be available from Database Query Log (DBQL) tables? (Choose three.)


Options are :

  • node efficiency levels
  • the number of short queries run during a specific time period (Correct)
  • tables accessed during the requested period (Correct)
  • system-level CPU and disk I/O utilization percentages
  • the number of queries that a particular user has run in a time frame (Correct)

Answer : the number of short queries run during a specific time period tables accessed during the requested period the number of queries that a particular user has run in a time frame

Which two statements are true concerning MERGE processing? (Choose two.)


Options are :

  • MERGE can be specified as a triggered action.
  • MERGE cannot be prepared as dynamic SQL.
  • Only single row statements are supported in MERGE operations. (Correct)
  • A MERGE statement can operate on a table with an identity column. (Correct)

Answer : Only single row statements are supported in MERGE operations. A MERGE statement can operate on a table with an identity column.

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


Options are :

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

Answer : PARTITION BY ROWS ORDER BY

What are three factors to consider when migrating a non-partitioned table to a partitioned table? (Choose three.)


Options are :

  • whether data falls outside the partition ranges (Correct)
  • how the data are accessed (Correct)
  • choosing columns that maximize partition elimination (Correct)
  • number of columns in the table
  • if the table contains an identity column

Answer : whether data falls outside the partition ranges how the data are accessed choosing columns that maximize partition elimination

What are two benefits of using a partitioned primary index (PPI)? (Choose two.)


Options are :

  • can use partition elimination to improve range searches (Correct)
  • can reduce the size of the base table through partitioning
  • can improve query response time by changing the primary index access path to the base table
  • can facilitate table maintenance (Correct)

Answer : can use partition elimination to improve range searches can facilitate table maintenance

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions