ASA SQL User's Guide
Query Optimization and Execution
Query execution algorithms
Miscellaneous
Filters apply search conditions. The search conditions appear in the statement in the WHERE and HAVING clauses and in the ON conditions of JOINS in the FROM clause.
Pre-filter is the same as filter, except that it doesn't depend on input. For example, in the case of WHERE 1 = 2 a pre-filter applies.
A hash-filter can be used when an execution plan fetches all of the rows of one table before fetching any rows from another table that joins to it. The hash filter builds an array of bits, and turns one bit on for each row of the first table that matches search conditions on that table. When accessing the second table, rows that could not possibly join with the first table are rejected.
For example, consider the plan:
R<idx> *JH S<seq> JH* T<idx>
Here we are joining R to S and T. We will have read all of the rows of R before reading any row from T, and we can immediately reject rows of T that can not possibly join with R. This reduces the number of rows that must be stored in the second hash join.
For more information, see The WHERE clause: specifying rows.