OceanBase Developer Center (ODC) allows you to create a materialized view on a GUI. This topic describes the creation procedure.
Background
A materialized view is different from a regular view because it stores the data of query results. In other words, when you create a materialized view, the database executes the associated SQL query and stores the result set on the disk. It improves query performance and simplifies complex query logic by precomputing and storing the query result of the view. Materialized views are often used for rapid report generation and data analysis scenarios. Real-time calculation is reduced.
The example below shows how to create a materialized view named test_1 in the ODC console. It combines the id and name columns from the employee table and the alias column from the order table.
All data in this example is for reference only. You can replace the data as needed.
Prerequisites
You have the permission to create materialized views.
If you do not have database permissions, you can click Database Permission Application on the Tickets tab to apply for the query, export, and change permissions.
Procedure
Log on to the SQL console. Expand the details of the target database in the database list on the left. Click the plus sign (+) on the right side of the materialized views to create a new one.
Specify the basic information.
Parameter
Description
Materialized View Name
Specify a name for the materialized view.
Storage Mode
Select Row Store or Column Store.
Refresh Method
Select a refresh method for the materialized view.
Fast refresh: synchronizes only incremental changes made on the base table since the last refresh.
Force refresh: enables the database to automatically determine whether the conditions for a fast refresh are met. If yes, a fast refresh is performed. Otherwise, the refresh is downgraded to a full refresh.
Full refresh: deletes all existing data from the materialized view and re-executes the query to load the latest data from the base table.
No need to refresh: No data update will be performed.
Refresh Parallelism (optional)
Used to control the default parallelism of materialized view refresh operations. By setting its value appropriately, you can significantly improve the refresh efficiency and optimize the database performance.
Auto Refresh
You can choose to enable automatic refresh immediately or at a specified time.
Query Rewrite
When it is enabled, the system will automatically rewrite queries to use existing materialized views when querying the base table.
Real Time
When it is enabled, real-time data will be obtained.
Configure data information.
Select base tables.
Operation
Description
Set an alias
You can set an alias for a selected base table. This operation is optional. To set an alias, click <Alias> next to the table name and enter an alias in the text box.
Set relationships
When two or more tables are selected in the table operation area, you can select their relationships from the drop-down list. The default value is none. You do not need to set the relationship for the last table. To select a relationship, click the drop-down list icon
next to the alias, and then select JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, FULL JOIN, UNION, UNION ALL, INTERSECT, MINUS, LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN. Change the order
You can drag the base tables in the table operation area to reorder them. After each adjustment, the relationship for the last table is cleared.
Delete a base table
You can click the delete icon next to a table in the table operation area to delete the table from the table operation area. After each deletion, the relationship for the last table is cleared.
Select columns.
Operation
Description
Set an alias
You can set an alias for the selected column. This operation is optional. To set an alias, click <Alias> next to the column name and enter an alias in the text box.
Change the order
You can drag the columns in the column operation area to reorder them.
Delete a column
You can click the delete icon next to a column in the column operation area to delete the column.
Add a custom column
You can click +Custom in the upper-right corner of the column operation area to add a custom column. You must specify a name and an alias for the custom column.
Specify constraints.
Operation
Description
Add a constraint
Click the plus sign (+) in the upper-left corner of the constraints tab to add a PRIMARY KEY constraint. Then, select the target column in Column.
Delete a constraint
Select a constraint and click the delete icon in the upper-left corner to delete it.
Configure partitioning.
Operation
Description
Set partitioning rules
Set partitioning rules. The default value is None, which indicates no partitioning. Supported partitioning methods are RANGE, RANGE COLUMNS, LIST, LIST COLUMN, HASH, and KEY. After selecting a method, fill in the corresponding method configuration as needed.
Confirm the SQL statement.
After you complete the preceding settings, click Commit and Confirm SQL Statement to go to the statement editing page.
On the statement editing page, the corresponding materialized view creation statement is generated based on the specified information. If more than two tables are involved, you need to complete the statement based on the relationships between the tables and the logical conditions. After you complete the materialized view creation statement, click Execute in the lower-right corner.
Complete the materialized view creation.
After the materialized view is created, you can view it under the database.
