NR0-015 Teradata Design Architecture V2R5 Practice Test Set 1

What would be an alternative archive method for a table that takes too long to execute a full archive?


Options are :

  • BTEQ Export
  • use FastExport to unload the data and FastLoad to reload the data
  • cluster data table archive
  • cluster data table archive with a full archive of the Data Dictionary (Correct)

Answer : cluster data table archive with a full archive of the Data Dictionary

Which are two accurate statements about TPump? (Choose two.)


Options are :

  • It uses time-based checkpoints, rather than count-based. (Correct)
  • TPump cannot operate on tables with Referential Integrity specified.
  • The processing rate can be modified (Correct)
  • The larger the batch is, the faster the throughput is.

Answer : It uses time-based checkpoints, rather than count-based. The processing rate can be modified

What are two ways of adding new rows to an existing table that has a Referential Integrity constraint? (Choose two.)


Options are :

  • drop the Referential Integrity constraint and MultiLoad the new rows (Correct)
  • insert the new rows with FastLoad
  • use MultiLoad if the table contains the primary key not the foreign key
  • use TPump to insert the new rows (Correct)

Answer : drop the Referential Integrity constraint and MultiLoad the new rows use TPump to insert the new rows

When you plan the allocation of system resources in Priority Scheduler, which are two vital tasks? (Choose two.)


Options are :

  • calculating how many CPU cycles are available
  • gaining an understanding of the current workload (Correct)
  • 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

Whichof the followingmay justify the use of the Fast Export and FastLoad utilities as a backup/recovery approach?


Options are :

  • 300 large volatile tables
  • 20 small static tables (Correct)
  • 300 large static tables
  • 20 small volatile tables

Answer : 20 small static tables

Which two of the following options should you take into account when calculating the size requirements for global temporary tables? (Choose two.)


Options are :

  • the size and number of rows per use (Correct)
  • the archive policy for the system
  • the spool requirements for the user
  • the number of concurrent materializations per user (Correct)

Answer : the size and number of rows per use the number of concurrent materializations per user

Of the followingstatements,which two concerning Fallback protected tables are correct? (Choose two.)


Options are :

  • In limited cases, the Fallback copy of a row can reside on the same AMP as the primary copy.
  • Fallback protection is accomplished by grouping AMPs into cliques and distributing every row to a different AMP from the one containing the primary row.
  • If the primary AMP fails, the system can still access data on the fallback AMP. (Correct)
  • Fallback protected tables take up twice the space on your system as non-Fallback protected tables. (Correct)

Answer : If the primary AMP fails, the system can still access data on the fallback AMP. Fallback protected tables take up twice the space on your system as non-Fallback protected tables.

What is verified by the system when a row is inserted into a referencing table and foreign key columns are defined to be NOT NULL?


Options are :

  • It verifies if a row exists in the referenced table with the same values as those in the primary key of the referencing table.
  • It verifies if a row exists in the reference index subtable with a zero count.
  • It verifies if a row exists in the referenced table that is NOT NULL.
  • It verifies if a row exists in the referenced table with the same value(s) as that in the foreign key column(s). (Correct)

Answer : It verifies if a row exists in the referenced table with the same value(s) as that in the foreign key column(s).

Which three of the followingare essential Teradata RDBMS facilities for hardware fault tolerance? (Choose three.)


Options are :

  • AWS
  • RAID disk units (Correct)
  • cliques (Correct)
  • redundant power supplies (Correct)

Answer : RAID disk units cliques redundant power supplies

Which three Teradata tools use the Query Capture Database (QCD)? (Choose three.)


Options are :

  • Teradata Index Wizard (Correct)
  • Teradata Statistics Wizard (Correct)
  • DBQL
  • Teradata DQM
  • Visual EXPLAIN (Correct)

Answer : Teradata Index Wizard Teradata Statistics Wizard Visual EXPLAIN

When determining a workload definition for TDQM, which of the followingare three useful methods? (Choose three.)


Options are :

  • Priority Scheduler output containing queue length and wait times (Correct)
  • the number of Resource Partitions defined in Priority Scheduler
  • DBQL analysis of full table scans requested during the period (Correct)
  • DBQL analysis of join types for the schedule period (Correct)
  • the Statistics Wizard analysis of needed statistics for this workload

Answer : Priority Scheduler output containing queue length and wait times DBQL analysis of full table scans requested during the period DBQL analysis of join types for the schedule period

The use of global temporary tables has what two performance advantages? (Choose two.)


Options are :

  • They share data between user sessions.
  • There are no locks placed on any objects when they are materialized or dropped. (Correct)
  • Referential Integrity rules can be enforced
  • They allow reuse of intermediate results. (Correct)

Answer : There are no locks placed on any objects when they are materialized or dropped. They allow reuse of intermediate results.

The elapsed time of a particular query can shorten by performing which of the following tasks? (Choose two.)


Options are :

  • delete all Performance Groups that are not being used
  • change the Priority Scheduler settings to give a higher priority to all long-running (CPU intensive) queries
  • raise the priority of either the query or of the session in which it is running (Correct)
  • modify the weight assigned to the Allocation Group of the session used to run the query (Correct)

Answer : raise the priority of either the query or of the session in which it is running modify the weight assigned to the Allocation Group of the session used to run the query

Which three of the followingTeradata utilities are also CLI applications? (Choose three.)


Options are :

  • SQL Assistant
  • PM/API (Correct)
  • C Preprocessor2 (Correct)
  • BTEQWin (Correct)

Answer : PM/API C Preprocessor2 BTEQWin

Which three statements with reference to MultiLoad are true? (Choose three.)


Options are :

  • It can load data using a view. (Correct)
  • Each import task can do multiple insert, update, and delete functions. (Correct)
  • It maintains all secondary indexes.
  • A maximum of five tables can be loaded at a time. (Correct)

Answer : It can load data using a view. Each import task can do multiple insert, update, and delete functions. A maximum of five tables can be loaded at a time.

A join index has been created to optimize access to frequently used tables. For daily updates on these tables, MultiLoad is employed. What group of tasks can you execute to update these tables now?


Options are :

  • use the Teradata Warehouse Builder Apply Operator instead of MultiLoad
  • modify the MultiLoad script to include the join index
  • drop the join index; run the MultiLoad script; recreate the join index (Correct)
  • run the MultiLoad script unchanged (The join index will automatically be updated.)

Answer : drop the join index; run the MultiLoad script; recreate the join index

In which of the following situations should you consider using value-ordered NUSIs?


Options are :

  • when you have a high number of concurrent queries
  • when you have a high number of range constraint queries (Correct)
  • when you have a high number of joins in queries
  • when you have a high number of updated rows

Answer : when you have a high number of range constraint queries

Of the followingstatements, which two are accurate for primary keys as compared to primary indexes? (Choose two.)


Options are :

  • The first objective is to control row distribution.
  • The data values may change over time.
  • They are logical constructs which ensure uniqueness. (Correct)
  • The data values may not be null. (Correct)

Answer : They are logical constructs which ensure uniqueness. The data values may not be null.

Assume that table Big_T is a very large table, with hash distributed rows that are based on 16 unique values. Applications using Big T operates well with high parallelism on a system with 1 or 2 nodes and 8 to 16 AMPs. Which two statements are true if the system is expanded to 8 nodes and 128 AMPs? (Choose two.)


Options are :

  • . Applications accessing Big_T could be rewritten to utilize the new system configuration more efficiently.
  • The rows of Big_T will be distributed across the AMPs equally and the application will perform much better.
  • The rows of Big_T will still be distributed across 16 AMPs and applications will not perform any better. (Correct)
  • Applications accessing Big_T could perform much worse than before the system was upgraded. (Correct)

Answer : The rows of Big_T will still be distributed across 16 AMPs and applications will not perform any better. Applications accessing Big_T could perform much worse than before the system was upgraded.

You are a database administrator at Your company. Your company'sPermanent space on a Teradata machine is being used up rapidly. You want to make sure that there will be enough spool space in the system to satisfy all queries. What should you do to meet these requirements?


Options are :

  • take all available space and divide by the number of users in the system and give this amount to each user as spool space
  • give each user a spool size equal to the sum of the sizes of the tables that the user can access, the system managing the rest
  • create a database called Spool Space with all currently available space assigned to it
  • create a database equal to at least twenty percent of the total user space to reserve for spool usage (Correct)

Answer : create a database equal to at least twenty percent of the total user space to reserve for spool usage

Which is an accurate statement with regard to two queries in deadlock for the same tables?


Options are :

  • A transaction time-out mechanism aborts and restarts a transaction without notifying the user.
  • A 'hung transaction' detection mechanism detects and aborts hung transactions.
  • A detection mechanism will abort the transaction that has completed the least work.
  • A detection mechanism will abort the youngest transaction. (Correct)

Answer : A detection mechanism will abort the youngest transaction.

Which of the followingis a true statement on the subject of archiving tables with join indexes?


Options are :

  • Since the Teradata ARC utility cannot be used on a join index, you must archive the underlying tables and manually rebuild all the join indexes when you need to restore. (Correct)
  • The Teradata ARC utility is sufficient if the join indexes are archived as frequently as the data is changed.
  • The Teradata ARC utility cannot be used on a join index itself but it automatically recreates and repopulates all the join indexes as long as you have saved the join index definitions within a Data Dictionary archive.
  • The Teradata ARC utility is sufficient if the join indexes are archived at least on a weekly basis.

Answer : Since the Teradata ARC utility cannot be used on a join index, you must archive the underlying tables and manually rebuild all the join indexes when you need to restore.

The amount of raw data generated by a single INSERT EXPLAIN in the Query Capture Database is affected by whichthree elements? (Choose three.)


Options are :

  • average size of the tables in the query
  • size of the EXPLAIN statement output (Correct)
  • number of tables and views in the query (Correct)
  • size of the SQL statement (Correct)

Answer : size of the EXPLAIN statement output number of tables and views in the query size of the SQL statement

Of the followingsoftware components,which two runs on a Teradata channel-attached client? (Choose two.)


Options are :

  • TPA
  • TGTW
  • TDP (Correct)
  • PE
  • CLI (Correct)

Answer : TDP CLI

Which three extremely effective join plans is the Optimizer allowed to choose as a result of joining tables on their primary index? (Choose three.)


Options are :

  • Merge Join (Correct)
  • Nested Join (Correct)
  • Hash Join (Correct)
  • Product Join
  • Cross Join

Answer : Merge Join Nested Join Hash Join

Sufficient data protection can be provided by a backup of your entire database using the ARC utility, in which three of the followingcircumstances? (Choose three.)


Options are :

  • when there are accidentally dropped tables, views, or macros (Correct)
  • when there is a loss of data on an AMP for non-Fallback tables (Correct)
  • when global temporary tables are in use at the time of the archive operation
  • when there is a loss of the data on multiple AMPs within the same cluster (Correct)

Answer : when there are accidentally dropped tables, views, or macros when there is a loss of data on an AMP for non-Fallback tables when there is a loss of the data on multiple AMPs within the same cluster

Your company'sdata warehouse environment has critical data that needs to be available 24 hours a day, 7 days a week. How can continual availability be ensured?


Options are :

  • develop and implement a strategy for simultaneously or near simultaneous loading of critical data to an offsite dual system (Correct)
  • send archived data to the offsite system to be loaded on a regular basis
  • keep a dual system offsite and restore the data as needed
  • archive data and keep a copy of the archived data offsite

Answer : develop and implement a strategy for simultaneously or near simultaneous loading of critical data to an offsite dual system

Why is the Partitioned Primary Index (PPI) regarded on a table for two purposes? (Choose two.)


Options are :

  • to reduce the amount of I/O needed to process queries (Correct)
  • to reduce response time by placing all data in the same partition on the same AMP
  • to reduce the number of columns in a table to those needed by most queries
  • to eliminate ranges of data values from a query and thereby reduce scan time (Correct)

Answer : to reduce the amount of I/O needed to process queries to eliminate ranges of data values from a query and thereby reduce scan time

When is a fully covering join index advantageous to a query?


Options are :

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

Answer : when relatively few columns are in the result and join or selection criteria includes values other than the base tables' primary index(es)

Which are three factual statements concerning the creation and use of indexes? (Choose three.)


Options are :

  • A Unique Secondary Index entry might not reside on the same AMP as the base table row. (Correct)
  • Hash indexes are created in order to allow the Optimizer to choose hash joins.
  • All secondary, hash, and join indexes are stored in subtables, so they require additional storage space. (Correct)
  • Index subtables are updated each time an indexed column value in the base table is updated or deleted. (Correct)

Answer : A Unique Secondary Index entry might not reside on the same AMP as the base table row. All secondary, hash, and join indexes are stored in subtables, so they require additional storage space. Index subtables are updated each time an indexed column value in the base table is updated or deleted.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions