- Overview
- Definition
- When to use a materialized view?
- Creating a materialized view
- Overview
- Creation from the data model editor
- Creation by using the SQL code
- Using a materialized view
- Overview
- Rights on a materialized view
- Modifying the content of a materialized view
- Index on a materialized view
- WLanguage functions used to handle a materialized view
Materialized view
 Available only with this kind of connection
Definition A materialized view is a physical data source defined by an SQL query. This materialized view is physically created on the HFSQL server. It can be used like an HFSQL data file by applications that access the database. The application can use or handle a materialized view by using the regular file management functions. The data used is not automatically updated: you must ask to update the data. When to use a materialized view? Using a materialized view presents several benefits: - 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,
- ...
Creating a materialized view Overview Several methods can be used to create a materialized view: - 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 To create a materialized view from the data model editor: - 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.
- In the wizard, select "Create a materialized view" then go to the next screen (arrow at the bottom of wizard).
- 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.
- 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.
- Generate the analysis to use the materialized view in the code of the application: on the "Analysis" tab, in the "Analysis" group, expand "Generation" and select "Generation".
Remarks: - 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 To create a materialized view using SQL code, all you have to do is use the CREATE MATERIALIZED VIEW command in the SQL code of the query. For example:
Qry_View is Data Source
// Create the materialized view
MyMaterializedViewCode is string
MyMaterializedViewCode = [
Create materialized view View_Customer
AS SELECT * FROM Customer
]
HExecuteSQLQuery(Qry_View, HFSQLConnection, MyMaterializedViewCode)
Caution: The SQL code for creating the materialized view must refer to items and data files defined in the analysis only. For example, references to stored queries cannot be used in this code. Using a materialized view Overview To use a materialized view, simply use the HFSQL functions for accessing data files. The <File Name> parameter will correspond to the name of the materialized view. Like a standard data file, a materialized view can be: - browsed using the HFSQL functions (FOR EACH, HRead*, ...),
- associated with a control (Table control, List Box control, ...).
Remark: If the materialized view was not created in the data model editor, HDeclareExternal must be called before it can used. Rights on a materialized view A materialized view corresponds to a data file. Right management is handle the same way as for data files. These rights can be configured: Remark: HInfoViewRights is used to find out the current rights on the materialized view. Modifying the content of a materialized view The content of a materialized view is updated: - 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:
| | | HAddScheduling | Creates a scheduled task on an HFSQL server: - stored procedure,
- backup,
- optimization,
- refresh of materialized view.
| | HDeleteScheduling | Deletes a scheduled task found on an HFSQL server: - stored procedure,
- backup,
- optimization,
- refresh of materialized view.
| | HExecuteScheduling | Immediately runs a scheduled task regardless of its schedule: - stored procedure,
- backup,
- optimization,
- refresh of materialized view.
| | HModifyScheduling | Modifies a scheduled task on an HFSQL server: - stored procedure,
- backup,
- optimization,
- refresh of materialized view.
|
Index on a materialized view To create an index on a materialized view, use CREATE INDEX. If the materialized view is refreshed, the indexes are automatically updated: there is no need to re-create the indexes. WLanguage functions used to handle a materialized view The following WLanguage functions are used to handle the materialized views:
| | | HInfoView | Returns information about a materialized view. | | HRefreshView | Asks to recalculate the content of a materialized view. | | HUpdateView | Creates or updates the description of an SQL view or materialized view from the analysis to an HFSQL server. |
This page is also available for…
|
|
|