2.7. Create a Materialized View for Code Table Caching

Procedure 2.1. Create a Materialized View for Code Table Caching

1. Create a view selecting the appropriate columns from the desired table. In general, this view may have an arbitrarily complicated transformation query.
2. Designate the appropriate column(s) as the primary key. Additional indexes can be added if needed.
3. Set the materialized property to true.
4. Add a cache hint to the transformation query. To mimic the behavior of the implicit internal materialized view created by the lookup function, use the Hints and Options /*+ cache(pref_mem) */ to indicate that the table data pages should prefer to remain in memory.