Filter Columns

Filter columns are an essential component of subquery materialized views. A filter column must be defined in a materialized view log that is supporting a materialized view that references a column in a WHERE clause and is not part of the equi-join.

Consider the following DDL:

1. CREATE MATERIALIZED VIEW sales.orders AS

2. SELECT * FROM sales.orders@dbs1.acme.com o

3. WHERE EXISTS

4. (SELECT c_id FROM sales.customers@dbs1.acme.com c

5. WHERE o.c_id = c.c_id AND zip = 19555);

Notice that in line 5 three columns are referenced in the WHERE clause. Columns O.C_ID and C.C_ID are referenced as part of the equi-join clause; the column ZIP is an additional filter column. You will therefore need to create a filter column in the materialized view log for the ZIP column of the SALES.CUSTOMERS table.

You are encouraged to analyze the defining queries of your planned materialized views and identify which filter columns will need to be created in your materialized view logs. If you try to create or refresh a materialized view that requires a filter column before creating the materialized view log containing the filter column, your materialized view creation or refresh may fail.