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 devil }

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.

/// Define an index for <property>Name</property>.
Index NameIDX On Name [ Data = (Name, Home.State, SSN) ];

And being experienced and comfortable with the query generator you know that

SELECT ID, Name, Home_State, SSN   from Sample.Person 

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.

Class Sample.Person.NameView [ ClassType = view
                             ,
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

SELECT Name, BaseClass->DOB, HomeState, SSN,"%CLASSNAME",BaseClass FROM Sample_Person.NameIDX


and here's the class definition and you have to design it manually

/// mapped index
/// 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 !

 

  • 100
  • 1
  • 218
  • 1

Replies

Thank you Robert,

Excellent article.
In fact we use this approach when we need to gain speed for queries that run on hundreds of millions records indexes and need to check few items only, so we save checking the "base" class by using [ DATA ... ]  

In addition, to gain more speed, which is so essential in huge queries, we use the (good, old) $order on the index global itself to gain more speed. This is much faster than normal SQL.