NR0-012 Teradata Physical Implementation V2R5 Practice Exam Set 1

Which two expressions may be used to define Partitions in the CREATE TABLE statement? (Choose two)


Options are :

  • PARTITION BY expressions (Correct)
  • ALWAYS and AS DEFAULT expression
  • CAS and RANGE expressions
  • CASE_N and RANGE_N expressions (Correct)

Answer : PARTITION BY expressions CASE_N and RANGE_N expressions

Which of the following are two factual statements regarding parallel steps? (Choose two)


Options are :

  • They are steps whose output may be used in a later statement (Correct)
  • They are sequentially numbered steps that may execute simultaneously (Correct)
  • They represent combined identical steps for two more SQL statements within the request parcel.
  • They are seen in the creation of a spool file, which is subsequently used in more than one processing steps.

Answer : They are steps whose output may be used in a later statement They are sequentially numbered steps that may execute simultaneously

What is guaranteed by using pseudo table locks?


Options are :

  • Two users will not deadlock on a table level lock request. (Correct)
  • Two usersill not get conflicting row locks on a table.
  • One user will be able to have locks on two tables simultaneously
  • Two users will not need to share virtual tables.
  • Two users will be able to have simultaneous and conflicting locks on a table.

Answer : Two users will not deadlock on a table level lock request.

What best describes primary index access in a table with a Partitioned Primary Index?


Options are :

  • The partition value is hashed and the data row is retrieved directly.
  • The primary index column values are hashed and each partition which hasn't been eliminated in the predicate is scanned for therowid. (Correct)
  • The primary index column values are hashed and the data row is retrieved directly
  • The partition value is hashed and each partition which hasn't been eliminated in the predicate is scanned for therowid.

Answer : The primary index column values are hashed and each partition which hasn't been eliminated in the predicate is scanned for therowid.

You can reclaim all possible cylinders on the system with which operation?


Options are :

  • PACKKDISK (Correct)
  • SHOWSPAC
  • DEFRAG
  • QRYCONFIG
  • CYLPACK

Answer : PACKKDISK

Which three of the following statements regarding identity columns are true? (Choose three)


Options are :

  • They are supported byTpump (Correct)
  • They guarantee sequential numbering of inserted rows.
  • They simplify the initial port from other database that use generated keys. (Correct)
  • They provide even row distribution when used as a primary index (Correct)

Answer : They are supported byTpump They simplify the initial port from other database that use generated keys. They provide even row distribution when used as a primary index

Visual EXPLAIN is best described by which three of the following statements? (Choose three)


Options are :

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

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

As part of an assignment for Your Company, you run a query against a three year history table.SELECT * FROM Orders WHERE 0_orderdat BETWEEN ?003-07-01[] AND ?003-07-07? Which three statements regarding a NUSI on 0_orderdate are FALSE?


Options are :

  • A value-ordered NUSI should perform better than a hash-ordered NUSI. (Correct)
  • A hash-ordered NUSI should perform better than a value-ordered NUSI
  • Since a full table scan is required anyway, a NUSI will not improve performance
  • The value-ordered NUSI rows are stored in row hash order.

Answer : A value-ordered NUSI should perform better than a hash-ordered NUSI.

When you implement Referential Integrity, which two definitions are required on the referenced columns? (Choose two)


Options are :

  • Not Null (Correct)
  • Indexed
  • Unique (Correct)
  • Nullable

Answer : Not Null Unique

Which of the following are two goals of the hashing algorithm? (Choose two)


Options are :

  • to produce random and unique row hash values
  • to compute a variable row hash depending on the input column value.
  • to produce a unique rowed
  • to compute a 32 bit row hash (Correct)
  • to produce random and consistent row hash values (Correct)

Answer : to compute a 32 bit row hash to produce random and consistent row hash values

Of the following, which are three data demographic elements? (Choose three)


Options are :

  • number of constraints
  • number of distinct values (Correct)
  • number of nulls (Correct)
  • number of rows per value (Correct)

Answer : number of distinct values number of nulls number of rows per value

When the join constraints are ORed, which join method will probably be used?


Options are :

  • Inclusion Merge Join
  • Exclusion Merge Join
  • Product Join (Correct)
  • Inclusion Hash Join

Answer : Product Join

If column enum is a primary index for both the employee and employee_phone table, which join plan would be seen in the EXPLAN output for the following query? SELECT * FROM employee,employee phoneWHER employee.enum=employee phone.enum;


Options are :

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

Answer : Merge Join

Which two of the following statements are accurate with regards to data distribution?(Choose two)


Options are :

  • In a partitioned table the partition identifier is part of the row identifier (Correct)
  • Secondary indexes affect the distribution of rows across AMPs.
  • Rows from a partitioned table are stored in partitions on each AMP and ordered by hash (Correct)
  • Both primary and secondary indexes may be created and dropped dynamically E. Both primary and secondary indexes usesubtables for access to data rows

Answer : In a partitioned table the partition identifier is part of the row identifier Rows from a partitioned table are stored in partitions on each AMP and ordered by hash

Which three of the following are recovered from an AMPs virtual disk (vdisk)? (Choose three)


Options are :

  • hash maps
  • spool (Correct)
  • master index
  • tables (Correct)
  • indexsubtables (Correct)
  • free cylinder list

Answer : spool tables indexsubtables

What reason describes why Hash joins generally operate better than Merge Joins?


Options are :

  • because neither table needs to be sorted
  • because the large table may be cached
  • because the large table does not need to be sorted (Correct)
  • because the small table is always cached

Answer : because the large table does not need to be sorted

Which join method DOES NOT require all AMPs?


Options are :

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

Answer : Nested Joins

What redistribution action is necessary to prepare for a Merge or Exclusion Merge Join,when both join columns is not a primary index, and the join condition operator is either equal ( = ) or not equal ( < >)?


Options are :

  • Qualified rows from both tables must be re-hashed, redistributed/duplicated, and sorted (Correct)
  • All rows from both tables must be re-hashed, redistributed/duplicated, and sorted
  • Qualified rows from one table must be re-hashed redistributed/duplicated and sorted
  • All rows from both tables must be stored and joined locally

Answer : Qualified rows from both tables must be re-hashed, redistributed/duplicated, and sorted

Which three are possible join types if you see the following phrase in the EXPLAIN of a join process? (Choose three) "spool... which is duplicated on all AMPs."


Options are :

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

Answer : Product Join Hash Join Merge Join

In a CREATE TABLE statement, which are two options that overrides system level specifications for that tables? (Choose two)


Options are :

  • ON COMMIT DELETE ROWS
  • DATABLOCKSIZE (Correct)
  • COMPRESS
  • FREESPACE (Correct)
  • MAXCOLUMNS

Answer : DATABLOCKSIZE FREESPACE

How is a submitted query affected when a deadlock occurs?


Options are :

  • Query waits for deadlock to resolve.
  • Oldest query is aborted.
  • Dispatcher identifies which query to hold and waits for completion of deadlocking query.
  • Youngest query is aborted. (Correct)

Answer : Youngest query is aborted.

Pseudo table lock is designed to enable ___________.


Options are :

  • locking of the virtual tables accessed by the user
  • locking of the global temporary tables
  • reserving of a volatile temporary table
  • sequenced locking of a resource (Correct)

Answer : sequenced locking of a resource

With reference to changing the default lock assignments using the locking modifier which two statements are true? (Choose two)


Options are :

  • Any lock may be upgraded. (Correct)
  • A WRITE lock may be downgraded to a READ lock.
  • Any lock may be downgraded
  • A READ lock may be downgraded to an ACCESS lock. (Correct)

Answer : Any lock may be upgraded. A READ lock may be downgraded to an ACCESS lock.

Which of the following are three features of Volatile table? (Choose three)


Options are :

  • Spool space is used to materialize the table. (Correct)
  • Rows are lost following a system restart (Correct)
  • Temporary space is used to materialize the table.
  • Rows are deleted at session end. (Correct)
  • Definitions are stored in the data dictionary

Answer : Spool space is used to materialize the table. Rows are lost following a system restart Rows are deleted at session end.

You must decide whether to calculate or store stand-alone derived data, whenever you have it. Which of the following are two demographics you should base your decision on? (Choose two)


Options are :

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

Answer : access frequency column data value volatility

What are three purposes of Teradata STATISTICS (Choose three)


Options are :

  • Helps the Optimizer determine optimal join types and sequences (Correct)
  • used to estimate the cardinalities of intermediate spool files. (Correct)
  • Helps the Optimizer determine how well the data is distributed across the AMPs.
  • enables the Optimizer to evaluate NUSI bit mapping (Correct)

Answer : Helps the Optimizer determine optimal join types and sequences used to estimate the cardinalities of intermediate spool files. enables the Optimizer to evaluate NUSI bit mapping

When migrating a Non-Partitioned Primary index table (NPPI) to a Partitioned Primary Index table (PPI), what added space per row is required?


Options are :

  • three bytes
  • four bytes
  • two bytes (Correct)
  • one byte

Answer : two bytes

When the join condition is an inequality, which join type would you use?


Options are :

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

Answer : Product Join

What can a referencing column not reference?


Options are :

  • Column defined with the unique and not null constraints
  • primary key
  • NUPI defined with not null (Correct)
  • Unique secondary index defined with not null

Answer : NUPI defined with not null

Which three of the following are applicable for the ALTER TABLE command? (Choose three)


Options are :

  • MODIFY CONSTRAINT for a named CHECK constraint (Correct)
  • ADD compression to an existing column
  • ADD a new column with multi-value compression (Correct)
  • ADD a compressible value to a column with multi-value compression
  • ADD columns up to 2048 columns (Correct)

Answer : MODIFY CONSTRAINT for a named CHECK constraint ADD a new column with multi-value compression ADD columns up to 2048 columns

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions