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

Which type of queries benefit from a sparse index?


Options are :

  • queries that often redistribute a large table
  • queries that repeatedly use the same subset of the table (Correct)
  • queries that often eliminate a large percentage of the rows of a large table using a superset of the table
  • queries that rarely redistribute a large table in complex join processing

Answer : queries that repeatedly use the same subset of the table

The Priority Scheduler may limit CPU allocation percentages on which three levels? (Choose three.)


Options are :

  • allocation group (Correct)
  • performance group
  • resource partition (Correct)
  • system (Correct)
  • AMP

Answer : allocation group resource partition system

Which two apply when defining a table using the multi-value compression capability? (Choose two.)


Options are :

  • cannot compress a column that is a component of a secondary index
  • has a maximum of 32 values per column
  • has a maximum length of 128 bytes for a BYTE column
  • has a maximum length of 255 characters for a CHARACTER column (Correct)
  • cannot compress a column that is a component of the primary index (Correct)

Answer : has a maximum length of 255 characters for a CHARACTER column cannot compress a column that is a component of the primary index

Which three statements are true about partitioned primary indexes (PPIs)? (Choose three.)


Options are :

  • They cannot be defined on a join index. (Correct)
  • They are beneficial in queries specifying range constraints. (Correct)
  • They can only be defined on primary index columns.
  • They reduce the number of rows processed by using partition elimination. (Correct)
  • They are used to enhance performance for small tables.

Answer : They cannot be defined on a join index. They are beneficial in queries specifying range constraints. They reduce the number of rows processed by using partition elimination.

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)
  • change the priority of the query
  • delay the query (Correct)
  • log the query in the TDQM log file

Answer : reject the query delay the query

Under which two conditions should AMP worker tasks be reserved? (Choose two.)


Options are :

  • when short-running, high-priority queries need to be expedited (Correct)
  • when data loading and maintenance operations are running too slowly
  • when CPU-bound complex strategic queries are running too long
  • when specific allocation groups must be restricted
  • when tasks in high-priority allocation groups are delayed waiting for AMP worker tasks (Correct)

Answer : when short-running, high-priority queries need to be expedited when tasks in high-priority allocation groups are delayed waiting for AMP worker tasks

What are two characteristics of an aggregate join index? (Choose two.)


Options are :

  • Aggregated columns must be part of the primary index.
  • It must contain more than one table reference.
  • COUNT is automatically present if SUM is used. (Correct)
  • It can be created for a global temporary table.
  • It automatically reflects changes in the underlying detail data. (Correct)

Answer : COUNT is automatically present if SUM is used. It automatically reflects changes in the underlying detail data.

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


Options are :

  • improves full-table joins of tables with the same primary index
  • reduces the need for secondary indexing (Correct)
  • reduces full-table scans (Correct)
  • improves disk space utilization

Answer : reduces the need for secondary indexing reduces full-table scans

You have a large number of queries that access a very large table. These queries look at the same subset of data of this large table. Which index should you consider implementing to reduce the queries' access time?


Options are :

  • global
  • aggregate join
  • hash join
  • sparse join (Correct)

Answer : sparse join

What are two advantages that stored procedures have over other types of application implementation? (Choose two.)


Options are :

  • definition and enforcement of business rules on the server (Correct)
  • reduction of intra-request processing on the server side (Correct)
  • enhancement of server-side parallelism
  • reduction of network traffic between client and server

Answer : definition and enforcement of business rules on the server reduction of intra-request processing on the server side

A table in an Active Data Warehouse is being loaded by a MultiLoad job. This load job may benefit from a partitioned primary index (PPI) on the table if the partition being loaded is _____.


Options are :

  • partitioned by CASE_N instead of CASE
  • empty (Correct)
  • partitioned by a column that is not part of the primary index
  • on a single AMP

Answer : empty

What is the benefit of specifying two column lists in the select list of a CREATE JOIN INDEX statement for a single-table join index?


Options are :

  • The second list of columns defines the columns that are the primary index of the base table, providing efficient access back to the base table.
  • . It allows repetition of the second set of columns for a first set of columns in a physical row of the join index, leading to a smaller index. (Correct)
  • It vertically partitions the index into two subtables, providing efficient access if only the columns in the first set of columns are referenced.
  • The second list of columns defines the columns that are the primary key of the base table, providing efficient access back to the base table.

Answer : . It allows repetition of the second set of columns for a first set of columns in a physical row of the join index, leading to a smaller index.

Which three capabilities are available to the Teradata Dynamic Query Manager (TDQM) Administrator for managing scheduled requests? (Choose three.)


Options are :

  • delete a job or request from the queue (Correct)
  • modify results
  • drop results (Correct)
  • modify scheduled request text
  • modify a job start time (Correct)

Answer : delete a job or request from the queue drop results modify a job start time

How can the load utilities benefit from partitioned primary indexes (PPIs)?


Options are :

  • Load utilities are not impacted by having a PPI on a table.
  • Using the TPump PARTITION option allows you to explicitly specify the target partition, which results in lower utility CPU consumption.
  • Fewer TPump sessions are required if the data being updated reside in a single partition.
  • Inserting into a single partition may result in a higher number of rows inserted per data block, resulting in fewer disk reads and writes (Correct)

Answer : Inserting into a single partition may result in a higher number of rows inserted per data block, resulting in fewer disk reads and writes

If columns are joined using equality joins, under which two conditions are direct merge joins possible? (Choose two.)


Options are :

  • when two partitioned primary index (PPI) tables have the same primary index and identical partitioning expressions (Correct)
  • when two non-partitioned primary index (NPPI) tables have the same primary index (Correct)
  • when two partitioned primary index (PPI) tables have the same primary index and have different partitioning expressions
  • when a non-partitioned primary index (NPPI) table and a partitioned primary index (PPI) table have the same primary index

Answer : when two partitioned primary index (PPI) tables have the same primary index and identical partitioning expressions when two non-partitioned primary index (NPPI) tables have the same primary index

Under which two conditions do changed profile parameters take effect for the profile members? (Choose two.)


Options are :

  • after an update to DBC.AccessRights
  • starting with the next transaction
  • immediately upon assignment of spool and temporary space limits (Correct)
  • upon the next logon (Correct)

Answer : immediately upon assignment of spool and temporary space limits upon the next logon

How do a join index and a hash index differ?


Options are :

  • A join index can be FALLBACK protected.
  • A join index can have a secondary index. (Correct)
  • A join index can have statistics collected.
  • A join index is stored in a distinct table.

Answer : A join index can have a secondary index.

When you use the Database Query Log (DBQL), you may begin query logging on _____. (Choose three.)


Options are :

  • all users (Correct)
  • one or more roles
  • one or more accounts (Correct)
  • one or more users (Correct)
  • one or more profiles

Answer : all users one or more accounts one or more users

When would a query benefit from a fully covering join index?


Options are :

  • when the base table is small enough to fit in cache
  • when there is an unpredictable selection of columns from the base table
  • when join or selection criteria include values other than the base table's primary index (Correct)
  • when the result set is produced through either join or selection criteria on the base table's primary index

Answer : when join or selection criteria include values other than the base table's primary index

Using the Teradata Statistics Wizard, workloads may be _____. (Choose two.)


Options are :

  • imported from another system (Correct)
  • defined from Teradata Dynamic Query Manager (TDQM) statements
  • defined from the Workload Monitor
  • defined from Query Capture Database (QCD) statements (Correct)

Answer : imported from another system defined from Query Capture Database (QCD) statements

Which two are benefits of a single-table join index? (Choose two.)


Options are :

  • It improves join processing of large tables. (Correct)
  • It requires less space than a non-unique secondary index (NUSI).
  • It allows table maintenance through MultiLoads.
  • It can be hashed to the same AMP as the join table. (Correct)

Answer : It improves join processing of large tables. It can be hashed to the same AMP as the join table.

When using the Database Query Log (DBQL) facility, which two statements are true? (Choose two.)


Options are :

  • The results of DBQL logging are cached and periodically stored in sequential files.
  • The DBQL facility must be enabled by a DBSControl parameter.
  • The results of DBQL logging are cached and periodically written to dictionary tables. (Correct)
  • Query logging is initiated and terminated by means of special SQL statements. (Correct)

Answer : The results of DBQL logging are cached and periodically written to dictionary tables. Query logging is initiated and terminated by means of special SQL statements.

What are three stages of data warehouse evolution? (Choose three.)


Options are :

  • reporting (Correct)
  • integrating
  • analyzing (Correct)
  • operationalizing (Correct)
  • transforming

Answer : reporting analyzing operationalizing

What can be used to determine the views directly and indirectly referenced by view DBView.A?


Options are :

  • SELECT * FROM DBC.VIEW WHERE ViewName = 'A' AND DBName = 'DBView';
  • SHOW SELECT * FROM DBView.A; (Correct)
  • SHOW REFERENCED VIEWS DBView.A;
  • SHOW VIEW DBView.A;

Answer : SHOW SELECT * FROM DBView.A;

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


Options are :

  • The join index is joined to another join index before joining back to the base table.
  • The join back is to the base table (Correct)
  • The join index must be joined back to the base table before any other joins.
  • The join back is counted against the maximum number of tables joined per query. (Correct)
  • The join back happens only if the index is fully covering.

Answer : The join back is to the base table The join back is counted against the maximum number of tables joined per query.

In which SQL command is the SQL clause WHEN NOT MATCHED found?


Options are :

  • MERGE (Correct)
  • UPSERT
  • UPDATE
  • CASE

Answer : MERGE

Which two statements are true concerning parameters within a stored procedure? (Choose two.)


Options are :

  • Parameters and their attributes are always stored in the Data Dictionary (Correct)
  • Parameters can be used to build a dynamic SQL statement. (Correct)
  • . Parameters can include status variables such as ACTIVITY_COUNT.
  • Parameters can be altered using the FORMAT clause.

Answer : Parameters and their attributes are always stored in the Data Dictionary Parameters can be used to build a dynamic SQL statement.

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 be used for join elimination. (Correct)
  • It is more costly to use than traditional RI.
  • It can only be implemented at the column level.

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

Referential integrity (RI) is defined on a table with the WITH CHECK OPTION. An implicit transaction with multiple inserts is submitted against the table. What happens when one insert violates the RI?


Options are :

  • All rows except the one violator are inserted and a message is returned.
  • An error table is created with a copy of the offending column(s) that violated the RI.
  • All rows are inserted and a message is returned.
  • All the rows inserted are rolled back and an abort message is issued. (Correct)

Answer : All the rows inserted are rolled back and an abort message is issued.

Which two statements are true about the Database Query Log (DBQL) SUMMARY logging option? (Choose two.)


Options are :

  • Default rows are written to the DBQLogTbl for each query within the scope of the BEGIN LOGGING statement.
  • Up to 10000 characters of SQL text may be captured.
  • Only query counts are collected and logged (Correct)
  • Four elapsed time count intervals are captured, based on user-supplied thresholds. (Correct)

Answer : Only query counts are collected and logged Four elapsed time count intervals are captured, based on user-supplied thresholds.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions