NR0-012 Teradata Physical Implementation V2R5 Practice Exam Set 2

When electing to demoralize a table, what should you take into consideration?


Options are :

  • number of users
  • cardinality
  • connectivity
  • volatility (Correct)

Answer : volatility

When can two or more SQL statements share spool files?


Options are :

  • when statements are from the same request parcel. (Correct)
  • When statements are executed during the same session
  • when statements are within a stored procedure
  • when spool is on the same node

Answer : when statements are from the same request parcel.

TE0-121 Teradata Basics Practice Test Set 4

Whether parallel steps have been generated in an EXPLAIN output is defines by which of the following?


Options are :

  • the phrase "we execute parallel steps on each AMP"
  • the phrase "we execute the following steps in parallel" (Correct)
  • the phrase "we execute the following steps as an all-AMPs RETRIEVE"
  • the phrase "we execute the following steps in unison"

Answer : the phrase "we execute the following steps in parallel"

Which of the following is a feature of a cylinder split?


Options are :

  • Data blocks are moved from a full cylinder to an empty cylinder on a different AMP.
  • Data blocks are moved from a full cylinder to an empty cylinder on the same AMP. (Correct)
  • The data blocks in a cylinder are split to defragment the space.
  • A new cylinder is divided into two and one half is put in overflow

Answer : Data blocks are moved from a full cylinder to an empty cylinder on the same AMP.

What does the Optimizer estimate when you want to equi-join a Partitioned Primary Index (PPI) table to a Non-Partitioned Primary Index (NPPI) table, or to another PPI table defined with a different partitioning expression? (Choose three)


Options are :

  • Spooling one or both PPIs into a NPPI spool and joining these using a conventional merge join. (Correct)
  • Re-partitioning, if possible, one of the two tables into a PPI spool with the same partitioning as the PPI table in preparation for arowkey-based. (Correct)
  • Re-partitioning and redistributing both tables to have matching partition definition in preparation for arowkey-based Merge Join.
  • using a "sliding window"join of the two tables without re-partitioning either table. (Correct)

Answer : Spooling one or both PPIs into a NPPI spool and joining these using a conventional merge join. Re-partitioning, if possible, one of the two tables into a PPI spool with the same partitioning as the PPI table in preparation for arowkey-based. using a "sliding window"join of the two tables without re-partitioning either table.

TE0-141 14 Teradata Basics Certified Practice Test Set 1

Every time you have stand-alone derived data, you have to decide to either calculate it, or store it. Which two demographics should be the basis of your decision? (Choose two)


Options are :

  • number of sources involved
  • column data value volatility (Correct)
  • access frequency (Correct)
  • access column

Answer : column data value volatility access frequency

The Index Wizard performs which three of the following functions? (Choose three)


Options are :

  • recommends adding indexes based on specified workloads (Correct)
  • automatically creates DDL statements for recommended indexes (Correct)
  • recommends dropping the indexes that may not be necessary (Correct)
  • recommends Partitioned Primary Indexes based on specified workloads.
  • automatically drops existing indexes based on the workload

Answer : recommends adding indexes based on specified workloads automatically creates DDL statements for recommended indexes recommends dropping the indexes that may not be necessary

TE0-121 Teradata Basics Practice Test Set 2

Of the following, which are two Referential Integrity options accessible in the ALTER TABLE command? (Choose two)


Options are :

  • ON COMMIT DELET ROWS
  • DROP INCONSISTENT REFERENCE (Correct)
  • WITH CHECK option (Correct)
  • DUAL BEFORE JOURNAL
  • MODIFY PRIMARY INDEX

Answer : DROP INCONSISTENT REFERENCE WITH CHECK option

Which action by the system is enabled by the use of the following SQL option? Locking row no wait


Options are :

  • immediate access to the row
  • abort holding lock on a wait
  • abort requesting task on a wait (Correct)
  • down grade to an access lock

Answer : abort requesting task on a wait

Which join method is the Optimizer employing when it elects to cache one table and join it by hash to an unsorted spool?


Options are :

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

Answer : Hash Join

TE0-141 14 Teradata Basics Certified Practice Test Set 4

Which statement is true if the following phrase is included in an EXPLAIN output? "by way of a traversal of index #n extracting row ids only"


Options are :

  • A spool file is built from rowed values found in a secondary index (Index#n). (Correct)
  • Rowid values are accessed in memory without the need for a spool file.
  • A spool file is built from therowhash found in the cylinder index.
  • A temporary file is accessed with rowed values found in a secondary Index (index #n).

Answer : A spool file is built from rowed values found in a secondary index (Index#n).

Which two of the following would make the joining of a Partitioned Primary Index (PPI) table to a Non-Partitioned Primary Index (NPPI) table more efficient? (Choose two)


Options are :

  • choosing primary index columns that minimize row redistributions (Correct)
  • designing PPI tables to have fewer rather than more rows per partition
  • designing the partitions so that the most common WHERE conditions eliminate the most partitions. (Correct)
  • designing the partitions so that each partition has the same number of rows

Answer : choosing primary index columns that minimize row redistributions designing the partitions so that the most common WHERE conditions eliminate the most partitions.

Which two of the following are conditions for changing Partition definitions using an ALTER TABLE statement? (Choose two)


Options are :

  • Is empty and not in use (Correct)
  • Is populated, but no updates are running
  • Is populated and the ALTER TABLE statements are adding partitions to either the beginning or the end of the table (Correct)
  • Is defined with a NUPI

Answer : Is empty and not in use Is populated and the ALTER TABLE statements are adding partitions to either the beginning or the end of the table

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 2

Of the following, which are three factual statements regarding Visual EXPLAIN? (Choose three)


Options are :

  • identifies what the Optimizer chooses and why
  • may be used on any SQL statement, including EXPLAIN.
  • may be used on any SQL statement, except EXPLAIN (Correct)
  • identifies what the Optimizer chooses not why (Correct)
  • translates Optimizer output (the parse tree) into icons (Correct)

Answer : may be used on any SQL statement, except EXPLAIN identifies what the Optimizer chooses not why translates Optimizer output (the parse tree) into icons

Considering the nested view situation where ViewY references TableA, which three privileges are required? (Choose three)?User1 accesses ViewY?User2 owns ViewY?Database VMDB owns ViewX ?Database DBX owns TableA


Options are :

  • User1 has privileges on aViewY (Correct)
  • User2 has privileges onViewX WITH GRANT option. (Correct)
  • User1 has privileges onTableA.
  • Database VMDB has privileges onTableA WITH GRANT option. (Correct)

Answer : User1 has privileges on aViewY User2 has privileges onViewX WITH GRANT option. Database VMDB has privileges onTableA WITH GRANT option.

All possible cylinders on the system can be retrieved using which operation?


Options are :

  • SHOWSPACE
  • CYLPACK
  • QRYCINFIG
  • DEFRAG
  • PACKDISK (Correct)

Answer : PACKDISK

TE0-121 Teradata Basics Practice Test Set 1

Which three menu options are accessible when you use Visual EXPLAIN? (Choose three)


Options are :

  • Show Connectors (Correct)
  • Open Plan from Database (Correct)
  • Explain Textual Difference (Correct)
  • Replace Execution Plans

Answer : Show Connectors Open Plan from Database Explain Textual Difference

In the table definition, what does the use of the free space parameter allow you to reserve.


Options are :

  • space within a cylinder during loading (Correct)
  • cylinders for spool to be used by the users
  • space within block during loading
  • cylinder for further expansion of tables

Answer : space within a cylinder during loading

A Partitioned Primary Index would be defined during which stage of modeling design?


Options are :

  • Physical Data Modeling (Correct)
  • Business information Modeling
  • Entity-Relationship Modeling
  • Logical Data Modeling

Answer : Physical Data Modeling

TE0-121 12 Teradata Basics Certification Practical Exam Set 1

Which of the following are two types of columns that is supported with compression? (Choose two)


Options are :

  • date (Correct)
  • primary index
  • dentity
  • variable length
  • integer (Correct)

Answer : date integer

When collected statistics are available the Optimizer can estimate costs more accurately, and build more aggressive join plans. What expression would suggest that statistics are absent in an EXPLAIN?


Options are :

  • A table is duplicated on all AMPS...
  • High confidence...
  • Statistics are needed...
  • No confidence... (Correct)

Answer : No confidence...

When electing to denormalize a table, which are two factors you should think about? (Choose two)


Options are :

  • connectivity
  • volatility (Correct)
  • performance (Correct)
  • cardinality

Answer : volatility performance

TE0-121 12 Teradata Basics Certification Practical Exam Set 3

To determine space requirements for NUSIs, what are three factors that is utilized? (Choose three)


Options are :

  • Number of NULL values
  • Index value size (Correct)
  • Fallback (Correct)
  • Cardinality (Correct)
  • Number of nodes in the system

Answer : Index value size Fallback Cardinality

When dynamic AMP Sampling is used, which three of the following are utilized to work out the number of rows in a table? (Choose three)


Options are :

  • number of disk arrays in the configuration
  • average number of rows per block in the sampled cylinder (Correct)
  • number of data blocks in the sampled cylinder (Correct)
  • number of nodes in the configuration
  • number of AMPs in the configuration (Correct)

Answer : average number of rows per block in the sampled cylinder number of data blocks in the sampled cylinder number of AMPs in the configuration

Why is the Product Joins chosen by Optimizer for all Join conditions except equality?


Options are :

  • They require less setup (redistribution and sorts)
  • Duplicating a small table is usually more efficient than hashing, redistributing, and sorting tables on the hash of the join condition
  • They are more efficient
  • Hash values cannot be used in a greater than (>) or less than (<) compare. (Correct)

Answer : Hash values cannot be used in a greater than (>) or less than (<) compare.

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 4

Why is it advantageous for a query to access a view that performs aggregation, instead of accessing a table that stores the aggregations?


Options are :

  • The aggregation may be used by other queries
  • The aggregation is always current with the data in the database (Correct)
  • The aggregation is calculated only once and stored in a derived table
  • The aggregation is independent of the underlying tables

Answer : The aggregation is always current with the data in the database

With regard to join plans, which of the following are three accurate statements? (Choose three)


Options are :

  • A Merge Join requires that the data from the tables be sorted before performing the join operation. (Correct)
  • A Nested joinIs only used when the user specifies a constant value in a WHERE clause (Correct)
  • A Merge join retrieves rows from two tables and requires them to be on the same AMP based on the row hash of the columns involved in the join. (Correct)
  • A Hash join requires that the data from both tables be stored before performing the join operation

Answer : A Merge Join requires that the data from the tables be sorted before performing the join operation. A Nested joinIs only used when the user specifies a constant value in a WHERE clause A Merge join retrieves rows from two tables and requires them to be on the same AMP based on the row hash of the columns involved in the join.

TE0-121 12 Teradata Basics Certification Practical Exam Set 3

What shows that a query contains a join or a subquery in an EXPLAIN output?


Options are :

  • thesubquery step that is identified
  • the number ofretrieve steps that are generated
  • the number of parallel steps that are generated
  • the type of join step that is identified (Correct)

Answer : the type of join step that is identified

Which two of the following statements concerning dynamic AMP Sampling are accurate? (Choose two)


Options are :

  • The potential for error introduced from a large table is much higher than the estimates gained by the full-table statistics gathered using the Optimizer from of COLLECT STATISTICS.
  • An AMP is selected to represent an estimation of population demographics (Correct)
  • A random data block from a random cylinder is selected and the number of rows it containsare counted. (Correct)
  • The Optimizer understands the reliability of the information it collects using a random AMP and it can be more aggressive in its pursuit of optimal access and join strategies.

Answer : An AMP is selected to represent an estimation of population demographics A random data block from a random cylinder is selected and the number of rows it containsare counted.

Which of the following are two reasons why you would apply Referential integrity constraints? (Choose two)


Options are :

  • requires more application programming to enforce constraints
  • provides the Optimizer with a mean for devising better query plans (Correct)
  • ensures data integrity and consistency between Primary and Foreign key columns (Correct)
  • allows referencing values to exist without a corresponding referenced value

Answer : provides the Optimizer with a mean for devising better query plans ensures data integrity and consistency between Primary and Foreign key columns

TE0-141 14 Teradata Basics Certified Practice Test Set 4

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions