NR0-016 NCR Teradata Application Development V2R5 Exam Set 2

What is a Bit Map Set Manipulation Step (BMSMS)?


Options are :

  • handling weakly selective secondary indexes that have been ANDed (Correct)
  • taking a single row of a table and using a secondary index value that hashes to the AMP on which the table row is stored
  • combining rows under control of a UNION, EXCEPT, MINUS, or INTERSECT operator
  • relocating data in preparation for an upcoming join step

Answer : handling weakly selective secondary indexes that have been ANDed

Which statement applies to applications written for Teradata?


Options are :

  • The applications should be written to use multiple instances of the program running in parallel.
  • The applications should use set manipulation to leverage parallelism. (Correct)
  • The applications should use the Optimizer to resolve rules-based calculations.
  • The applications should minimize the use of extensions to ANSI SQL.

Answer : The applications should use set manipulation to leverage parallelism.

What are two ways to load data using a macro? (Choose two.)


Options are :

  • FastLoad
  • MultiLoad
  • INSERT/SELECT (Correct)
  • TPump (Correct)

Answer : INSERT/SELECT TPump

In which two cases can an application read a primary index value from a table that is being populated with an INSERT/SELECT ? (Choose two.)


Options are :

  • anytime during the INSERT/SELECT
  • after the row-hash lock has been released
  • when the application can issue a LOCKING FOR ACCESS (Correct)
  • after the INSERT/SELECT has finished loading all the data (Correct)

Answer : when the application can issue a LOCKING FOR ACCESS after the INSERT/SELECT has finished loading all the data

Which statement is true for a Merge Join?


Options are :

  • It compares every qualifying row from one table to every qualifying row from the other table.
  • It is a join where only the rows that do not satisfy any condition specified in the request are joined.
  • It retrieves rows from two tables and then puts them onto a common AMP, based on the row hash of the columns involved in the join. (Correct)
  • It is a join for which the WHERE conditions specify a constant value for a unique index in one table and those conditions also match some column of that single row to the primary or secondary index of the second table.

Answer : It retrieves rows from two tables and then puts them onto a common AMP, based on the row hash of the columns involved in the join.

Given: USING (x INTEGER, y INTEGER, z INTEGER) EXEC Macro1(:x, :y, :z); Which three statements about the execution of this macro are true? (Choose three.)


Options are :

  • It should be demographically dependent.
  • No values are present for the Optimizer. (Correct)
  • It will be cached immediately. (Correct)
  • It should be demographically independent. (Correct)
  • It contains multiple statements.

Answer : No values are present for the Optimizer. It will be cached immediately. It should be demographically independent.

Which two statements are true about placement of the residual conditions? (Choose two.)


Options are :

  • Conditions against the outer table should be residual to the WHERE . (Correct)
  • Conditions against the inner table should be residual to the ON . (Correct)
  • Conditions against the outer table should be residual to the ON .
  • Conditions against the inner table should be residual to the WHERE .

Answer : Conditions against the outer table should be residual to the WHERE . Conditions against the inner table should be residual to the ON .

What are three possible disadvantages of a Partitioned Primary Index (PPI)? (Choose three.)


Options are :

  • The table might need to be re-created if the partitioning granularity changes. (Correct)
  • It can cause more AMPs to be involved in accessing data.
  • The partitioning column might not be part of the primary index (Correct)
  • A large number of partitions could cause slow joins. (Correct)

Answer : The table might need to be re-created if the partitioning granularity changes. The partitioning column might not be part of the primary index A large number of partitions could cause slow joins.

Which statement is true for a partial covering join index?


Options are :

  • The partial covering index includes all columns of the unique index of a table containing non-covered columns. (Correct)
  • The partial covering index is used if most of the columns selected are in the index.
  • The partial covering index is used if the join index is an aggregate.
  • The partial covering index is used if the primary index of one table is in the covering index

Answer : The partial covering index includes all columns of the unique index of a table containing non-covered columns.

What are three characteristics of volatile tables? (Choose three.)


Options are :

  • They are materialized in spool space. (Correct)
  • They are restricted to a single query statement.
  • They do not have to be explicitly created in the current session with DDL.
  • Their table definitions are kept in cache. (Correct)
  • They require no Data Dictionary access or transaction locks. (Correct)

Answer : They are materialized in spool space. Their table definitions are kept in cache. They require no Data Dictionary access or transaction locks.

Which operation leads to an Exclusion Join?


Options are :

  • NOT IN (Correct)
  • NOT EXISTS
  • NOT EQUAL
  • NOT BETWEEN

Answer : NOT IN

Which two effects of statistics collection can be shown via EXPLAIN output? (Choose two.)


Options are :

  • if a new index will be used (Correct)
  • the estimated spool row count of a step (Correct)
  • the CPU and I/O usage of each step
  • the selectivity of various indexes
  • the data skew of large tables

Answer : if a new index will be used the estimated spool row count of a step

Assume the following stored procedure partial definition: CREATE PROCEDURE test_set (IN a INT, INOUT b INT, OUT c INT) Which three statements would be valid constructs within the stored procedure? (Choose three.)


Options are :

  • SET c = b + 1 (Correct)
  • SET b = b + 1 (Correct)
  • SET b = c + 1
  • SET c = b + a (Correct)

Answer : SET c = b + 1 SET b = b + 1 SET c = b + a

Which three are introduced in the ELDM? (Choose three.)


Options are :

  • foreign keys
  • maximum rows null (Correct)
  • column nullability
  • value access frequency (Correct)
  • join access rows (Correct)
  • index selection

Answer : maximum rows null value access frequency join access rows

Which two utilities can you use to export data for a report using RECORD mode from the Teradata database? (Choose two.)


Options are :

  • Teradata SQL Assistant
  • FastExport (Correct)
  • BTEQ (Correct)
  • TPump

Answer : FastExport BTEQ

What are two ways to lessen the impact of TPump on the system? (Choose two.)


Options are :

  • reduce the RATE parameter in the TPump job (Correct)
  • add a TENACITY parameter to the TPump job
  • add a SLEEP parameter to the TPump job
  • apply options in the Ferret utility
  • modify the Priority Scheduler Facility (PSF) settings (Correct)

Answer : reduce the RATE parameter in the TPump job modify the Priority Scheduler Facility (PSF) settings

When creating multiple triggers on the same table, which three conditions must exist so that an ORDER clause can guarantee the correct sequencing of the triggered actions? (Choose three.)


Options are :

  • They must have the same trigger action time (Correct)
  • They must have the same trigger type. (Correct)
  • They must share the same trigger WHEN condition.
  • They must affect the same triggered columns.
  • They must share the same trigger event. (Correct)

Answer : They must have the same trigger action time They must have the same trigger type. They must share the same trigger event.

Which two statements about USI maintenance are correct? (Choose two.)


Options are :

  • Updating the USI value of the base row requires relocating the corresponding USI row. (Correct)
  • Updating the UPI value of the base row requires relocating the corresponding USI row.
  • A separate Transient Journal entry is required for a changed USI row. (Correct)
  • Updating the PI of a base row requires relocating the corresponding USI row.

Answer : Updating the USI value of the base row requires relocating the corresponding USI row. A separate Transient Journal entry is required for a changed USI row.

Which four determine if a column or index should have statistics collected? (Choose four.)


Options are :

  • columns in the SELECT list
  • the UPI of small tables with no other statistics (Correct)
  • columns in the GROUP BY clause
  • non-unique indexes (Correct)
  • non-indexed column used for set selection (Correct)
  • non-indexed column used for join constraints (Correct)

Answer : the UPI of small tables with no other statistics non-unique indexes non-indexed column used for set selection non-indexed column used for join constraints

Which application development API provides maximum control over Teradata connectivity?


Options are :

  • ECL
  • ODBC
  • Embedded SQL
  • CLI (Correct)

Answer : CLI

Which two statements regarding rows per NUPI value are true? (Choose two.)


Options are :

  • A large table with fewer unique NUPI values than there are AMPs in the system can still have an even distribution.
  • All rows with the same NUPI value always end up in the same datablock.
  • Adding a second column to the NUPI generally reduces the number of rows per primary index value. (Correct)
  • A SET table with many rows for each NUPI value has much worse insert performance than a MULTISET table with many rows for each NUPI value. (Correct)

Answer : Adding a second column to the NUPI generally reduces the number of rows per primary index value. A SET table with many rows for each NUPI value has much worse insert performance than a MULTISET table with many rows for each NUPI value.

When can a user access a table via the primary index while it is being loaded with the TPump utility?


Options are :

  • only after the data has been loaded
  • when the user is the owner of the table
  • when the user is DBC
  • when the user issues a LOCKING FOR ACCESS (Correct)

Answer : when the user issues a LOCKING FOR ACCESS

Which three statements about a Partitioned Primary Index (PPI) are true? (Choose three.)


Options are :

  • A PPI can substitute for a secondary index. (Correct)
  • A PPI table can be altered to change the range of an existing partition.
  • A PPI can slow joins if there are many partitions. (Correct)
  • A PPI can speed up row deletion. (Correct)

Answer : A PPI can substitute for a secondary index. A PPI can slow joins if there are many partitions. A PPI can speed up row deletion.

Assuming that the department_number column is not an index, which three statements are true about the following? (Choose three.) UPDATE employee SET salary_amount = salary_amount * 1.1 WHERE department_number = 403;


Options are :

  • Read locks are applied.
  • Write locks are applied at the table level. (Correct)
  • Write locks will queue behind existing read locks. (Correct)
  • A pseudo lock is applied. (Correct)
  • Write locks are applied on all updated rows

Answer : Write locks are applied at the table level. Write locks will queue behind existing read locks. A pseudo lock is applied.

Which two utilities can you use to export data for a report using RECORD mode from the Teradata database? (Choose two.)


Options are :

  • TPump
  • Teradata SQL Assistant
  • FastExport (Correct)
  • BTEQ (Correct)

Answer : FastExport BTEQ

Why should you create a view with aggregation? (Choose two.)


Options are :

  • to minimize summary table maintenance (Correct)
  • to improve query performance
  • to minimize macro executions
  • to reduce query complexity (Correct)

Answer : to minimize summary table maintenance to reduce query complexity

Which three features can be used to improve overall system performance? (Choose three.)


Options are :

  • macros (Correct)
  • Priority Scheduler (Correct)
  • NUSI (Correct)
  • Permanent Journals
  • Transient Journals

Answer : macros Priority Scheduler NUSI

Which two statements are true of a join back with a join index? (Choose two.)


Options are :

  • The join back to the base table does not count against the join maximum if a partial covering is in effect. (Correct)
  • The join index is joined back to the base table. (Correct)
  • The join index is joined to another join index before joining back to the base table.
  • The join back to the base table happens only if the index is fully covered.

Answer : The join back to the base table does not count against the join maximum if a partial covering is in effect. The join index is joined back to the base table.

Which two can often be performed by the Optimizer as a Hash Join? (Choose two.)


Options are :

  • Inner Join (Correct)
  • Cross Join
  • Self Join (Correct)
  • Nested Join

Answer : Inner Join Self Join

Which three are join types that are available within queries? (Choose three.)


Options are :

  • Outer (Correct)
  • RowID
  • Self (Correct)
  • Product
  • Cross (Correct)
  • Hash

Answer : Outer Self Cross

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions