It’s taken me a while to warm up to the idea that rejecting queries can be good. I’ve always subscribed to the hippocratic oath of workload management: “Above all, do no harm to a query.” Yet lately I’m seeing more and more Teradata DBAs grimly, yet firmly, embracing filter rules as a new weapon in their arsenal. And they’re starting to like it.
Where is this lethal trend coming from?
Not to leave any reader behind, a filter is a workload management rule that will evaluate and potentially reject a query before the query begins execution, based on specific characteristics of the query. The filter check is made after the optimizer builds the query plan but before the first query step is dispatched to the AMPs, so all the detail within the query plan is available to the code that enforces the filter. The tables to be accessed, what join geography will be used, and the time and row count estimates for the query are known by the filter rule.
Filters are easy to define. They can be used with or without TASM. As one example, you could create a filter to reject all queries that try to do a full table scan of your CDR table during 8 AM to 5 PM, Monday through Friday.
What I think has brought out this new ruthlessness on the part of some Teradata DBAs is the fairly recent ability to create a filter rule that will reject an unconstrained product join, but only when the join involves a very high number of rows (1 billion, for example). Defining a filter that combines the type of join with estimated row counts gives DBAs the ability to stop badly-written queries before they start eating up database resources, instead of after. It’s like pulling weeds out of your garden before they take root.
Plus, my sources tell me that non-trivial levels of CPU processing power are being conserved on their platforms since they activated such filter rules, and end-user queries are experiencing less contention for resources as a result. This means more conventional queries may be able to run and complete sooner, and a greater number of new applications might be supportable on the same hardware.
So, if like me, you squirm at the thought of having to make life or death decisions about a query before it gets a chance to run, get over it. The rewards may be larger than you think.