Create a materialized view

OceanBase Developer Center (ODC) supports the visual creation of materialized views. This topic describes how to create a materialized view in ODC.

Background information

Unlike a regular view, a materialized view stores the results of the query. In short, when you create a materialized view, the database executes the associated SQL query and stores the result set on disk. This approach reduces real-time computation by precomputing and storing the query results, thereby improving query performance and simplifying complex query logic. It is commonly used in scenarios such as rapid report generation and data analysis.

This topic describes how to create a materialized view named test_1 in ODC. It also explains how to combine the id and name fields from the employee table and the alias field from the order table into the test_1 materialized view.

Prerequisites

You have the permission to create a materialized view.

Procedure

1. Log in to the SQL console, click the target database in the left-side database list, and click the + sign next to the materialized view to create a materialized view.

2. Specify the basic information.

   Parameter	Description
   Materialized View Name	The name of the materialized view.
   Storage Mode	The storage mode. Valid values: Row and Column.
   Refresh Method	The refresh method. Valid values: Fast Refresh: Only the incremental changes in the base table since the last refresh are synchronized. Force Refresh: The database automatically determines whether the fast refresh conditions are met. If the conditions are met, the fast refresh is executed. Otherwise, the complete refresh is executed. Complete Refresh: All existing data in the materialized view is deleted, and the definition query is executed again to load the latest data from the base table. No Refresh: No data is updated.
   Refresh Parallelism (Optional)	The default parallelism for the refresh of the materialized view. By setting an appropriate value, you can significantly improve the refresh efficiency and optimize the database performance.
   Auto Refresh	Specifies whether to enable auto refresh immediately or at a specified time.
   Query Rewrite	If this option is enabled, the system automatically rewrites queries on the base table to use the existing materialized view.
   Real-time	If this option is enabled, real-time data is obtained.

3. Configure the data information.

   1. Select the base table.

      Parameter	Description
      Set Alias	The alias of the selected base table. This is an optional parameter. Click the <Alias> label after the table name in the Table Operations section and enter the alias in the text box.
      Set Relationship	When two or more tables are displayed in the Table Operations section, you can select the relationship between the tables from the drop-down list. The default value is empty. The relationship of the last table does not need to be set. Click the drop-down list after the alias label to select a relationship. Valid values: JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, FULL JOIN, UNION, UNION ALL, INTERSECT, MINUS, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
      Adjust Relationship Order	You can directly drag the selected base table in the Table Operations section to adjust the order. The relationship of the last table will be cleared after each adjustment.
      Delete Base Table	Click the delete button after each table in the Table Operations section to delete the table. The relationship of the last table will be cleared after each deletion.

   2. Select the column.

      Parameter	Description
      Set Alias	The alias of the selected column. This is an optional parameter. Click the <Alias> label after the field name in the Selected Columns section and enter the alias in the text box.
      Adjust Relationship Order	You can directly drag the selected column in the Selected Columns section to adjust the order.
      Delete Field	Click the delete button to the right of each column in the Selected Columns section to delete the column.
      Custom Column	Click +Custom in the upper-right corner of the Selected Columns section to add a custom column. You need to specify the name and alias for the new column.

   3. Specify the constraint.

      Parameter	Description
      Add Constraint	Click the + icon in the upper-left corner of the Constraint tab to add a primary key constraint. Select the target column in the Column field.
      Delete Constraint	After you select the constraint content, click the delete button in the upper-left corner.

   4. Select the partition.

      Parameter	Description
      Set Partitioning Rule	The partitioning rule. The default value is None (no partitioning). Valid values: Range, Range Columns, List, List Column, Hash, and Key. After you select a method, you need to specify the corresponding parameters.

4. Confirm the SQL statement.

   After you specify all the information, click Commit and Confirm SQL Statement. in the upper-right corner of the page to go to the SQL confirmation page.

   The system generates a materialized view definition statement based on the specified information. If the statement contains two or more tables, you need to add the relationships and logical conditions between the tables. After you complete the materialized view definition statement, click Execute in the lower-right corner of the page.

5. Create the materialized view.

   After the materialized view is created, you can view it in the database.

References

• Create a table
• Create a view
• Create a function
• Create a stored procedure
• Create a sequence
• Create a program package
• Create a trigger
• Create a type
• Create a synonym