NR0-012 Teradata Physical Implementation V2R5 Practice Exam Set 4

To upgrade a table read lock to an exclusive lock, which privilege is needed?


Options are :

  • DROP table (Correct)
  • RESTORE table
  • CREATE table
  • UPDATE table

Answer : DROP table

On which type of table may compression be specified for the columns?


Options are :

  • global temporary
  • multiset (Correct)
  • volatile
  • derived

Answer : multiset

A USI has which three advantages? (Choose three)


Options are :

  • duplicate row checking is not necessary (Correct)
  • can eliminate a full table scan (Correct)
  • cannot insert rows with duplicate USI values (Correct)
  • saves disk space
  • is used to cover queries

Answer : duplicate row checking is not necessary can eliminate a full table scan cannot insert rows with duplicate USI values

Spool files are required for which three join methods? (Choose three)


Options are :

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

Answer : Merge Join Product Join Exclusion Merge Join

In the CREATETABLE statement, what are three applicable DATABLOCKSIZE options? (Choose three)


Options are :

  • DATABLOCKSIZE = 32 KILOBYTES (Correct)
  • MAXIMUM DATABLOCKSIZE (Correct)
  • DATABLOCKSIZE = MAXIMUM
  • MINIMUM DATABLOCKSIZE (Correct)
  • OWNERS DATABLOCKSIZE

Answer : DATABLOCKSIZE = 32 KILOBYTES MAXIMUM DATABLOCKSIZE MINIMUM DATABLOCKSIZE

What are two accurate statements concerning multiple value-list compression (VLC)? (Choose two)


Options are :

  • VLC decreases storage capacity
  • VLC improves response time for table scans. (Correct)
  • VLC reduces the table header size
  • VLC reduce disk I/O time (Correct)

Answer : VLC improves response time for table scans. VLC reduce disk I/O time

What is caused as a result of demoralization on application coding?


Options are :

  • It usually increases data flexibility.
  • It generally reduces the potential for data problems.
  • It increases the cost of data retrieval.
  • It generally makes new applications more difficult to implement. (Correct)

Answer : It generally makes new applications more difficult to implement.

You are an administrator at Your Company. There is join between a Non-Partitioned Primary index (NPPI) table and a Partitioned Primary index (PPI) table with a huge number of surviving partitions after applying constraints. Which two of the following statements of the above NPPI to PPI join are accurate if you compare it to an NPPI to NPPI join? (Choose two)


Options are :

  • CPU utilization is higher. (Correct)
  • Disk I/Oare less.
  • Disk I/Os are higher (Correct)
  • Memory requirements are the same.
  • CPU utilization is the same.

Answer : CPU utilization is higher. Disk I/Os are higher

Which three statements pertaining to join plans are true? (Choose three)


Options are :

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

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

Which are three accurate statements concerning aggregations in views? (Choose three)


Options are :

  • They simplify the maintenance of complicated calculations (Correct)
  • Alias names are not required in views with aggregates
  • They simplify end-user coding requirements (Correct)
  • They are unaffected if new columns are added to underlying tables. (Correct)
  • They are affected if new columns are added to underlying tables.

Answer : They simplify the maintenance of complicated calculations They simplify end-user coding requirements They are unaffected if new columns are added to underlying tables.

Which two components do the rowid consist of? (Choose two)


Options are :

  • row number
  • AMP number identification
  • uniqueness value (Correct)
  • row hash (Correct)
  • table number

Answer : uniqueness value row hash

Decomposable data may be indicated by which two operators? (Choose two)


Options are :

  • EQUALS
  • BETWEEN
  • IN
  • SUBSTRING (Correct)
  • LIKE (Correct)

Answer : SUBSTRING LIKE

Which is the join plan that can utilize a primary index on each of two tables with matching Partitioned Primary Indexes?


Options are :

  • Exclusion Merge
  • Merge (Correct)
  • Bitmap
  • Product

Answer : Merge

Which of the following are three practical reasons for using a USI? (Choose three0


Options are :

  • to provide an additional Optimizer option for Merge Join planning
  • to provide good distribution of base rows
  • to providea option for avoiding full table scans (Correct)
  • to provide an alternate path to the data (Correct)
  • to provide a uniqueness constraint to a column (Correct)

Answer : to providea option for avoiding full table scans to provide an alternate path to the data to provide a uniqueness constraint to a column

Which statement is true if the following phrase is included in an EXPLAIN output? "Aggregate Intermediate Results are computed globally"


Options are :

  • Aggregation results are placed in a global temporary table
  • Both local and global aggregation phases are required. (Correct)
  • Aggregation requires a global spool file.
  • Aggregation requires no redistribution phase.

Answer : Both local and global aggregation phases are required.

Which two of the following statements are FALSE? (Choose two)


Options are :

  • ALTER TABLE supports compression on existing columns (Correct)
  • Primary index columns can be compressed (Correct)
  • Only fixed width columns can be compressed.
  • New columns with multi-value compression can be added to a table.

Answer : ALTER TABLE supports compression on existing columns Primary index columns can be compressed

Which compression method does Teradata use?


Options are :

  • Field level (Correct)
  • Cylinder level
  • Row level
  • Block level

Answer : Field level

"soft" Referential integrity has been defined on two tables joined in a view. When you are selecting columns using this view, which three of the following statements are true? (Choose three)


Options are :

  • If data is requested from the referencingtable, Teradata will lock and return data directly from the referencing table without joining to the referenced table. (Correct)
  • If data is requested from the referencedtable, Teradata will lock and return data directly from the referenced table without joining to the referencing table.
  • If data is requested from the referenced table,Teradata will do the expected join. (Correct)
  • If data is requested from both tables,Teradata will do the expected join. (Correct)

Answer : If data is requested from the referencingtable, Teradata will lock and return data directly from the referencing table without joining to the referenced table. If data is requested from the referenced table,Teradata will do the expected join. If data is requested from both tables,Teradata will do the expected join.

Of the following, which are three true statements regarding Teradata Statistic? (Choose three)


Options are :

  • Statistics are eliminated when all rows from the table are deleted.
  • Statistics can be collected on a global temporary table (Correct)
  • Table statistics are refreshed automatically byTeradata
  • Table statistics can be viewed using the STATS module ofTeradata Manager (Correct)
  • User must have at least one appropriate privilege on the object to COLLECT STATISTICS (Correct)

Answer : Statistics can be collected on a global temporary table Table statistics can be viewed using the STATS module ofTeradata Manager User must have at least one appropriate privilege on the object to COLLECT STATISTICS

In which three ways do Materialized global temporary tables differ from permanent tables? (Choose three)


Options are :

  • They are automatically dropped at the end of each session (Correct)
  • Rows are maintained after a system restart
  • Contents can optionally be at the end of each transaction (Correct)
  • Contents can be shared by other sessions
  • They are always empty when first materialized (Correct)

Answer : They are automatically dropped at the end of each session Contents can optionally be at the end of each transaction They are always empty when first materialized

Join steps can potentially be eliminated by which two features? (Choose two)


Options are :

  • multi-table join index (Correct)
  • matching primary index columns on the join conditions
  • "soft" Referential integrity (RI) constraints (Correct)
  • single table join index

Answer : multi-table join index "soft" Referential integrity (RI) constraints

If you assume that the required information has been collected, when is NUSI bit mapping utilized?


Options are :

  • when the query ANDs two more indexes with the bit mapping option
  • when two or more weakly selective indexed conditions areANDed and the combination is strongly selective (Correct)
  • when at least one of the NUSI is null
  • when two or more weakly selective indexed conditions areOred and the combination is strongly selective

Answer : when two or more weakly selective indexed conditions areANDed and the combination is strongly selective

Of the following properties, which is a feature of a NUSI?


Options are :

  • two-AMP operation
  • index row and table row on same AMP (Correct)
  • physical grouping of relateddat
  • two tables with the same index

Answer : index row and table row on same AMP

Which two statements pertaining to join indexes are correct? (Choose two)


Options are :

  • Join indexes are automatically updated when underlying data rows change. (Correct)
  • A query must reference the join indexes to activate it.
  • The Archive utility will restore join indexes.
  • Join indexes are normally used when tables are weakly selective.
  • Join indexes may have repeating values. (Correct)

Answer : Join indexes are automatically updated when underlying data rows change. Join indexes may have repeating values.

A USI subtable row includes which three elements? (Choose three)


Options are :

  • Secondary index rowed (Correct)
  • USI column value(s) (Correct)
  • Rowid of the base row (Correct)
  • subtable-id
  • UPI column value (S)

Answer : Secondary index rowed USI column value(s) Rowid of the base row

The following represents a query you are running against a three year history table SELECT *from orders WHERE o_orderdate BETWEEN ?003-07-01- AND ?003-07-07? Which statement regarding a NUSI on o orderdate is true?


Options are :

  • 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.
  • A hash-ordered NUSI should perform better than a value -ordered NUSI
  • A value- ordered NUSI should perform better than a hash-ordered NUSI. (Correct)

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

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions