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,
  • data binding,
  • 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" tab, in the "Creation" group, expand "New" and select "View".
   • On the "Analysis" pane, select the "Views" folder then select the "New view" option of context 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 context 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 view materialized in the application code: under the "Analysis" pane, in the "Analysis" group, pull down "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

Req_Vue is Data Source

// Création de la vue matérialisée
MonCodeVueMatérialisée is string
MonCodeVueMatérialisée = [
	Create materialized view Vue_Client
	AS SELECT * FROM Client
]
HExecuteSQLQuery(Req_Vue, ConnexionHFSQL, MonCodeVueMatérialisée)

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.
• via HModifyViewRights.

Modifying the content of a materialized view

• via the SQL "REFRESH MATERIALIZED VIEW" statement.
• via HRefreshView.
• via a refresh scheduled task.

WLanguage functions used to handle a materialized view

Overview

• via the HFSQL Control Center.
• programmatically.

Scheduled refresh of a materialized view in the HFSQL Control Center

Create a refresh task for a view materialized by programming

1. Create a variable of type hScheduleMaterializedView and describe its various characteristics.
2. Add planned optimization using HAddScheduling.

New in version 2025
An execution history is automatically recorded when the refresh task is automatically executed (or when it is executed using the HExecuteScheduling function). This history can be manipulated:

• via the HFSQL Control Center. When the scheduled task has been executed, the "Execution history" tab is available in the scheduled item description window. This tab allows you to view all the characteristics of the planned element's executions.
• by programming, using the following functions:

  New in version 2025 HClearSchedulingHistory	Clears the execution history of scheduled jobs on an HFSQL server.
  New in version 2025 HDeleteSchedulingHistory	Deletes the executions of a scheduled job on an HFSQL server. This job can be a scheduled task (stored procedure), backup, optimization or a materialized view refresh.
  New in version 2025 HListSchedulingHistory	Lists the executions of a scheduled job on an HFSQL server. This job can be a scheduled task (stored procedure), backup, optimization or a materialized view refresh.