Best Practices for Using Materialized Views in HP Neoview Release 2.4 (R2.4, R2.4 SP1, R2.5)

ManualsBrandsHP ManualsSoftwareHP Neoview Release 2.5 Software

Best Practices for

Using

Materialized Views in

HP Neoview Release 2.4

by Yuval Sherman and Taoufik Ben Abdellatif

Introduction......................................................................................................................................... 2

Background ........................................................................................................................................ 3

Schema organization ....................................................................................................................... 3

Summary tables ............................................................................................................................... 4

Overview............................................................................................................................................ 5

Materialized View REFRESH Policies .................................................................................................. 5

Types of Incremental Materialized Views ............................................................................................ 5

Working with Materialized Views...................................................................................................... 6

Commands and Operations.................................................................................................................. 7

CREATE MATERIALIZED VIEW........................................................................................................... 7

Incremental Materialized View Restrictions........................................................................................ 10

Materialized views automatic clustering............................................................................................ 12

Materialized views defined on top of other Materialized views restrictions............................................ 14

Automatic Log Maintenance ............................................................................................................ 14

ALTER MATERIALIZED VIEW............................................................................................................ 15

DROP MATERIALIZED VIEW............................................................................................................ 16

MVGROUP Commands................................................................................................................... 16

The REFRESH Operation ................................................................................................................. 17

REFRESH Operation Considerations ................................................................................................. 18

New Table Attributes.......................................................................................................................... 20

Utilities ............................................................................................................................................. 21

PURGEDATA ................................................................................................................................. 21

TRANSFORM ................................................................................................................................ 21

Privileges .......................................................................................................................................... 21

Limitations......................................................................................................................................... 22

Summary of content (22 pages)

PAGE 1
Best Practices for Using Materialized Views in HP Neoview Release 2.4 by Yuval Sherman and Taoufik Ben Abdellatif Introduction......................................................................................................................................... 2 Background ........................................................................................................................................ 3 Schema organization ...........................................................................
PAGE 2
Introduction A materialized view is a pre-computed data summary that transparently allows users to query large amounts of data much more quickly than they could access the original data. This summary is stored in a table so that users can query it directly, create indexes on it for performance, use partitioning to promote scalability, and so on. The materialized view is like a cache — a copy of the data that can be accessed quickly.
PAGE 3
Background Materialized views are targeted to improve query performance in two kinds of environments: classical data warehouses (pure DSS), and mixed OLTP/DSS environments (e.g., ODS). The field survey performed during the project analysis stage showed that HP customers mimic the materialized views’ behavior by manual maintenance of summary tables that are actually aggregate MVs.
PAGE 4
performance of actions that would be unacceptable in a highly available environment, for example, locking tables for long periods. Typically, these systems have very stringent requirements on the size of load windows compared to the size of query windows. Summary tables Summary tables are typically a result of aggregation on top of join between the fact table and one or more dimensions. For example, consider a sales report summary: SELECT part.category, time.
PAGE 5
Overview Materialized View REFRESH Policies When an MV is updated incrementally to reflect changes in its base tables, we say that it is incrementally refreshed. Not all materialized views can be incrementally refreshed. MVs that cannot be refreshed incrementally have to be completely recomputed from the base tables and therefore their maintenance costs are relatively high. There are two incremental MV REFRESH modes: ON REQUEST - explicit REFRESH ON STATEMENT - immediate REFRESH.
PAGE 6
CREATE MATERIALIZED VIEW sales_store REFRESH ON REQUEST INITIALIZE ON REFRESH AS SELECT price, partkey, timekey, store.name FROM sales, store WHERE sales.storekey = store.storekey; • Materialized Aggregate View (MAV): A materialized aggregate view holds the result of a GROUP BY query with any of these aggregate functions: SUM, AVG, VARIANCE, STDDEV, MIN, MAX, COUNT(*) and COUNT(x). The aggregate function can include an expression on the columns, such as SUM(a+b). The DISTINCT function is not supported.
PAGE 7
Note that logging introduces a substantial overhead on the IUD (INSERT/UPDATE/DELETE) operation to the base table. For INSERT and DELETE, this overhead can be as large as 90% compared to the same operation when no logging is performed. For UPDATE, in Release 2.4 the performance is substantially enhanced but the overhead can still be as high as 300%.
PAGE 8
CREATE MATERIALIZED VIEW mv-name [column-name-list] {RECOMPUTE | REFRESH refresh-type} INITIALIZE ON initialization-type [file-options] [MVATTRIBUTE COMMIT REFRESH EACH n-rows] AS query-expr column-name-list: ( column-name [ heading] [, column-name [ heading]]...
PAGE 9
The IGNORE CHANGES ON clause can be used in these scenarios: • If users do not want to change the materialized view to reflect changes to one or more tables, they should specify the tables’ names in the IGNORE CHANGES ON clause. Typically, changes to the dimension tables can be ignored to avoid restatement of history. However, new data inserted into the fact table will be joined with the updated dimension tables.
PAGE 10
MVATTRIBUTE COMMIT REFRESH EACH n-rows This materialized view attribute is used to enhance the availability of the MV and its base tables.
PAGE 11
CREATE MATERIALIZED VIEW Cdr_by_phone_month REFRESH ON REQUEST INITIALIZE ON REFRESH AS SELECT area_code, phone_no, mnth, yr, SUM(call_cost) cost, SUM(call_duration) duration FROM (SELECT area_code, phone_no, extract(month from disconnect_time) mnth, extract(year from disconnect_time) yr, call_cost, call_duration FROM Call_Detail) as cdr GROUP BY area_code, phone_no, mnth, yr; MAV on an Explicit Join (MAJV): • ON STATEMENT MAJVs are not supported. • Only inner equi-joins are supported.
PAGE 12
Note that COUNT(expr) is avoided when expr is based only on NOT NULL columns. In this case, COUNT(*) is used instead. MIN and MAX support When the MAX or MIN function is used in a MAV, incremental REFRESH cannot always be guaranteed. This is because if a row that contains the MAX or MIN value of its group is deleted from the base table, a new value cannot be incrementally computed. Sometimes there is a workaround; for example in cases where an even greater value was inserted, it is the new MAX or MIN value.
PAGE 13
Materialized join views (MJVs) are automatically clustered by Neoview. The default clustering key is composed of the clustering key of one of the materialized view base tables and a system generated SYSKEY column. Clustering columns of the base table with the largest size is chosen as the clustering key. MJVs that are defined on a single table and the table has a non-droppable primary key will not include a SYSKEY in their clustering columns and will have a primary key.
PAGE 14
Materialized views defined on top of other Materialized views restrictions Materialized views can use other materialized views as base tables, but not all refresh type combinations are allowed. The restriction on refresh type combination is put in place to ensure materialized view consistency. The following compatibilities are noted: • An ON STATEMENT materialized view cannot be defined on top of an ON REQUEST or a RECOMPUTE materialized view.
PAGE 15
ALTER MATERIALIZED VIEW This statement allows users to rename the MV, modify the COMMIT REFRESH EACH or the IGNORE CHANGES clauses. COMMIT REFRESH EACH This clause is used to enable Multi-transactional REFRESH. specifies the number of rows read from the IUD log in each transaction. The feature is disabled if the value of is set to 0. This option is available only for ON REQUEST self maintainable MVs (e.g. MAV on a single table). The refresh will be broken into short transactions.
PAGE 16
DROP MATERIALIZED VIEW DROP MATERIALIZED VIEW mv-name [CASCADE] This statement drops the MV. Users may not drop an MV that is used by other MVs or by regular views without the CASCADE option, in which case they will be dropped as well. The drop command removes the MV from all the MVGROUPs to which it belongs. It may cause recompilation of SQL statements that use this MV, as well as any of its base tables.
PAGE 17
The REFRESH Operation MAINTAIN MV , REFRESH [CASCADE] MAINTAIN MVGROUP , REFRESH MAINTAIN TABLE , ALL mv-refresh-option : [RECOMPUTE | CANCEL] [{WITH | WITHOUT | DO ONLY} MVLOG CLEANUP] REFRESH is implemented as a utility rather than an SQL command because of its complex multitransactional logic which cannot be implemented in a single dataflow tree. The REFRESH utility can be applied to both incremental MVs and recomputed MVs.
PAGE 18
Note that if an ON REQUEST MV is not refreshed, the data in the log tables of the base table is accumulated and cannot be cleaned. This is also true for ON REQUEST MVs that were created but have not been initialized. REFRESH Operation Considerations Since the REFRESH of a materialized view group may take a long time, the operation is broken into separate transactions, generally one per MV. Successes and failures for each MV are reported to the EMS log.
PAGE 19
Where is the name of the MV source table, since the source table and the IUD log share the sane name. This should be done for every MV source table. Please refer to the Neoview SQL Reference Manual for a more comprehensive description of the MAINTAIN utility. Availability Locking A materialized view is self-maintainable if refreshing it requires that each base table T access data either in T or its log table, but not both. Typically, a self-maintainable MV is an aggregate on a single table.
PAGE 20
Failure Handling REFRESH uses several mechanisms to ensure the consistency of the operation. One is the DDL lock, which is used to disallow any DDL operations on the MV and its base tables for the duration of the REFRESH operation. In case of a failure during a REFRESH, the DDL lock stays in effect and must be handled during recovery. After a failure of the REFRESH operation, users have two options: • Rerun the REFRESH operation. It will recover from the failure and continue where it last failed.
PAGE 21
Once this option is used the MV will no longer be consistent with its base tables, and recomputing it will produce a different result. LOCK and UNLOCK The LOCK TABLE and UNLOCK TABLE commands used to lock and unlock tables and views may also be used on materialized views. Using the LOCK command on an MV locks the MV itself, rather than the underlying tables.
PAGE 22
The INSERT and DELETE privileges on an MV can be granted by the MV’s creator or any user that has a grant option. INSERT and UPDATE commands are blocked on MVs. The DELETE command on RECOMPUTED and ON STATEMENT MVs is blocked. The DELETE privilege on ON REQUEST MV enables the user to perform deletes. Limitations Materialized Views in Neoview Release 2.4 have these limitations: • • A regular view that uses an MV is never updateable.