TEO-141 Teradata 14 Basics - Personal

What are some characteristics of a hot standby node?

1.) A node that is a member of a clique. 2.) Does not normally participate in the trusted parallel application (TPA). 3.) Can be brought into the configuration when a node fails in the clique. 4.) Helps with unplanned outages. 5.) Eliminates the need for a restart to bring a failed node back into service.

What are the 4 principal join strategies in Teradata?

1.) Merge Join (The Swiss Army Knife) 2.) Hash Join (The Sprinter) 3.) Nested Join (The Fastest, but scarce) 4.) Product Join (The Disliked Guest)

What are the principal components of the Teradata Architecture

1.) Paring Engine (Optimizer) 2.) Access Module Processors (AMPs) 3.) BYNETs 4.) Disks

What are the types of Recovery Journal?

1.) Transient Journal - Used for an interrupted transaction. 2.) Down-AMP Recovery Journal - Used in the event of an AMP failure. 3.) Permanent Journal -

Describe the steps in the Hashing Process

1.The primary index value goes into the hashing algorithm. 2.The output of the hashing algorithm is the row hash value. 3.The hash map points to the specific AMP where the row resides. 4.The PE sends the request directly to the identified AMP. 5.The AMP locates the row(s) on its vdisk. 6.The data is sent over the BYNET to the PE, and the PE sends the answer set on to the client application.

A USI is always a (X) AMP operation

2

How many secondary indexes can be defined for a table?

32

What is the maximum number of columns for a join index?

64

Briefly describe hot standby nodes

A Hot Standby Node (HSN) is a node that is a member of a clique that is not configured (initially) to execute any Teradata vprocs. If a node in the clique fails, the AMPs from the failed node move to the hot standby node. The performance degradation is 0%. When the failed node is recovered/repaired and restarted, it becomes the new hot standby node. A second restart of Teradata is not needed.

Briefly describe a Multi-Table Join Index and it's purpose

A Multi-Table Join index is a Join Index that involves two or more tables. Facilitates join operations by possibly eliminating join processing or by reducing/eliminating join data redistribution.

When would you choose a NUPI over a UPI?

A NUPI may before more effective for joins if frequently used join condition columns are not unique.

Briefly describe a Non-Unique Primary Index

A Non-Unique Primary Index (NUPI) means that the values for the selected column can be non-unique. Duplicate values can exist. A Non-Unique Primary Index will almost never spread the table rows evenly. An All-AMP operation will take longer if the data is unevenly distributed. You might pick a NUPI over an UPI because the NUPI column may be more effective for query access and joins.

Briefly describe a Single-Table Join Index and it's purpose

A Single-Table Join Index duplicates a single table, but changes the Primary Index. Users will only query the base table, but the Parsing Engine will use the Join Index. The reason to create a Single-Table Join Index is so joins can be performed faster because no Redistributions or Duplication needs to occur.

Describe the process and purpose of a transient journal.

A Transient Journal maintains data integrity when in-flight transactions are interrupted (due to aborted transactions, system restarts, and so on). Data is returned to its original state after transaction failure. A Transient Journal is used during normal system operation to keep "before images" of changed rows so the data can be restored to its previous state if the transaction is not completed. This happens on each AMP as changes occur. When a transaction is started, the system automatically stores a copy of all the rows affected by the transaction in the Transient Journal until the transaction is committed (completed). Once the transaction is complete, the "before images" are purged. In the event of a transaction failure, the "before images" are reapplied to the affected tables and deleted from the journal, and the "rollback" operation is completed.

Briefly describe a Unique Primary Index

A Unique Primary Index (UPI) is unique and cannot have any duplicates. If you try and insert a row with a Primary Index value that is already in the table, the row will be rejected. An UPI enforces UNIQUENESS for a column. A Unique Primary Index (UPI) will always spread the rows of the table evenly amongst the AMPs. UPI access is always a one-AMP operation.

Briefly describe a clique

A clique (pronounced, "kleek") is a group of nodes that share access to the same disk arrays. Each multi-node system has at least one clique. The cabling determines which nodes are in which cliques -- the nodes of a clique are connected to the disk array controllers of the same disk arrays.

What are the benefits of Fallback?

A level of protection beyond RAID disk array protection. Can be specified on a table-by-table basis to protect data requiring the highest availability. Permits access to data while an AMP is off-line. Automatically restores data that was changed during the AMP off-line period.

Briefly describe the physical structure of a Secondary Index

A secondary index is stored as a sub-table which points the secondary index value to the base table via the row-id. Unlike a Primary Index, a secondary index requires disk space, maintenance is handled by the system.

Parallel Database Extensions (PDE) are

A software layer that was added to the operating system to support the parallel software environment. The PDE controls the virtual processor (vproc) resource.

What is an Access lock?

Access locks can be specified by users unconcerned about data consistency. The use of an access lock allows for reading data while modifications are in process. Access locks are designed for decision support on tables that are updated only by small, single-row changes. Access locks are sometimes called "stale read" locks, because you may get "stale data" that has not been updated. Access locks prevent other users from obtaining the following locks on the locked data: Exclusive locks

A NUSI is always a (X) AMP operation

All

NoPI Table Capabilities Include

Are always Multi-Set Tables Have Secondary Indexes (USI or NUSI) Have Join Indexes Be Volatile or Global Temporary Tables Can COLLECT STATISTICS Be FALLBACK Protected Have Triggers Be Large Objects (LOBs) Have Primary Key Foreign Key Constraint

What is the syntax for creating a Unique Secondary Index (USI)

CREATE UNIQUE INDEX (Column1, ..., ColumnN) ON ..;

Columnar (NoPI) Table Fundamentals

Columnar Tables must be a NoPI Table so No Primary Index (NoPI). The NoPI brings even distribution to the table. Columnar Tables allow Columns to be Partitioned. An AMP still holds the entire row, but partitions vertically. Columns are placed inside their own individual Container. All Containers have the same amount of rows in the exact order. Single Columns or Multi-Columns can be placed inside containers. Each container looks like a small table for I/O purposes. Add up all the containers and you rebuild the row. Columnar Tables make sense when users query only certain columns. When a row is deleted it is NOT Physically Deleted but marked deleted

How does RAID 5 handle data?

Data is striped across a rank of disks (spread across the disk drives) one segment at a time, using a binary "exclusive-or" (XOR) algorithm. Parity is also striped across all disk drives, interleaved with the data. A "parity byte" is an extra byte written to a drive in a rank. The process of writing data and parity to the disk drives includes a read-modify-write operation for each new segment: 1. Read existing data on the disk drives in the rank. 2.Read existing parity in that rank for the corresponding segment. 3.Calculate the parity: existing data + new data + existing parity = new parity. 4.Write new data. 5.Write new parity. If one of the disk drives in the rank becomes unavailable, the system uses the parity byte to calculate the missing data from the down drive so the system can remain operational. With a rank of 4 disks, if a disk fails, any missing data block may be reconstructed using the other 3 disks.

What does the hash map do?

Determines which amp will one the row

Briefly describe the position of disks in the Teradata Architecture

Disks are disk drives associated with an AMP that store the data rows.

Describe the possible processing efficiencies afforded by a Join Index

Eliminate base table access Eliminate aggregate processing Reduce joins Eliminate redistributions Eliminate Summary processing

A Unique Secondary Index (USI) serves two purposes

Enforces uniqueness on a column or group of columns. The database will check USIs to see if the values are unique. Speeds up access to a row (data retrieval speed).

The Teradata database can be used as:

Enterprise data warehousing Active data warehousing Customer relationship management Internet and EBusiness Data marts

What are the four types of lock?

Exclusive Write Read Access

What is an exclusive lock?

Exclusive locks are applied to databases or tables, never to rows. They are the most restrictive type of lock. With an exclusive lock, no other user can access the database or table. Exclusive locks are used when a Data Definition Language (DDL) command is executed (i.e., CREATE TABLE). An exclusive lock on a database or table prevents other users from obtaining any lock on the locked object.

Briefly describe the concept of Fallback in the Teradata system

Fallback protects your data by storing a second copy of each row of a table on a different AMP in the same cluster. If an AMP fails, the system accesses the Fallback rows to meet requests. Fallback provides AMP fault tolerance at the table level. With Fallback tables, if one AMP fails, all data is still available. Users may continue to use Fallback tables without any loss of access to data.

(True/False) FastLoad/MultiLoad will load with a join index

False

(True/False) Join indexes support BLOB and CLOB types

False

(True/False) A join index must be manually updated as data in the base table changes.

False, join indexes are automatically maintained.

(True/False) The Teradata Database is logarithmically scalable.

False, linearly.

Briefly describe a Hash Index and it's purpose

Hash Indexes are used similar to a Join Index, but Hash Indexes are maintained in AMP-Local tables and used to quickly find certain key columns in a base table.

How does RAID I handle failures?

If a disk fails, the Teradata Database is unaffected and the following are each handled in a different way: Reads Writes Replacements Reads: When a drive is down, the system reads the data from the other drive. There may be a minor performance penalty because the read will occur from one drive instead of both. Writes: When a drive is down, the system writes to the functional drive. No mirror image exists at this time. Replacements: After you replace the failed disk, the disk array controller automatically reconstructs the data on the new disk from the mirror image. Normal system performance is affected during the reconstruction of the failed disk.

How does RAID 5 handle failures?

If a disk fails, the Teradata Database is unaffected and the following are each handled in different ways: Reads Writes Replacements Reads: Data is reconstructed on-the-fly as users request data using the binary XOR algorithm. Writes: When a drive is down, the system writes to the functional drives, but not to the failed drive. Replacements: After you replace the failed disk, the disk array controller automatically reconstructs the data on the new disk, using known data values to calculate the missing data. Normal system performance is affected during reconstruction of the failed disk.

How does Fallback handle failures?

If two physical disks fail in the same RAID 5 rank or RAID 1 mirrored pair, the associated AMP vproc fails. Fallback protects against the failure of a single AMP in a cluster. If two AMPs in a cluster fail, the system halts and must be restarted manually, after the AMP is recovered by replacing the failed disk(s). Reads: When an AMP fails, the system reads all rows it needs from the remaining AMPs in the cluster. If the system needs to find a Primary row from the failed AMP, it reads the Fallback copy of that row, which is on another AMP. Writes: A failed AMP is not available, so the system cannot access any of that AMP's disk space. Copies of its unavailable primary rows are available as Fallback rows on the other AMPs in the cluster, and are updated there. Replacement: Repairing the failed AMP requires replacing the failed physical disks and bringing the AMP online. Once the AMP is online, the system uses the Fallback data on the other AMPs to automatically reconstruct data on the newly replaced disks.

Compare and contrast the data access using a USI and a NUSI

In both cases the SQL is submitted and the hashing algorithm calculates the row hash value. In the case of a UPI, the hash map points directly to AMP containing the subtable row corresponding to the row hash value. This get sent back over the BYNET to the AMP with that row. However, in the case of a NUSI, all AMPS are activated to find the hash value of the NUSI in their index subtables. The AMPs whose subtables contain that value become the participating AMPs in this request. The other AMPs discard the message. Each amp find the row IDs corresponding to the row hash values on their subtable. The qualifying row are sent over the BYNET to the PE and the PE send the answer set on to the client application.

Rows are stored...

In data blocks which are stored in Cylinders

Describe the role of a clique in the event of node failure

In the event of a node failure, cliques provide for data access through vproc migration. When a node resets, the following happens to the AMPs: 1.) When the node fails, the Teradata Database restarts across all remaining nodes in the system. 2.) The vprocs (AMPs) from the failed node migrate to the operational nodes in its clique. 3.) The PE vprocs will migrate as follows: LAN attached PEs will migrate to other nodes in the clique. Channel attached PEs will not migrate. While that node remains down, that channel connection is not available. 4.) Disks managed by the AMP remain available and processing continues while the failed node is being repaired.

Briefly describe a Sparse Join Index and it's purpose

Is a Join Index that doesn't use every row because it has a WHERE Clause. This is done to save space and time.

Briefly describe a Compressed Join Index and it's purpose

Is designed to save space by not REPEATING the repeating values. See other cards on compression for more details.

Under what conditions would a hash value be duplicated?

It is possible for the hashing algorithm to end up with the same row hash value for two different rows. There are two ways this could happen: Duplicate NUPI values: If a Non-Unique Primary Index is used, duplicate NUPI values will produce the same row hash value. Hash synonym: Also called a hash collision, this occurs when the hashing algorithm calculates an identical row hash value for two different Primary Index values. H To differentiate each row in a table, every row is assigned a unique Row ID. The Row ID is the combination of the row hash value and a uniqueness value.

Briefly describe locks in the Teradata Database

Locking prevents multiple users who are trying to access or change the same data simultaneously from violating data integrity. This concurrency control is implemented by locking the target data. Locks are automatically acquired during the processing of a request and released when the request is terminated. Levels of Locking Locks may be applied at three levels: Database Locks: Apply to all tables and views in the database. Table Locks: Apply to all rows in the table or view. Row Hash Locks: Apply to a group of one or more rows in a table.

The Teradata Database can run on the following operating systems

Microsoft Windows SusE Linux

What is the most taxing process for an AMP?

Moving data blocks into memory

Describe the space impact of the Fallback feature

Need extra space for Fallback-protected tables in addition to the original database size. RAID protection of Fallback-protected tables

Describe the performance impact of the Fallback feature

Need twice the disk space for storage and twice the I/O required for INSERTs, UPDATEs, and DELETEs of rows in Fallback protected tables. The Fallback option does not require any extra I/O for SELECTS, as the system will read from one copy or the other, and the Fallback I/O will be performed in parallel with the primary I/O so there is no performance hit.

NoPI Table Restrictions Include

No Primary Indexes allowed No SET Tables No Partition Primary Index (PPI) tables No Queue Tables No Hash Indexes No Identity Columns No Permanent Journaling Can't be the Target Table for any UPDATE, UPSERT or MERGE-INTO Statements

Describe the process and purpose of a permanent journal.

Permanent Journals are an optional feature used to provide an additional level of data protection. You specify the use of Permanent Journals at the table level. It provides full-table recovery to a specific point in time. It can also reduce the need for costly and time-consuming full-table backups. Permanent Journals are tables stored on disk arrays like user data is, so they take up additional disk space on the system. The Database Administrator maintains the Permanent Journal entries (deleting, archiving, and so on.) How Permanent Journals Work A Database (object) can have one Permanent Journal. When you create a table with Permanent Journaling, you must specify whether the Permanent Journal will capture: *Before images -- for rollback to "undo" a set of changes to a previous state. *After images -- for rollforward to "redo" to a specific state. You can also specify that the system keep both before images and after images. In addition, you can choose that the system captures: *Single images (the default) -- this means that the Permanent Journal table is not Fallback protected. *Dual images -- this means that the Permanent Journal table is Fallback protected. The Permanent Journal captures images concurrently with standard table maintenance and query activity. The additional disk space required may be calculated in advance to ensure adequate resources. Periodically, the Database Administrator must dump the Permanent Journal to external media, thus reducing the need for full-table backups since only changes are backed up rather than the entire database.

What are the main types of data protection in the Teradata Database

RAID Cliques Hot Standby Nodes Fallback Journaling Locks

Briefly describe RAID I

RAID 1 is a data protection scheme that uses mirrored pairs of disks to protect data from a single drive failure. RAID 1 requires double the number of disks because every drive has an identical mirrored copy. Recovery with RAID 1 is faster than with RAID 5. The highest level of data protection is RAID 1 with Fallback.

How does RAID I work?

RAID 1 protects against a single disk failure using the following principles: Mirroring Reading Mirroring: RAID 1 maintains a mirrored disk for each disk in the system. Reading: Using both copies of the data, the system reads data blocks from the first available disk. This does not so much protect data as provide a performance benefit.

Briefly describe RAID 5

RAID 5 is a data protection scheme that uses parity striping in a disk array to protect data from the failure of a single drive. For the Teradata Database, RAID 5 uses the concept of a rank, which is a set of disks working together. Note that the disks in a rank are not directly cabled to each other. RAID 5 also uses some overhead during a write operation, because it has to read the data, then calculate and write the parity

Briefly describe RAID and list the configuration levels supported by the Teradata Database

RAID: Redundant Array of Inexpensive Disks (RAID) is a storage technology that provides data protection at the disk drive level. There are 6 RAID configuration levels. RAID 1 and 5 are supporting, the recommendation is to always use 1 for maximum data protection. RAID 0 - Data striping RAID 1 - Disk mirroring RAID 2 - Parallel array, hamming code RAID 3 - Parallel array with parity RAID 4 - Data parity protection, dedicated parity drive RAID 5 - Data parity protection, interleaved parity

What is a read lock?

Read locks are used to ensure consistency during read operations. Several users may hold concurrent read locks on the same data, during which time no data modification is permitted. Read locks prevent other users from obtaining the following locks on the locked data: Exclusive locks Write locks

What are the 1.) Requirements 2.) Process 3.) Possible preparations for a Nested Join

Requirements: Spool 1 allows a unique ROWHASH access (a unique index is defined) Spool 2 allows any kind of ROWHASH access (a unique or not unique is index defined) Process: The qualifying row of spool 1 is accessed by usage of any unique index. The row is relocated to the AMP owning the rows of spool 2 Spool 2 is full table scanned and each row is combined with the one row from Spool 1 Possible Join Preparations required: None

What are the 1.) Requirements 2.) Process 3.) Possible preparations for a Hash Join

Requirements: The rows to be joined have to be on a common AMP The smaller spool is sorted by the ROWHASH calculated over the join column(s) and kept in the FSG cache The bigger spool stays unsorted Process: The bigger spool is full table scanned row by row Each ROWID from the bigger spools is searched in the smaller spool (with a binary search) Possible Join Preparations required: Re-Distribution of the smaller spool by ROWHASH or Duplication of the smaller spool to all AMPs Sorting of the smaller spools

What are the 1.) Requirements 2.) Process 3.) Possible preparations for a Product Join

Requirements: The rows to be joined have to be on the AMP No spool needs to be sorted! Process: A full table scan is done on the smaller spool and Each qualifying row of spool 1 is compared against each row of spool 2 Possible Join Preparations required: Re-Distribution of one or both spools by ROWHASH or Duplication of the smaller spool

What are the 1.) Requirements 2.) Process 3.) Possible preparations for a Merge Join

Requirements: The rows to be joined have to be on a common AMP Both spools must be sorted by the ROWID calculated over the join column(s) Process: The ROWHASH of each qualifying row in the left spool is used to look up matching rows with identical ROWHASH in the right spool (with a binary search as both spools are sorted by ROWID) Possible preparations: Re-Distribution of one or both spools by ROWHASH or Duplication of the smaller spool to all AMPs Sorting of one or both spools by the ROWID The common AMP of rows from two spools being joined is defined by the join columns. This leaves us with 3 data distribution scenarios: 1.The Primary Indexes (or any other suitable index) of both tables equals the join columns: No join preparation is needed as the rows to be joined are already on the common AMP 2.Only the Primary Index (or any other suitable index) of one table matches the join columns: The rows of the second table have to be relocated to the common AMP 3.Neither the Primary Index of the first table (or any other suitable index) nor the Primary Index (or any other suitable index) of the second table matches the join columns: The rows of both tables have to be relocated to the common AMP Relocation of rows to the common AMP can be done by redistribution of the rows by the join column(s) ROWHASH or by copying the smaller table to all AMPs.

(Equation) Row ID =

Row Hash Value + Uniqueness Value where the uniqueness value is used to differentiate between rows whose Primary Index values generate identical row hash values. In most cases, only the row hash value portion of the Row ID is needed to locate the row. When each row is inserted, the AMP adds the row ID, stored as a prefix of the row. The first row inserted with a particular row hash value is assigned a uniqueness value of 1. The uniqueness value is incremented by 1 for any additional rows inserted with the same row hash value.

Rules for a primary index:

Rule 1: One Primary Index per table. Rule 2: A Primary Index value can be unique or non-unique. Rule 3: The Primary Index value can be NULL. Rule 4: The Primary Index value can be modified. Rule 5: The Primary Index of a populated table cannot be modified. Rule 6: A Primary Index has a limit of 64 columns.

Rules for Secondary Index

Rule 1: Secondary Indexes are optional. Rule 2: Secondary Index values can be unique or non-unique. Rule 3: Secondary Index values can be NULL. Rule 4: Secondary Index values can be modified. Rule 5: Secondary Indexes can be changed. Rule 6: A Secondary Index has a limit of 64 columns.

The Teradata Data is primarily a Client/Server

Server

UPI and NUPI will always be (X) AMP retrieves

Single

The most direct and efficient way for the system to find a row is a

Single AMP operation. Follow-up, when a SQL request against a table uses the primary index the request becomes a single AMP operation.

The following are type of Join Indexes (Name as many as you can)

Single Table Join Index Multi-Table Join Index Multi-Table Compressed Join Index Aggregate Join Index Sparse Join Index Global Join Index Hash Index

What is the Teradata database?

Teradata is a relational database management system (RDBMS) that is: • Teradata is an open system, running on a UNIX MP-RAS or Windows server platform. • Teradata is capable of supporting many concurrent users from various client platforms. • Teradata is compatible with industry standards (ANSI compliant). • Teradata is completely built on a parallel architecture.

What are the principal benefits of using Teradata?

Teradata supports more larger warehouse data than all competitors combined. Teradata Database can scale from 100 gigabytes to over 100+ petabytes of data on a single system without losing any performance .This is called Scalability. Provides a parallel-aware Optimizer that makes query tuning unnecessary to get a query to run. Automatic and even data distribution eliminates complex indexing schemes or time-consuming reorganizations . Teradata Database can handle the most concurrent users, who are often running multiple, complex queries. Designed and built with parallelism. Supports ad-hoc queries using SQL Single point of control for the DBA (Teradata Manager). Unconditional parallelism (parallel architecture) Teradata provides the lowest total cost (TCO) of ownership High availability of data because there is no single point of failure - fault tolerance is built-in to the system.

Briefly describe an AMP

The AMP is a virtual processor (vproc) designed to managing a portion of the entire database. It performs all database management functions such as sorting, aggregating, and formatting data. The AMP receives data from the PE, formats rows, and distributes them to the disk storage units it controls. The AMP also retrieves the rows requested by the Parsing Engine.

Briefly describe the role of the BYNETs

The BYNET act as message-passing layer. It decides which AMP should receive a message.

Describe the process and purpose of a Down-AMP recovery journal.

The Down-AMP Recovery Journal allows continued system operation while an AMP is down (for example, when two disk drives fail in a rank or mirrored pair). A Down-AMP Recovery Journal is used with Fallback-protected tables to maintain a record of write transactions (updates, creates, inserts, deletes, etc.) on the failed AMP while it is unavailable. The Down-AMP Recovery Journal starts automatically after the loss of an AMP in a cluster, Any changes to the data on the failed AMP are logged into the Down-AMP Recovery Journal by the other AMPs in the cluster. When the failed AMP is brought back online, the restart process includes applying the changes in the Down-AMP Recovery Journal to the recovered AMP. The journal is discarded once the process is complete, and the AMP is brought online, fully recovered.

Briefly describe the role of the Parsing Engine

The Parsing Engine (PE) is a component that interprets SQL requests, receives input records, and passes data. It sends the messages through the BYNET to the AMPs.

Describe the data access steps with a NUSI

The SQL is submitted, specifying a NUSI The hashing algorithm calculates a row hash value for the NUSI All AMPs are activated to find the hash value of the NUSI in their index subtables. The AMPs whose subtables contain that value become the participating AMPs in this request. The other AMPs discard the message. Each participating AMP locates the row IDs (row hash value plus uniqueness value) of the base rows corresponding to the hash value The participating AMPs access the base table rows, which are located on the same AMP as the NUSI subtable The qualifying rows are sent over the BYNET to the PE, and the PE sends the answer set on to the client application .

Describe the data access steps with a USI

The SQL is submitted, specifying a USI . The hashing algorithm calculates a row hash value. The hash map points to the AMP containing the subtable row corresponding to the row hash value. The subtable indicates where the base row resides. The message goes back over the BYNET to the AMP with the row and the AMP accesses the data row. The row is sent over the BYNET to the PE, and the PE sends the answer set on to the client application. As shown in the example above, accessing data with a USI is typically a two-AMP operation. However, it is possible that the subtable row and base table row could end up being stored on the same AMP, because both are hashed separately. If both were on the same AMP, the USI request would be a one-AMP operation.

(TRUE/FALSE) Rows distribution can be skewed with a non-unique PI

True

(True/False) A Join index is updated as the base table changes.

True

(True/False) After restoring a table you must drop and recreate the join index

True

(True/False) Join indexes can has NUPI and NUSI

True

(True/False) Join indexes support collecting statistics on Primary and Secondary indexes

True

(True/False) Regarding a secondary index, an AMP will hold the secondary index values for their rows in the base table only.

True

(True/False) Triggers with Join Indexes are allowed V2R6

True

How many different ways does an AMP sort data, describe them.

Two. TBD

What type of index ensures an even distribution of rows across amps?

Unique Primary Index

Briefly describe a Trusted Parallel Application (TPA) as it relates to the Teradata Database.

Uses PDE to implement virtual processors (vprocs). The Teradata Database is classified as a TPA. The four components of the Teradata Database TPA are: AMP (Top Right) PE (Bottom Right) Channel Driver (Top Left) Teradata Gateway (Bottom Left)

When would a UPI retrieve be a single rather than one AMP operation?

When the secondary index subtable row and the base table row are stored on the same AMP.

Briefly describe a Aggregate Join Index and it's purpose

Will allow tracking of the Aggregates SUM and COUNT on any table. Essential stores aggregate calculations on a table, the users never query the join index directly.

What is a write lock?

Write locks enable users to modify data while maintaining data consistency. While the data has a write lock on it, other users can only obtain an access lock. During this time, all other locks are held in a queue until the write lock is released.

Can a primary index contain multiple columns?

Yes, up to 64 columns