Allow materialized views that exist only on copy nodes

[Breck_Carter]

This request was made by an attendee at Anil Goel's Techwave 2010 presentation "Improving Performance with SQL Anywhere Materialized Views":

Copy nodes are read-only databases available as part of SQL Anywhere Version 12's "Read-only scale-out" feature.

These databases may be used for reporting purposes, and materialized views are often very useful for reporting. Rather than defining the materialized views on the primary database where they might never be used, it would be great to define them ONLY on the copy nodes where they would be of some use. That way, the existence of "too many, too aggressive" materialized views would not affect performance on the primary database, and may in fact be tolerated on the reporting databases.

At least two people in the audience thought this was a great idea.

[Former Member]

makes a load of sense. I'm wondering how one could have a generic system that allowed one to transparently off load read only queries to a read only server if it existed, but would behave OK if it didn't. Our user base varies from installs with 100s users to single user systems and ideally one wants a common approach.

[Former Member · Accepted solution]

This is an interesting idea, and one we have thought of - not only for materialized views, but also (for example) secondary indexes - there is every possibility that the indexes required for read-only query processing on copy nodes may be different from the set of indexes required on the primary, read/write database.

We'll consider this for a future release.