When should I create a View and when should I create a Materialised Query Table?

I get the idea that Materialised Query Tables are more intended for performance but would like more specifics.

Thanks.

Hi,

it depends on the view and what you want to achieve with it.
If the view just select a subset of data (e.g. select * from cars where color='red'), you should stick with the view. Also if the view is implemented to ease implementation of query (e.g. often used simple joins).

If the view is used by many queries and includes some expensive aggregations, you should consider to switch it into a MQT. It also depends on how often the underlying data changes and the need of updating the data in the MQT.

hth

Views are best used when you need to restrict different users/groups for multiple numbers of limited view of the entire table. E.g. From the Employee table, you want to allow all employees to view details about all others employees' Names and Department, but want to allow only the Senior Managers to view the entire detail of the employees including wages, address, band etc. So you will allow the employee group with SELECT access on view V_EMP_Contact and will grant SELECT access to group created for the managers on view V_EMP_DETAILs which has got the entire details for all employees ( from the EMPLOYEE table). There are many other uses of Views as well like your table contains too many fields, whereas almost all the user queries are interested only in a subset of the same.

For Materialized query tables, as you correctly mentioned, they are created for performance primarily and are physical copies in the form of a table of the query output that is embedded in the definition of the MQT. There are lot many white papers and redbooks available on MQTs in the net.