Materialized view tuning and usability enhancement

A system for automated materialized view creation comprising:

• a DDL validation system for examining an input query and determining if the query can be tuned;
• an SQL analyzer system that receives a tunable input query from the DDL Validation system, analyzes the defined query and divides the defined query into one or more sub-queries, each sub-query being used as a defined query of a sub-materialized view;
• a materialized view log analyzer system that compares a create statement generated by the SQL analyzer to base tables in the materialized view logs and creates materialized view logs if the logs do not exist and are required for the create statement;
• and
• a query rewrite system to generate a modified defined query to replace the original defined query in terms of sub-materialized views.

A materialized view tuning process:

• generating a first output for a create process;
• generating a second output for a drop process;
• and
• recording the first and second output in advisor repository table and providing access to the first and second output through catalog views.