The query frequently joins a large fact table (VehicleEvent) with a master data table (Vehicle) and performs an aggregation (COUNT) grouped by columns from both tables. An Aggregate Join Index (AJI) is the optimal solution for this specific use case. An AJI pre-computes and stores the results of this join and aggregation. When the query is executed, the Teradata Optimizer can directly access the much smaller, pre-aggregated AJI instead of performing the expensive join and aggregation on the large base tables. This dramatically improves query runtime and reduces I/O and CPU resource consumption.

B. A sparse join index is designed to index a filtered subset of rows using a WHERE clause, not to pre-compute aggregations.

C. While NUSIs can aid in row lookups, they do not pre-aggregate data. The resource-intensive join and aggregation would still be required.

D. Single-table join indexes provide an alternative primary index for a table but do not pre-join or pre-aggregate data from multiple tables.

1. Teradata Vantage™ - Database Design, B035-1094-0123K, Release 17.20, Chapter 10: Join and Hash Indexes, Section: "Aggregate Join Indexes": This document states, "Aggregate join indexes can be used to satisfy queries that summarize large amounts of data... The Optimizer can use an aggregate join index to satisfy a query if the columns in the SELECT and WHERE clauses of the query are a subset of the columns in the index definition, and if the query aggregates are the same as or can be derived from the aggregates in the index definition." This directly matches the scenario.

2. Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184-0423K, Release 17.20, Chapter 12: Join and Hash Indexes, Section: "CREATE JOIN INDEX": The documentation explains, "The primary purpose of an aggregate join index is to enhance the performance of decision support queries that frequently perform the same aggregations on the same column or columns." This confirms that an AJI is the correct tool for a frequently performed aggregate query.

The TDSERVERDB database is specifically created during the Teradata QueryGrid installation process to store the definitions and parameters of foreign servers. When an administrator uses the CREATE SERVER or ALTER SERVER DDL statements to manage connections to remote systems, the metadata for these foreign server objects is created and maintained within tables inside the TDSERVERDB database. This database acts as the central repository for all QueryGrid foreign server configurations on the Vantage system.

A. TDSYSFNLIB: This system database contains system functions, operators, and user-defined functions (UDFs), not the definitions for foreign servers used by QueryGrid.

C. TDFOREIGNDB: This is not a standard database created or used by the Teradata QueryGrid software for storing foreign server definitions.

D. SECADMIN: This is typically a user-defined database or user for security administration tasks and does not hold system-level QueryGrid configuration objects.

1. Teradata QueryGrid: Installation and User Guide, 02.08, Document ID: B035-5800-028K, Chapter 3: Installing Teradata QueryGrid Software on Teradata Database, Section: "Running the post-installation script".

This section details the tdqg-database-setup.sql script, which explicitly creates the TDSERVERDB database. The guide states, "The TDSERVERDB database contains the foreign server objects."

2. Teradata Vantage™ SQL Data Definition Language Syntax and Examples, Document ID: B035-1144-B20K, Chapter 2: Data Definition Statements, Section: "CREATE SERVER".

The documentation for the CREATE SERVER statement explains its function in defining a foreign server. It notes that the resulting foreign server object is stored in the TDSERVERDB database, which must exist before the statement can be used. For example, it specifies, "The foreign server object is created in the TDSERVERDB database."

When a profile assigned to a user is dropped, the Profile attribute for that user in the DBC.UsersV view is set to NULL. Upon the user's next logon, the system applies the system-level defaults for any attributes that would have been controlled by the profile. This includes password attributes such as expiration, complexity, and lockout rules, which are defined in the DBC.SysSecDefaults table and are visible through the DBC.SecurityDefaultsV view. The logon process will succeed, but with these system-wide security settings enforced.

A. While the user's profile setting in the data dictionary does become NULL, this is the cause, not the full effect. The question asks what happens when the user logs on, and this option only describes the state change, not the resulting behavior.

C. The system does not issue a warning message in this scenario. It transparently applies the system-level default settings for the session without notifying the user of the change.

D. Once a profile is dropped, it ceases to exist, and none of its specifications, including SPOOL or DEFAULT DATABASE, can be applied. The user will revert to their own defined attributes from the CREATE/MODIFY USER statement or system defaults.

1. Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, Release 17.20 (B035-1144-172K).

Section: "DROP PROFILE"

Content: "When you drop a profile, any user or profile that was assigned the dropped profile is reassigned to have no profile (the value of the Profile column in DBC.Users is NULL)." This supports the initial state change that leads to the behavior described in the correct answer.

2. Teradata Vantage™ - Security Administration, Release 17.20 (B035-1100-172K).

Chapter 6: "Managing User Passwords", Section: "Password Controls Precedence"

Content: This section explains that if a user is not assigned a profile (i.e., the profile is NULL), the system-level password controls defined in DBC.SysSecDefaults apply. This directly validates that the attributes from DBC.SecurityDefaultsV (the view on DBC.SysSecDefaults) will be used.

Teradata Workload Management Filters are designed to screen incoming requests or sessions based on specific classification criteria. An administrator can create a filter that identifies sessions based on a combination of attributes, such as the application username and the client application/ID. The filter can then be configured with an action, such as 'Reject,' to prevent the session from being established or the query from running. This directly addresses the security policy requirement to prohibit access for a specific user/client combination.

A. Flex Throttle: Flex Throttles are used to dynamically manage the concurrency of active queries based on system health conditions (e.g., CPU usage), not to reject sessions based on user or client criteria.

B. Utility Session: This is not a specific workload management feature for enforcing security policies. It refers to sessions initiated by Teradata utilities, which are managed by workloads but not filtered by a feature of this name.

C. Exception: Exceptions are rules that trigger actions (like aborting a query) when a running query violates predefined resource limits (e.g., exceeds CPU time), not for preventing a session from starting.

1. Teradata Vantage™ Workload Management User Guide, Release 17.20, B035-1197-112K, November 2022.

Chapter 4: Workload Management Setup Steps > Filters (Page 61): "Filters provide a way to screen requests that you do not want to run on the system... A filter specifies classification criteria for a request and an action to take if the request matches the criteria. The action can be to reject the request..."

Filter Classification Criteria (Page 62): The guide lists User Name, Application, and Client ID as valid classification criteria, confirming that a filter can target the specific combination mentioned in the question.

Chapter 6: Throttles (Page 91): "Throttles limit the number of concurrent queries from all sources or from a specific workload." This confirms throttles are for concurrency, not rejection based on identity.

Chapter 7: Exceptions (Page 105): "Exceptions are rules that specify what to do when a query misbehaves or exceeds system resource limits." This confirms exceptions apply to already running queries.

Flex Throttles are a feature within Teradata Active System Management (TASM) specifically designed to manage workload concurrency dynamically. When a workload with a Flex Throttle enabled reaches its concurrency limit, it can "borrow" unused Amp Worker Task (AWT) slots from the system. This allows the workload to temporarily exceed its defined limit to process queries that would otherwise be delayed, without requiring a permanent modification to the carefully chosen base concurrency limit. This directly addresses the client's need for flexibility while maintaining their established workload rules.

A. Use a system throttle.

A system throttle imposes a global limit on all queries entering the database, which does not target the specific workload causing delays.

B. Use Query Group Viewpoint portlet to change the throttle limit temporally.

The Query Group Viewpoint portlet is used for monitoring and analysis of query groups, not for modifying workload definitions like throttle limits.

D. Use Query Monitor Viewpoint portlet to change query workloads.

This is a manual, reactive action on individual queries. It is not a systemic solution to automatically manage frequent delays caused by concurrency limits.

---

1. Teradata® Workload Management User Guide (Release 16.20, B035-1197-118K)

Section: Chapter 4: Workload Management Methods and Rules > Throttles > "Flex Throttles"

Content: "Flex throttles allow a workload to exceed its concurrency limit when there are available AWTs... When a workload with a flex throttle is at its concurrency limit, and a new query for that workload arrives, Teradata Database checks for available AWTs. If AWTs are available, the query is allowed to run, even though the workload concurrency limit is exceeded." This directly supports the use of Flex Throttles to handle delays without changing the base limit.

2. Teradata® Viewpoint User Guide (Release 16.20, B035-2206-118K)

Section: Chapter 11: Portlets > "Query Monitor"

Content: This section describes the Query Monitor portlet's functions, which include monitoring sessions and queries, and taking actions like aborting a query or changing its workload. This confirms it is a tool for manual intervention on specific queries, not a policy-based solution for concurrency.

3. Teradata® Viewpoint User Guide (Release 16.20, B035-2206-118K)

Section: Chapter 11: Portlets > "System Health"

Content: The description of system-level throttles confirms they are global settings: "The System Throttle... limits the number of concurrent requests that can execute on the system." This validates why option A is incorrect for a workload-specific issue.

The most effective and proactive method to prevent poorly written queries, such as those causing product joins on large tables, from harming the system is to use a Teradata Active System Management (TASM) system filter. Filters are rules that evaluate queries before they execute. An administrator can configure a filter to specifically detect and reject queries that are estimated to cause a product join, thereby preventing them from ever running and consuming system resources. This directly addresses the client's need to "avoid the harm" caused by these specific queries.

A. Use the Query Spotlight Viewpoint portlet to deprioritize the queries.

This is a reactive, manual action taken on a query that is already running. Deprioritizing only slows the query down; it does not stop the resource-intensive product join from occurring.

B. Use the Flex Throttle option.

Flex Throttles manage the number of concurrent queries based on system health. They do not inspect the nature of individual queries and cannot prevent a specific type of query, like a product join, from running.

D. Use the Query Log Viewpoint portlet to identity and abort the harmful queries.

The Query Log portlet is for analyzing historical query data after execution. It cannot be used to identify or abort queries that are currently running; that is a function of other portlets like Query Spotlight.

1. Teradata Vantage™ Workload Management User Guide, Release 17.20, B035-1197-112K.

Section: Filters: "Filters provide a way to prevent queries from running on the system based on user, query characteristics, or both... For example, you can use filters to prevent queries that are known to be resource-intensive, such as those that result in product joins, or queries that are estimated to run for a long time." (This directly supports option C).

2. Teradata® Viewpoint User Guide, Release 17.20, B035-2206-112K.

Section: Query Spotlight: "Query Spotlight is a real-time query monitor... You can perform actions on sessions and queries, such as changing a workload, changing a priority, or aborting a request." (This confirms option A is a reactive measure for already-running queries).

Section: Query Log: "The Query Log portlet allows you to view historical data about queries that have run on your Teradata systems." (This confirms option D is for historical analysis, not real-time intervention).

3. Teradata Vantage™ Workload Management User Guide, Release 17.20, B035-1197-112K.

Section: Throttles: "Throttles limit the number of concurrent queries... Flex throttles provide a way to dynamically adjust the throttle limit based on system conditions." (This confirms option B is for concurrency management, not query-specific rejection).

Detailed information for QueryGrid requests is found within the execution plans and step-level data of individual queries. The portlets My Queries, Query Groups, and Completed Queries are all designed for monitoring and analyzing individual queries. My Queries shows active queries for the current user, Query Groups shows active queries belonging to specific workload groups, and Completed Queries provides historical data for finished queries. All three provide access to the detailed query steps, which is where QueryGrid's remote query execution information is displayed, making them the correct choices for this type of analysis.

A. Application Queries, My Queries, Query Groups

While these portlets can show active QueryGrid queries, this option omits Completed Queries, which is essential for detailed post-execution analysis of historical QueryGrid requests.

B. My Queries, Completed Queries, Metric Heatmap

Metric Heatmap provides a high-level, color-coded visualization of system health metrics and does not contain detailed information about individual query requests.

C. Metrics Analysis, Node Monitor, Completed Queries

Metrics Analysis and Node Monitor are system-level tools. They display performance metrics over time and node status, respectively, not the specific execution details of a QueryGrid query.

1. Teradata® Viewpoint User Guide, 17.10 (B035-2206-017K), Chapter 5: "Monitoring Queries".

Section: My Queries Portlet: "The My Queries portlet displays queries submitted by the current user." This portlet allows drilling down into query details, including the execution plan where QueryGrid steps are visible.

Section: Completed Queries Portlet: "The Completed Queries portlet shows details of queries that have completed... You can view query-level details, such as the query steps..." This is a primary source for historical QueryGrid query analysis.

2. Teradata® Viewpoint User Guide, 17.10 (B035-2206-017K), Chapter 6: "Workload Management Portlets".

Section: Query Groups Portlet: "The Query Groups portlet allows you to monitor queries belonging to specific Workloads." Like other query monitoring portlets, it provides access to detailed step-level information for the active queries it displays.

To identify queries consuming a specific high volume of I/O (over 100 million), the TotalIOCount metric is the most direct and essential measure. This metric, found in Teradata's Database Query Log (DBQLogTbl), aggregates the total logical I/O operations for the entire query across all AMPs.

To diagnose the cause, such as a large redistribution, the PJI (Product Join Index Skew) metric is highly relevant. A high PJI value indicates that during a product join, the redistribution of rows to the AMPs was severely unbalanced. This skew is a common cause of performance issues, including the excessive I/O associated with large redistributions and potential full table scans of the spooled, duplicated table.

A. MAXAMPIO reflects the I/O of the single busiest AMP, not the query's total I/O, so it cannot be used to verify the "100 million IOs" threshold.

C. I/O SKEW % measures the degree of I/O imbalance but not the total I/O volume. UII (Unique Index Indicator) is not a direct measure of I/O consumption.

D. REQPHYSIO is a system-level I/O metric from ResUsage tables, not a query-specific metric suitable for identifying individual problematic queries from DBQLogTbl.

1. Teradata Vantage™ Data Dictionary, Release 17.20, B035-1092-172K, Chapter 2: Data Dictionary Views and Tables.

Page 1008, DBC.QryLogV View Definition: The TotalIOCount column is defined as "Total I/O count." This directly supports its use for measuring the overall I/O of a query.

Page 999, DBC.QryLogV View Definition: The PJI column is defined as "Product Join Index Skew." The description clarifies it measures the row count on the most-populated AMP as a percentage of the total rows in a product join, directly indicating redistribution skew.

2. Teradata Vantage™ Database Administration, Release 17.20, B035-1093-172K, Chapter 11: Monitoring System Activity.

Section: "Using DBQL to Identify Problem Queries": This section details the use of DBQLogTbl for performance analysis. It emphasizes tracking resource consumption metrics like TotalIOCount to find expensive queries. It also discusses analyzing skew metrics to understand the root cause of high resource usage. The combination of a high TotalIOCount and a high skew metric like PJI is a classic pattern for identifying queries with redistribution problems.

Workload Designer: This portlet is the interface for Teradata Workload Management (TASM/TIWM). Administrators use it to define Rulesets and Exceptions. An "exception" is a specific workload event (e.g., a query running too long) that can be configured to trigger an alert.

Server Management: This portlet provides a view of the physical and virtual infrastructure (CMICs, nodes). It specifically handles Server Management summary alerts related to hardware or OS-level health.

Performance Data Collection: This portlet configures which data metrics are harvested from the system. Specific data collectors include "Job" (e.g., for specific job metrics) and "Database Space" (for perm/spool space usage). Enabling these collectors is the prerequisite "configuration" required to trigger alerts based on these metrics.

Monitored Systems: By elimination and functional hierarchy, this matches the general definition of adding alerts to a system. The Monitored Systems portlet is where systems are added to Viewpoint, and it serves as the foundational setup where alert connectivity and system-level monitoring rules are initialized (often linking to the Alert Setup service).

Teradata Viewpoint User Guide, "Administration Portlets" Section:

Reference for Workload Designer: Describes configuring exceptions within a workload management ruleset to trigger actions such as alerts.

Reference for Performance Data Collection: Lists distinct data collectors including "Database Space" and "Job", which must be enabled/configured to monitor and alert on those specific areas.

Reference for Server Management: details the "Server Management" portlet and its "Summary" views for hardware alerts.

Teradata Official Documentation (Docs.teradata.com): "Workload Designer Portlet" – "Exceptions allow you to define rules... and actions such as Send an Alert."

The Teradata Optimizer cannot determine the number of defined partitions directly from the table's Data Definition Language (DDL) when the partitioning expression is based on the CASEN function. The CASEN function contains conditional logic that is opaque to the Optimizer, preventing it from mapping column values to specific partitions without external information. In the absence of collected statistics on the partitioning columns, the Optimizer makes a conservative, worst-case assumption that the table has the maximum number of partitions (65,535 for 2-byte partitioning). This can lead to the selection of a suboptimal query plan due to overestimated costs.

A. Partitioning on a character column does not inherently cause this assumption; the complexity of the function used on the column is the cause.

C. The number or data type of partitioning columns is not the direct cause; the function used in the partitioning expression is the determining factor.

D. While RANGEN can also cause this behavior, CASEN represents a more general and arbitrary conditional logic that the Optimizer cannot parse without statistics.

1. Teradata Vantage™ - Database Design, Release 17.20 (B035-1094-172K), Chapter 10: Partitioned Primary Indexes, Section: "Using CASEN for Row Partitioning". The manual states, "Without statistics on the partitioning columns, the Optimizer assumes the maximum number of partitions (65,535 for 2-byte partitioning) and might not be able to generate a good query plan."

2. Teradata Vantage™ SQL Data Definition Language Detailed Topics, Release 17.20 (B035-1184-172K), Chapter 5: CREATE TABLE, Section: "PARTITION BY Clause". This document notes, "If you do not collect statistics on the partitioning column set for a table partitioned using a CASEN or RANGEN function, the Optimizer assumes that the table has the maximum number of partitions possible for its partition level..."

3. Teradata Vantage™ - SQL Request and Transaction Processing, Release 17.20 (B035-1142-172K), Chapter 11: Optimizer, Section: "Accessing Partitioned Primary Index Tables". This reference confirms, "For partitioning expressions that use the CASEN or RANGEN functions, the Optimizer cannot determine the number of partitions from the table definition. If statistics have not been collected... the Optimizer assumes the maximum number of partitions (65,535 for 2-byte partitioning)."