When using materialized views it is critical to properly enable query rewrite so that SQL is automatically transformed to use the materialized views instead of the base tables.

The following parameters are required to enable materialized view query rewrite:

When is a materialized view used?

Question:  I have noted that sometimes a full match is not required to use a materialized view,  When are the times when materialized view query rewrite are invoked?  When is SQL query rewrite used?

Answer:  Oracle is very sophisticated in SQL query rewrite capability. The Oracle DBA can control the propensity of the SQL optimizer to go to the materialized views to service the query. The options are as follows:

• Full SQL text match - In this method, the SQL text of the query's select statement clause is compared to the SQL text of the select clause in the materialized view's defining query.

• Partial text match - If a full SQL test match fails, the optimizer will attempt a partial SQL text match. The optimizer compares the remaining SQL text of the query (beginning with the from clause) to the remaining SQL text of the materialized view's defining query.

• No match - If the full and partial SQL text matches both fail, the optimizer uses general query rewrite methods that enable the use of a materialized view even if it contains only part of the data, more than the data, or data that can be converted.

• CHECK constraint - This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of REGION are North, South, East, or West, a CHECK constraint can be added to the table definition to ensure the validity of the region column.

• NOT NULL constraint - This constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL INSERT and UPDATE time.
   

• PRIMARY KEY constraint - This constraint is used to identify the primary key for a table. This operation requires that the primary column(s) is unique, and Oracle will create a unique index on the target primary key.
   

• FOREIGN KEY constraint - This is the foreign-key constraint as implemented by Oracle. A foreign key constraint is only applied at SQL INSERT and DELETE times. For example, assume a one-to-many relationship between the CUSTOMER and ORDER tables such that each CUSTOMER may place many ORDERs, yet each ORDER belongs to only one CUSTOMER. The REFERENCES constraint tells Oracle at INSERT time that the value in ORDER.cust_num must match the CUSTOMER.cust_num in the customer row, thereby ensuring that a valid customer exists before the order row is added. At SQL DELETE time, the REFERENCES constraint can be used to ensure that a CUSTOMER is not deleted if rows still exist in the ORDER table.
   

• UNIQUE constraint - This constraint is used to ensure that all column values within a table never contain a duplicate entry.

Note the distinction between the UNIQUE and PRIMARY KEY constraint. While both of these constraints create a unique index, a table may only contain one PRIMARY KEY constraint column--but it may have many UNIQUE constraints on other columns.

Here are my notes on materialized views query rewrite: