Mysql – Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent

No, you do not need to enumerate the columns used in a materialized view when creating the materialized view log. In fact you cannot create a materialized view log using the primary key method and include all the columns because you would be including the primary key column itself, which is not allowed.

The concept of a materialized view log is to store the rowid or primary key of the data that changed. The refresh can then look up the entire record from the table. Adding specific columns to the WITH clause of the log explicitly records the data in the materialized view log itself. If your materialized view query is filtering on these columns or joining on them it could speed up the refresh.

Theoretically if all the columns used in the MV are in the log, then it should be able to refresh without referencing the table. The documentation does not indicate that this is being done. It would be interesting to trace this to see. Even if it does, the additional storage requirements may not make this route worth the trouble.

You should probably just create the materialized view logs without specifying a column list, like this:

CREATE MATERIALIZED VIEW LOG ON SCHEMA.TABLE_A 
   WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;

More Information:

• SQL Snippets
• Oracle® Database SQL Language Reference
• Oracle® Database Data Warehousing Guide

You are correct you cannot use indexed views. Sadly what you have here is a business problem not a database problem. If you need to know the PKs and indexes from the other database and you don't have even viewing access, then require the DBAs who do have that access to provide the information you need to do your job or require them to write and performance tune the queries. No one should be writing queries against a database they do not have access to. If they won't give you access, then the work belongs to them too. I would have my boss talk to their boss and ask them to solve this set of problems for you. They created the problem it's up to them to fix it.