Commit Time Materialized View Maintenance for Bulk Load Operations in Teradata
Arnab Phani, Chandrasekhar Tekur, Ragini Krishna · 2019
Materialized view is called Join Index (JI) in Teradata. JIs can store tables pre-joined, solely change a table to a different schema or aggregation results. JIs are stored permanently on the disks and cannot be peeped through. Also, their usage or evasion isn't subject to user choice. Teradata optimizer decides when to use a JI, or the underlying table(s) directly from a cost point of view. While the advantage of having JIs is to increase read performance, its downside could be their maintenance. In case of row insertion or modification of an indexed column in base table, JI must be updated. This can create a big performance impact if multiple DMLs within same transaction result in same JI(s) maintenance, bulk load operations for example. On tables with Multiversion Concurrency Control (MVCC), readers and writers work concurrently (Load Isolation in Teradata for example). Readers continue to access the last committed rows while concurrent modifications are happening on the base table(s). JIs defined on multiversioned tables also follow the MVCC principle and continue allowing readers to access the last committed rows. Currently, JIs are maintained immediately with every DML request on base table(s). The proposed approach is to defer the JI maintenance till commit time for MVCC tables, resulting in performance improvement for write operations, as all the JI related operations are performed at the end.