Definition

When to use a materialized view?

• Independence with respect to the physical structure of the data:
  The materialized view corresponds to an abstraction layer between the data structure and the data use. If the physical structure of data evolves, the application code does not change. Only the base query of view must be adapted to the new structure.
• Process acceleration
  Using a materialized view is interesting if extracting data from the view is a long process and if the data does not have to be real time. For example:
  • statistics,
  • reporting,
  • databinding,
  • selections for mailshots,
  • targeting,
  • creating "flat" data files,
  • ...

Overview

• Create the materialized view from the data model editor. In this case, to use the materialized view, the data must be updated on the server. This update can be performed:
  • via the automatic data modification (performed when installing the application for example).
  • via HUpdateView.
• Creating the materialized view by using the SQL "CREATE MATERIALIZED VIEW" code in the application.

Creation from the data model editor

1. Start the view creation wizard:
   • In the ribbon, on the "Analysis" pane, in the "Creation" group, expand "New" and select "View".
   • On the "Analysis" pane, select the "Views" folder then select the "New view" option of popup menu.
2. In the wizard, select "Create a materialized view" then go to the next screen (arrow at the bottom of wizard).
3. Specify the name of the view to create then select the edit mode of base query for the view:
   • Wizard. In this case, the query of the view is created via the wizard for query creation. The query is displayed graphically. The query can be modified at any time via the wizard.
   • SQL code. In this case, you directly enter the SQL code for selecting the elements selected by the view.
4. The materialized view is created. By default, it is not associated with a connection. To associate the view with a connection defined in the data model editor:
   • On the "Analysis" pane, select the materialized view that was just created.
   • In the popup menu (right mouse click), select "Description of the view".
   • In the window that is displayed, you can modify the name of the materialized view and specify the connection associated with the materialized view.
   • Validate.
5. Generate the analysis to use the materialized view in the code of the application: on the "Analysis" pane, in the "Analysis" group, expand "Generation" and select "Generation".

• A key or a composite key will be created based on the "ORDER BY" statement.
• The materialized views cannot contain parameters.

Creation by using the SQL code

Overview

• browsed using the HFSQL functions (FOR EACH, HRead*, ...),
• associated with a control (Table control, List Box control, ...).

Rights on a materialized view

• via the HFSQL Control Center (not available in this version). via the HFSQL Control Center.
• via HModifyViewRights.

Modifying the content of a materialized view

• via the SQL "REFRESH MATERIALIZED VIEW" statement.
• via HRefreshView.
• via a refresh scheduled task. This task is defined via a hScheduleMaterializedView variable. Then, this task can be handled via the following functions:

  Versions 20 and later HAddScheduling New in version 20 HAddScheduling HAddScheduling	Creates a scheduled task on a HFSQL server: stored procedure, backup, optimization, refresh of materialized view.
  Versions 20 and later HDeleteScheduling New in version 20 HDeleteScheduling HDeleteScheduling	Deletes a scheduled task found on a HFSQL server: stored procedure, backup, optimization, refresh of materialized view.
  Versions 20 and later HExecuteScheduling New in version 20 HExecuteScheduling HExecuteScheduling	Immediately runs a scheduled task regardless of its schedule: stored procedure, backup, optimization, refresh of materialized view.
  Versions 20 and later HModifyScheduling New in version 20 HModifyScheduling HModifyScheduling	Modifies a scheduled task on a HFSQL server: stored procedure, backup, optimization, refresh of materialized view.