Indexed Views in the EIQ Product Suite are materialized views with multiple uses:

 

·         They improve query performance for most frequently submitted and resource intensive queries.

·         They provide a flattened view for applications to use.

 

The EIQ Server can use Indexed Views to improve performance by transparently using pre-joined, pre-computed summary data contained in indexed views. These pre-computed summaries are usually smaller than the original source data.

 

Indexed Views are also used to create a virtual schema over a data source that differentiates it from the original schema. For example, a star-schema can be created over a relational data source schema. Indexed Views can also use other views but only up to two levels.

Using Indexed Views

1.    An indexed view is defined using either the EIQ Server RTI Tool or any EIQ Server client by specifying the SQL statement to create the view. The SQL statement can include aggregations, such as SUM, AVG, COUNT(*), COUNT(x), COUNT(DISTINCT), JOINs involving two or more tables, and GROUP BY clauses.

Example: CREATE VIEW as SELECT SUM(age) FROM person

2.    The views are maintained in real-time by EIQ RTIS. It updates EIQ Indexes when the original data changes.

3.    Indexed View tables are queried by EIQ Server clients like any other normal table.

Notes on using Indexed Views

1.    Multi-table JOIN views with virtual keys are supported only from the EIQ Server through the EIQ TurboAdapter configuration.

2.    Updates for Indexed Views are supported up to four levels (Base Table -> Level1View->Level2View->Level3View->Level4View).

3.    Each Indexed View must have a unique ID column generated from each of the tables/views involved in the CREATE VIEW query.

CREATE VIEW View1

AS

(SELECT RowID1, Col1, Col2 FROM

Table1

)

 

To use a view that contains the results of a UNION query from another view, use the CREATE VIEW custom clause WITH IDENTITY as shown below to generate a unique ID column.

CREATE VIEW UnionView1 WITH IDENTITY RowID

AS

SELECT idnumber1, idnumber2, count(*) as cnt FROM

            (SELECT idnumber1, idnumber2

FROM  Table1

UNION ALL

SELECT idnumber1, idnumber2

FROM  Table2

 ) as UnionResultTable1  GROUP BY idnumber1, idnumber2