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.
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.
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
Copyright © 2019 , WhamTech, Inc. All rights reserved. This
document is provided for information purposes only and the contents hereof are
subject to change without notice. Names may be
trademarks of their respective owners.