TE0-123 Teradata 12 Physical Design & Implementation Exam Set 2

Which two statements are true about MLPPI? (Choose two.)


Options are :

  • The UNKNOWN partition can not be used with MLPPI.
  • Partitioning expressions are mapped into a single-level combined partitioning expression
  • MLPPI allows for twice the PPI partition limit.
  • Rows are hash ordered within the lowest partition levels.

Answer : The UNKNOWN partition can not be used with MLPPI. MLPPI allows for twice the PPI partition limit.

Which characteristic of a Unicode column needs to be considered when creating a table?


Options are :

  • A CLOB cannot be defined as a Unicode character set.
  • Unicode data types require twice the amount of space as Latin data types.
  • Latin data values and Unicode data values will hash to the same AMP.
  • Uppercase and lowercase values will generate different row hash values

Answer : Uppercase and lowercase values will generate different row hash values

Which two statements are true when relating UPIs to hashing? (Choose two.)


Options are :

  • A UPI lookup is performed by value.
  • Two distinct UPI values will hash differently.
  • Two different UPI values may have the same row hash.
  • Tables with the same UPI will have identical distributions.

Answer : A UPI lookup is performed by value. Two different UPI values may have the same row hash.

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 2

Which query analysis tool can be used to identify the queries that used a new index structure during the last month?


Options are :

  • Visual Explain
  • PMON
  • Teradata Workload Analyzer
  • DBQL

Answer : Teradata Workload Analyzer

Data is being loaded into a large table that is partitioned by day. Sometimes an incorrect year is keyed on the POS system that does not fall into the planned range of partitions. How should the table with PPI be implemented?


Options are :

  • Back date the date during loading,then update it when the partition exists.
  • Change the business requirement.
  • Implement PPI with a NO RANGErange
  • Implement PPI with an UNKNOWN range.

Answer : Change the business requirement.

Which three statements produce a result that includes derived data? (Choose three.)


Options are :

  • SELECTDeptId, AVG(Salary) FROM Employee GROUP BY DeptId;
  • SELECTDeptId FROM Employee GROUP BY DeptId HAVING SUM(Salary) > 100000;
  • SELECTEmpName, HireDate + 10 FROM Employee WHERE EmpId = 1239045;
  • SELECTSUM(Salary) FROM Employee WHERE Salary > 20000;
  • SELECTEmpName FROM Employee WHERE HireDate + 100 > TerminationDate;
  • SELECTDeptId FROM Employee GROUP BY DeptId ORDER BY SUM(Salary);

Answer : SELECTDeptId FROM Employee GROUP BY DeptId HAVING SUM(Salary) > 100000; SELECTEmpName, HireDate + 10 FROM Employee WHERE EmpId = 1239045; SELECTSUM(Salary) FROM Employee WHERE Salary > 20000;

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 5

A character field with a maximum length of 30 contains the following values with frequency: "HALF" frequency 50% of table rows "SOME OF THE ROWS" for 25% of table rows Various values for the remaining 25% of table rows Which data definition would minimize space utilization?


Options are :

  • CHAR(30) COMPRESS ('HALF', 'SOME OF THE ROWS')
  • VARCHAR(30) COMPRESS ('HALF', 'SOME OF THE ROWS')
  • CHAR(30) COMPRESS
  • VARCHAR(30) COMPRESS

Answer : CHAR(30) COMPRESS

Which two statements are true about denormalization? (Choose two.)


Options are :

  • Denormalized rows are likely to be narrower.
  • Denormalization often introduces update anomalies into the database.
  • Tables using compression can bedenormalized with fewer penalties.
  • Denormalization may make use of redefined groups.
  • Aggregate join indexes are a form ofdenormalization.

Answer : Denormalization may make use of redefined groups. Aggregate join indexes are a form ofdenormalization.

A user continually summarizes data from a single table for specific date ranges. Which index should be used to cover that query and avoid accessing the base table?


Options are :

  • Hash Ordered NUSI
  • Aggregate Join Index
  • MLPPI
  • multi-level Aggregate Join Index

Answer : MLPPI

TE0-123 Teradata 12 Physical Design & Implementation Exam Set 1

A query summarizes data from columns A and B in the Customer table. The data demographics indicate that the total number of rows in the table is 100,000,000 and the columns A and B are highly selective. Which type of index would cover the query?


Options are :

  • NUSIs on A and
  • PPI on A and B
  • MLPPI on A and B
  • Composite NUSI on A and B

Answer : NUSIs on A and

What is an advantage of a Volatile Table compared to a Global Temporary Table?


Options are :

  • A greater number of Volatile Tables can be defined in a single session
  • A Volatile Table frees up spool space for other purposes.
  • Rows are more likely to be held in memory
  • Less data dictionary access will be required.

Answer : Rows are more likely to be held in memory

Which choice will the Optimizer make when only one column value is known for a multicolumn USI?


Options are :

  • The Optimizer will choose a Full Table Scan (FTS) because the index is not fully qualified.
  • The Optimizer will hash the value it has to get the rowids and then merge join to the remaining column(s).
  • The Optimizer will build an access plan that uses the primary index
  • The Optimizer will hash the value it has and do a partial index search.

Answer : The Optimizer will build an access plan that uses the primary index

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 4

Given the following EXPLAIN text: First, we do a single-AMP JOIN step from TCPP.E by way of the unique primary index "TCPP.E.Employee_Number = 102001" with a residual condition of ("NOT TCPP.E.Dept_Number IS NULL)"), which is joined to TFACT.D by way of the unique primary index "TCPP.D.Dept_Number = TCPP.E.Dept_Number". TCPP.E and TCPP.D are joined using a ________ join, with a join condition of ("(1=1)"). Which join method is represented in this EXPLAIN text?


Options are :

  • Hash
  • Merge
  • Partition
  • Product
  • Nested

Answer : Merge

Which two techniques may be used for determining candidate columns to compress? (Choose two.)


Options are :

  • Evaluate columns with large numeric data types known to contain equally distributed values.
  • Evaluate all large data type columns including BLOBs and CLOBs.
  • Evaluate all non-indexed columns to look for the most frequently occurring values.
  • Evaluate VARCHAR data type columns for potential conversion to CHAR data type.
  • Evaluate columns where the number of unique values is less than 255.

Answer : Evaluate columns with large numeric data types known to contain equally distributed values. Evaluate all large data type columns including BLOBs and CLOBs.

A large Vendor table will be value accessed using the Name column. The maximum rows per Name is 50 and there are 100 rows per block in the table. What will enhance the performance of queries that access the table via the Name column?


Options are :

  • Put a Value Ordered NUSI on the Name column.
  • Put a NUSI on the Name column.
  • Put a USI on the Name column.
  • Partition by Name.

Answer : Put a USI on the Name column.

TE0-141 14 Teradata Basics Certified Practice Test Set 3

Which category of values will help determine NUSI usage by the Optimizer when interpreting the data demographic "typical rows/value"?


Options are :

  • a low number of distinct values
  • a high number of rows per value
  • a low number of rows per value
  • a very high number of rows per value

Answer : a very high number of rows per value

Which two criteria does the Optimizer use when developing an access or join plan? (Choose two.)


Options are :

  • . It uses WHERE or ON clause predicates.
  • It uses statistics where they would increase response time.
  • It considers the indexes of the tables.
  • It uses demographics from OCES-3.

Answer : It uses statistics where they would increase response time. It considers the indexes of the tables.

TE0-121 Teradata Basics Practice Test Set 2

What occurs when an index is defined as CHAR(10) and the WHERE clause of a query specifies the index value as integer?


Options are :

  • The Optimizer will convert the integer value to character and use the hash value to access the index.
  • All the values in the indexed column are converted to character in a spool file.
  • The query will not execute because the data types do not match.
  • All the values in the indexed column are converted to numeric in a spool file.

Answer : The query will not execute because the data types do not match.

Which action can help conserve disk space?


Options are :

  • Delete rows from an unused partition.
  • Drop an UNKNOWN partition when the partitioning column is not null.
  • Drop an unused range partition on a table with NO RANGE and UNKNOWN partitions.
  • Combine the NO RANGE partition with the UNKNOWN partition to have a single NO RANGE OR UNKNOWN partition.

Answer : Drop an unused range partition on a table with NO RANGE and UNKNOWN partitions.

Which two situations are most likely to be good candidates to consider for a multi-column statistic? (Choose two.)


Options are :

  • multi-column UPI of a small table
  • columns used in a GROUP BY statement
  • multi-columns used in non-equality constraints
  • multi-column UPI of a large table

Answer : multi-columns used in non-equality constraints multi-column UPI of a large table

TE0-121 Teradata Basics Practice Test Set 4

Which type of Referential Integrity allows the Optimizer to utilize join elimination in the generated plan with enforcement performed implicitly during the execution of the insert, delete, or update request?


Options are :

  • Standard RI
  • Soft RI
  • Application RI
  • . Batch RI
  • Explicit RI

Answer : Application RI

What are two results of using the CASE_N function when defining a PPI table? (Choose two.)


Options are :

  • It places the data row into a partition associated with that condition.
  • It places a null value into the UNKNOWN partition.
  • It evaluates the expression and is mapped into one of a list of ranges.
  • It evaluates a list of conditions and returns the position of the first condition that evaluates TRUE.

Answer : It places the data row into a partition associated with that condition. It places a null value into the UNKNOWN partition.

Which tool can help an administrator determine which columns to collect statistics on?


Options are :

  • DISPLAY STATS
  • Teradata Administrator
  • Visual Explain
  • Teradata Index Wizard

Answer : Teradata Index Wizard

TE0-121 12 Teradata Basics Certification Practical Exam Set 6

Which two statements are true about defining compression during column definition? (Choose two.)


Options are :

  • The default for COMPRESS is to compress all nulls in the column.
  • The COMPRESS attribute for a column may be changed after it has been defined.
  • Primary index columns may use value compression.
  • It compresses a specified set of values in a column to zero column space.
  • Up to 256 non-null values can be compressed for an individual column.

Answer : It compresses a specified set of values in a column to zero column space. Up to 256 non-null values can be compressed for an individual column.

Which query analysis tool can be used to identify performance issues in a currently running query?


Options are :

  • PMON
  • Teradata Workload Analyzer
  • DBQL
  • Visual Explain

Answer : DBQL

Analysis of a query finds that a large amount of I/O is being used to redistribute and sort approximately 100 million rows in preparation for joining two large tables together. Which two indexes could improve performance by avoiding both redistribution and sorting of those 100 million rows? (Choose two.)


Options are :

  • single table join index
  • value ordered join index
  • multi-table join index
  • global join index
  • hash ordered NUSI

Answer : multi-table join index hash ordered NUSI

TE0-123 Teradata 12 Physical Design & Implementation Exam Set 3

Which two demographics should be considered when determining value list compression? (Choose two.)


Options are :

  • value access frequency
  • distinct values
  • . typical rows/value
  • join frequency

Answer : . typical rows/value join frequency

A customer is performing TPump inserts using multi-statement requests into a base table that has a trigger to a queue table. Both tables share the same primary index. Which two types of blocking or locking may occur if SERIALIZE ON is not defined in the TPump statement? (Choose two.)


Options are :

  • Row hash level write locks will occur.
  • A table level lock may occur
  • . Cross session blocking may occur
  • Block level row hash write locks will occur.

Answer : Row hash level write locks will occur. Block level row hash write locks will occur.

A sales table has an order number column defined as a character data type. A user submits the following query: SELECT * FROM sales WHERE order_number = 123 Which two effects will occur during the processing of the query? (Choose two.)


Options are :

  • The 123 value is converted to character data.
  • The data stored in theorder_number column is converted to a numeric data type.
  • A Full Table Scan (FTS) is performed.
  • A single-AMP operation is performed.

Answer : The data stored in theorder_number column is converted to a numeric data type. A single-AMP operation is performed.

TE0-125 Teradata 12 Solutions Development Practice Exam Set 2

A retailer has defined a trigger on an inventory table that checks for the quantity of an item falling below some threshold value. When an item falls below the threshold, the trigger fires and inserts a row into a queue table. Which activity should occur next?


Options are :

  • The row in the queue table is processed by an application that reorders the low quantity item.
  • A trigger on the queue table fires and inserts a row into theinventory_order table.
  • Immediate analytic processing may occur, causing an event to be published.
  • A stored procedure analyzes the row in the queue table to decide if it warrants further action

Answer : A stored procedure analyzes the row in the queue table to decide if it warrants further action

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions