SQL Anywhere 11 (Panorama) review guide Part III_Mssql series tutorials

The routine can pass throughHttp://www.sybase.com/files/Technical_Documents/PanoramaRevGuide.zipDownload
In this document, SQL Anywhere 11 may appear elsewhere as SQL Anywhere Panorama.

Real-time Materialized view maintenance

The Materialized view (MV) stores the results queried based on the base table in the database. It is usually used to store the computing results generated by a large number of aggregate queries on the base table. The main advantage of other database objects (such as views and tables) in addition to music videos is its interaction with the optimizer. Materialized view stores the results of high-energy queries and improves performance by performing complex tasks in advance.

In SQL Anywhere 10, when the base table of the view changes, the data in the Materialized view is not the latest data. You need to manually refresh regularly to update the Materialized view content. In addition, when the music video is refreshed, the music video content is deleted and replaced by a new query result set.

With SQL Anywhere 11, the Materialized view can be automatically updated by configuration. This means that when the base table of the MV changes (Insert/Update/Delete), the MV is also updated to reflect these changes. In this case, you do not need to delete the music video or refill the data.

Materialized view routine maintenance
This example demonstrates the real-time maintenance of Materialized views. This feature reduces the workload for developers to use materialized views in applications and improves the ability of the optimizer to use music videos during optimization.

1. Start Interactive SQL and connect to the SQL Anywhere 11 Demo database through ODBC: enable the command line and run the following command:

Dbisql-c "dsn = SQL Anywhere 11 Demo"

2. Create a Materialized view for the total number (quantity and total price) of each sold product under the groupo user based on the size and year, and declare that the view is a manual refresh view. Run the following SQL statement:

Create materialized view groupo. mv_manualrefresh
SELECT year (s. shipdate) AS yr, p. name AS name, p. size AS size,
SUM (s. quantity) AS total_quantity,
SUM (p. unitprice) AS total_price,
COUNT (*) AS num_records
FROM salesorderitems s, products p
WHERE p. id = s. productid
Group by name, size, yr;

Create unique index mvidx ON groupo. mv_manualrefresh (yr, name, size );

Alter materialized view groupo. mv_manualrefresh manual refresh;

3. create another Materialized view for the total number (quantity and total price) of each sold product based on the size and year, but declare the view as Automatically refreshed (using the immediate refresh keyword):

Create materialized view groupo. mv_autorefresh
SELECT year (s. shipdate) AS yr, p. name AS name, p. size AS size,
SUM (s. quantity) AS total_quantity,
SUM (p. unitprice) AS total_price,
COUNT (*) AS num_records
FROM salesorderitems s, products p
WHERE p. id = s. productid
Group by name, size, yr;

Create unique index mvidx ON groupo. mv_autorefresh (yr, name, size );

Alter materialized view groupo. mv_autorefresh immediate refresh;

4. Run the following SQL statement to load the Materialized view:

Refresh materialized view groupo. mv_manualrefresh;

Refresh materialized view groupo. mv_autorefresh;

5. Perform the following query to retrieve data from the view:

SELECT * FROM mv_manualrefresh order by name, size, yr;

<