Example: Preventing Full Table Scans on a Specific Table

Teradata® Viewpoint User Guide

brand
Analytical Ecosystem
prodname
Teradata Viewpoint
Teradata Workload Management
vrm_release
16.20
category
User Guide
featnum
B035-2206-107K
Suppose you want to protect your system from bad queries by preventing users from running full table scans. For example, your company call center logs details about the calls in a call history table, which has grown very large. You want to prevent users from running full table scans on this particular table.
  1. Select a ruleset to edit.
    1. Open the Workload Designer portlet and the Workload Designer view.
    2. From the For System list, select PROD1.
    3. In the local Working section, click the ruleset name, BadQueries.
  2. Create a filter.
    1. From the ruleset toolbar, click Filters.
    2. Next to Filters, click .
    3. Enter the name of the filter, in this case Full Table Scan.
    4. Click Save.
  3. Add classification criteria for the filter to apply to queries that attempt to run full table scans on the table CallHistoryTable.
    1. Click the Classification tab.
    2. From the Add Classification Criteria list, select Target.
    3. Click Add. The Edit Target Criteria dialog box appears.
    4. From the Target Type list, select Table.
    5. From the Database list, select the database that has the call history table: History.
    6. Under Table, type the name of the table you want to prevent full table scans on: CallHistoryTable.
    7. Click Include. The table shows up in the Selected list under Included with the name History.CallHistoryTable.
    8. From the Selected list, click next to History.CallHistoryTable. The Edit Criteria dialog box appears.
    9. Select the criteria Full Table Scan, click Include, and click OK.

    10. Click OK.
    11. Click Save.
  4. Activate the ruleset.
    1. Go back to the Workload Designer view.
    2. For the BadQueries ruleset, click and select Make Active.
  5. [Optional] Review the queries rejected according to the criteria you specified.
    1. Open the Workload Monitor portlet.
    2. Click in the portlet frame and select Settings.
    3. Click the Systems tab.
    4. From the Systems list, select PROD1.
    5. Click OK.
    6. In the Dynamic Pipes View, click on the number below the request status icon for system filters.

    7. Review the summary table to identify the queries rejected by this filter. The Reason column includes For Rule Name 'Full Table Scan'.