Materialized Views
A VIEW in SQL is basically a prepared SQL statement.
It has to be executed and assembled like any other SQL query.
MATERIALIZED VIEW means that the content is collected before hands and can be retrieved rather fast.
I saw the concept first with my favorite competitor named O* and they made a lot of noise about it.
{ favorite: because I could win every benchmark against them 
}
Depending on the individual construction of this materialized views updates and maintenance might be required.
In Caché / IRIS this exists almost since ever and we take this as normal and a given fact.
Most developers are just not aware of it and with a little bit of polishing it can be presented as an excellent feature.
In addition, any update and maintenance happen as a built-in functionality with no extra effort.
See this example:
In our famous Sample.Person class in namespace SAMPLES I have extended and rebuilt his index.
Index NameIDX On Name [ Data = (Name, Home.State, SSN) ];
And being experienced and comfortable with the query generator you know that
will run fast across just using the index global ^Sample.PersonI("NameIDX")
and never touch you data global.
That's basically the functionality of a Materialized View and update is implicit.
Defined as VIEW (from MgmtPortal as Studio isn't so handy) you get this class.
, CompileAfter = Sample.Person
, DdlAllowed
, Not ProcedureBlock
, SqlTableName = NameView
, ViewQuery = { SELECT ID
, Name
, Home_State
, SSN
from Sample.Person } ]
{ Parameter READONLY = 1; }
But if you want a little bit more comfort like a backlink to your data you can map the index global itself .
So you can apply implicit JOIN syntax and have a fully functional table as here
and here's the class definition and you have to design it manually
/// Index NameIDX On Name [ Data = (Name, Home.State, SSN) ];
Class Sample.Person.NameIDX Extends %Persistent [ Final ]
{
Property IndexName As %String [ InitialExpression = "NameIDX", ReadOnly ];
Property SQLUPPERname As %String [ ReadOnly ];
Property BaseClass As Sample.Person [ ReadOnly ];
Index min On (IndexName, SQLUPPERname, BaseClass) [ IdKey ];
/// Classname of Index Source
Property %CLASSNAME As %String [ ReadOnly ];
/// Person's name.
Property Name As %String [ ReadOnly ];
/// Person's home address. This uses an embedded object.
Property HomeState As %String [ ReadOnly ];
/// Person's Social Security number. This is validated using pattern match.
Property SSN As %String(PATTERN = "3N1""-""2N1""-""4N") [ ReadOnly ];
Parameter READONLY = 1;
Parameter MANAGEDEXTENT As INTEGER = 0;
Storage Default
{
<Data name="NameIDXDefaultData">
<Value name="1">
<Value>%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>Name</Value>
</Value>
<Value name="3">
<Value>HomeState</Value>
</Value>
<Value name="4">
<Value>SSN</Value>
</Value>
</Data>
<DataLocation>^Sample.PersonI</DataLocation>
<DefaultData>NameIDXDefaultData</DefaultData>
<IdLocation>^Sample.Person.NameIDXD</IdLocation>
<IndexLocation>^Sample.Person.NameIDXI</IndexLocation>
<StreamLocation>^Sample.Person.NameIDXS</StreamLocation>
<Type>%Library.CacheStorage</Type>
} }
This is a coding example working on Caché 2018.1.3 and IRIS 2020.2 It will not be kept in sync with new versions It is also NOT serviced by InterSystems Support !