Restrictions on using materialized query tables with nicknames

When you tune your federated system, consider these restrictions on materialized query tables that reference nicknames should be considered when you are tuning your federated system.

Label-based access control (LBAC) for data source objects

Nicknames for data source objects that use label-based access control or Oracle Label Security cannot be cached, and materialized query tables cannot be created on them.

Materialized query tables

You must to set the types of system-maintained materialized query tables to ALL or USER in one of the following ways:

Use the database configuration parameter DFT_MTTB_TYPES. For example, to set DFT_MTTB_TYPES to the value USER:
```
update db cfg for <dbalias> using DFT_MTTB_TYPES USER
```
Set the current special register. For example, to set the current special register to the value ALL:
```
set current maintained table types for optimization ALL
```

To work around this restriction, you can use user-maintained materialized query tables. For example, for a non-relational nickname named DEPART, you can issue the following commands to simulate a system-maintained materialized query table.

SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION ALL;

CREATE TABLE AST1(C1, C2) 
	AS (SELECT EMPNO, FIRSTNME FROM DEPART WHERE EMPNO>'000000') 
	DATA INITIALLY DEFERRED REFRESH DEFERRED 
	ENABLE QUERY OPTIMIZATION MAINTAINED BY USER;

SET INTEGRITY FOR AST1 ALL IMMEDIATE UNCHECKED;

INSERT INTO AST1 (SELECT EMPNO, FIRSTNME FROM DEPART WHERE EMPNO>'000000');

SET CURRENT REFRESH AGE ANY;

The following SELECT statement can be answered by the materialized query table defined above:

SELECT EMPNO, FIRSTNME FROM DEPART 
	WHERE EMPNO > '000000' AND FIRSTNME LIKE 'AN%';