Automatic live-aggregate projection refresh responsive to on-demand flattened table refresh

A flattened table (FT) of a database of a database management system (DBMS) is defined. The FT logically materializes a number of columns over a number of partitions. The columns include normalized columns, denormalized columns from a plurality of source tables of the database, as well as an aggregate column defining an aggregation of a selected normalized column over a selected denormalized column. A live-aggregate projection (LAP) is defined on the FT of the database. The LAP corresponds to the aggregate column and physically materializes the aggregation of the selected denormalized column over the selected denormalized column, as defined by the aggregate column. The FT is refreshed on-demand, on a per-column, per-partition basis. Responsive to the FT being refreshed on-demand, the LAP is automatically refreshed.

BACKGROUND

Data is the lifeblood of many entities like business and governmental organizations, as well as individual users. Large-scale storage of data in an organized manner is commonly achieved using databases. Databases are collections of information that are organized for easy access, management, and updating. Data may be stored in tables over rows (i.e., records or tuples) and columns (i.e., fields or attributes). In a relational database, the tables have logical connections, or relationships, with one another, via keys, which facilitates searching, organization, and reporting of the data stored within the tables.

DETAILED DESCRIPTION

As noted in the background, databases store data in tables over rows and columns, where the tables can be interrelated with one another in relational databases. A query is a request for data or information stored in a database in one or more tables. A query may be formulated in a particular query language, such as the structured query language (SQL).

A materialized view (MV) is a database object that contains the results of a query. Unlike a non-materialized view, an MV physically materializes a query in that the query result can be physically stored on a storage device. That is, an MV can store query results in a table-like form. An MV may be a local copy of data that is located remotely, a subset of the rows and/or columns of a table or join result, or may be a summarization of data in aggregate form.

Because MVs are physical materializations, they can become out-of-date and stale when their underlying data changes. Therefore, MVs have to be updated to ensure that they accurately and in an up-to-date manner reflect their underlying data. Such refreshing of an MV is generally controlled in a rule-based manner. Specifically, sometimes an MV may be updated automatically, such as when its underlying data changes or periodically, and at other times an MV may have to be updated on-demand, as triggered by a user.

Knowing when an MV will be updated can be confusing to database administrators. A database administrator may assume that an MV will be updated automatically when its underlying data changes. If this assumption is incorrect, then the MV will not accurately reflect its underlying data. A database administrator may conversely assume that an MV has to be updated on-demand when in fact the MV is updated automatically when its underlying data change. The result in this case is performance degradation, since the on-demand refresh of the MV is unnecessary.

Furthermore, when the underlying data for a column of an MV is updated, the MV may have to have all of its columns updated. That is, the MV as a whole is updated, and not just the columns affected by the update. For example, an MV may physically materialize an aggregation of select columns of one or more tables stored over two partitions. All of the columns of the MV may automatically update even if just one column has in actuality been changed. This means that the MV is needlessly refreshed in its entirety when in fact just select columns have been updated.

Similarly, refreshing the MV on-demand may result in recalculation of the MV over both partitions. However, the rows of just one partition may have been updated. The result in this case is that the processing cost in updating the MV is needlessly large, since the MV in actuality just has to be updated with respect to the rows of the updated partition, and not with respect to the rows of the partition that did not change.

Techniques described herein ameliorate these shortcomings associated with MVs. A flattened table (FT) of a database is defined that logically materializes a number of columns over a number of partitions. The columns can include denormalized columns from a number of source tables, key columns relating the source tables to the FT, columns of the FT itself (i.e., normalized columns), and aggregate columns defining aggregations of selected normalized columns over selected denormalized columns. A live-aggregate projection (LAP) corresponding to an aggregate column can be defined on the FT. The LAP physically materializes the aggregation of the selected normalized column over the selected denormalized column.

FTs, such as those found in the Vertica Analytics Platform (“Vertica”) database available from Vertica Systems, of Cambridge, Mass., which is a subsidiary of Micro Focus International plc, of Newbury, U.K., are refreshed on-demand, and can be refreshed on a per-column, per-partition basis. That is, an FT may not refresh automatically, but rather when an update of the FT is triggered on-demand, and just as to specified columns and partitions. Columns that have not changed, and partitions that remain unchanged, are not updated when the FT is otherwise refreshed on-demand.

LAPs, which are also provided in the Vertica database, by comparison are refreshed automatically, once any underlying data has changed. By permitting LAPs to be defined on FTs, the confusing update rules of MVs are avoided. A database administrator knows that FTs are refreshed on-demand, and that LAPs are refreshed automatically. Therefore, the potential that a physical materialization will be assumed to update automatically (when in fact it will not) or has to be updated on-demand (when it fact it will refresh automatically) is avoided, unlike with MVs.

While currently a LAP can ostensibly be defined on an FT, in practical effect LAPs are not able to be used in conjunction with FTs. That is, currently defining a LAP on an FT results in an error when a column of the FT to which the LAP pertain is attempted to be refreshed on-demand. The net result, therefore, is that LAPs are practically unusable with FTs.

The techniques described herein permit LAPs to be usably defined on FTs, and provide an update mechanism by which such LAPs are updated. An FT is first refreshed on-demand, on a per-column, per-partition basis. A LAP defined on the FT is then responsively refreshed automatically. For instance, non-LAP projections also defined on the FT that relate to the same denormalized column(s) that the LAP projection does may be updated in parallel. As soon as the first such projection has finished updating, the LAP can begin refreshing, even before all projections relating to the denormalized column(s) in question have themselves finished refreshing.

As noted above, unlike MVs, which may have to be refreshed over all columns and partitions, FTs can be refreshed on a per-column, per-partition basis. This in turn means that a LAP defined on an FT is automatically refreshed just if it pertains to an aggregate column defining an aggregation of a normalized column over a selected denormalized column that has changed when the FT was updated on-demand. Performance is thus improved, because an FT can be updated just as to the partition(s) and column(s) that have changed, and also because any LAPs defined on the FT are then responsively automatically updated just if the columns to which they pertain have been updated in the FT.

FIGS. 1A and 1Bshow an example flattened table (FT)100. Among other columns, the FT100includes four columns102A,102B,102C, and102D, collectively referred to as the columns102. There can be fewer than, or more typically, more than four columns102.

As depicted inFIG. 1A, the columns102are denormalized from source tables104and106. Specifically, the columns102A and102B are denormalized from source table104, whereas the columns102C and102D are denormalized from source table106. The source tables104and106typically include more columns than the columns102. Via denormalization of the columns102from the source tables104and106, queries can be run against the data stored over the columns102without having to join the source tables104and106, since the columns102have already been denormalized from the source tables104and106within the FT100.

The columns102are thus referred to as denormalized columns. The FT100further is said to logically materialize the denormalized columns102. The FT100materializes the columns102in that the definition of the FT100specifies which columns102are part of the FT100and the source tables104and106from which the columns102are denormalized. This materialization is a logical materialization because the FT100does not store the denormalized columns102separately from the source tables104and106, such as within a different file(s) or other storage location, however.

The FT100also includes columns103E and103F, which are collectively referred to as the columns103. Unlike the denormalized columns102, which are denormalized from source tables104and106, the columns103are not denormalized from any other table, and store data that can be interrelated to the data of the columns102denormalized from tables104and106. The columns103therefore can be considered normalized columns. There can be fewer than, or more typically, more than two normalized columns103.

The FT100further includes foreign key columns105. The foreign key columns105each store unique keys by which the source tables104and106are related to the FT100. The foreign key column105A relates the FT100to the source table104, whereas the foreign key column105B relates the FT100to the source table106.

For example, the FT100may store sales data of sales of items to customers, the source table104may store item data providing further information regarding each unique item, and the source table106may store customer data provide further information regarding each unique customer. Each sale in the FT100pertains to an item having a particular key in the key column105A, and likewise each item in the source table104has a particular key. Similarly, each sale in the FT100pertains to a customer having a particular key in the key column105B, and likewise each customer in the source table106has a particular key. The key columns105thus permits the source tables104and106to be related to the FT100, and thus have their columns102, along with the normalized columns103of the FT100, joined in a database query.

In this example, the FT100is a fact table, and the sources table104and106are dimension tables. The key in the key column105A that relates the FT100and the source table104is considered a foreign key from the perspective of the FT100, and is considered a primary key from the perspective of the source table104. Similarly, the key in the key column1056that relates the FT100and the source table104is considered a foreign key from the perspective of the FT100, and is considered a primary key from the perspective of the source table106. There may be other source tables that are dimension tables as well, which the FT100as a fact table references by other keys (in other key columns) that are foreign keys of the FT100and primary keys of these other source tables.

In general, the dimension tables (i.e., the source tables104and106) are much smaller in size than the fact table (i.e., the FT100), and provide additional information, or dimensions, for the information contained in the FT100. In the example, multiple sales in the FT100can pertain to the same item in the source table104and/or to the same customer in the source table106. Therefore, the relationship between the fact table and each dimension table can be a many-to-one relationship, or conversely, the relationship between each dimension table and the fact table can be a one-to-many relationship. For instance, each item in the source table104may be referenced by multiple sales in the FT100, and likewise each customer in the source table106may be referenced by multiple sales in the FT100.

As depicted inFIG. 1B, the data stored in the columns102of the FT100as denormalized from the source tables104and106ofFIG. 1A, as well as the data stored in the normalized columns103and105, are stored over storage partitions154A and154B, which are collectively referred to as the storage partitions154. Each storage partition154may include one or more data files, for instance, over which rows of data having values for the columns102A and102B denormalized from the source table104, rows of data having values for the columns102C and102D denormalized from the source table106, and/or rows of data having values for the columns103of the FT100are stored. As such, the source tables104and106are considered to store row data for the denormalized columns102and for the normalized columns103over the partitions154. The partitions154also store row data for the key columns105.

In addition to the denormalized columns102, the normalized columns103, and the key column105, the FT100further includes aggregate columns152C and152D as depicted inFIG. 1B, which are collectively referred to as the aggregate columns152. Each aggregate column152defines an aggregation function of the values of multiple rows that are grouped together to form one or more summary values. Examples of aggregation functions include average, count, maximum, median minimum, mode, range, and sum, for instance. The aggregation functions of the aggregate column152may aggregate values of the denormalized columns102and/or of the normalized columns103, as grouped together by other of the columns102and/or103.

In the example ofFIG. 1B, the aggregate column152E specifies an aggregation function of the normalized column103E over the denormalized column102C, whereas the aggregate column152F specifies an aggregation function of the normalized column103F over the denormalized column102B. Specifically, the aggregate column152E defines an aggregation, such as the summation of the values of the normalized column103E, for each unique value within the denormalized column102C. That is, the aggregate column152E groups the summations of the values of the normalized column103E by the unique values within the denormalized column102C. Similarly, the aggregate column152F defines an aggregation, such as the maximum value of the column103F, for each unique value within the denormalized column102B. That is, the aggregate column152F groups the maximizations of the values of the normalized column103F by the unique values within the denormalized column102B.

The aggregate columns152are defined for each partition154. Therefore, for the data rows stored in the partition154A, the aggregate column152E specifies the aggregation for each unique value for the denormalized column102C stored by these data rows. That is, the aggregate column152E groups the defined aggregation of the normalized column103E of the data rows of the partition154A for each unique value that these data rows store in the denormalized column102C, which is indicated inFIG. 1Bas “Grp By C”156C. The aggregate column152E likewise groups the defined aggregation of the normalized column103E of the data rows of the partition154B for each unique value that these data rows store in the denormalized column102C, as indicated inFIG. 1Bas “Grp By C”158C. Similarly, the aggregate column152F groups the defined aggregation of the normalized column103F of the data rows of the partition154A for each unique value that these data rows stored in the denormalized column102B, which is indicated inFIG. 1Bas “Grp By B”156B. The aggregate column152F groups the defined aggregation of the normalized column103F of the data rows of the partition154B for each unique value that these data rows store in the denormalized column102B, as indicated inFIG. 1Bas “Grp By B”158B.

FIG. 2Ashows example projections202,204, and206defined on the FT100. Each projection202,204, and206corresponds to a selected set of the columns102,103, and105of the FT100. That is, each projection202,204, and206includes one or more of the columns102,103, and105and not any of the aggregate columns152, of the FT100.

The projections202,204, and206physically materialize their respective sets of the columns102,103, and105. Each of the projections202,204, and206materializes its respective set of the columns102,103, and105in that each projection202,204, and206specifies the particular columns102,103, and105that are part of its set of the columns102,103, and105. The materializations are physical materializations in that the projections202,204, and206define where these sets are stored in physical storage, such as a data file. As such, the projections202,204, and206in effect copy the columns102,103, and105as stored over the partitions154, into a new physical storage. That is, the projections202,204, and206store the columns102,103, and105redundantly to the storage of these columns within the partitions154.

In the example ofFIG. 2A, the projection202can be considered a super projection, because it includes all the columns102,103, and105of the FT100. The super projection202can in one implementation include a super projection202A for the records stored in the partition154A and a super projection202B for the records stored in the partition1546. The projection204includes just the denormalized columns102C and102D and the normalized columns103E and103F of the FT100. The projection204does include the key column105B so that the columns102C and102D can be related to the columns103E and103F; this is because the columns102C and102D are denormalized from the source table106, and the FT100is related to the source table106via the key column105B. The projection204can in one implementation span a projection204A for the records stored in the partition154A and a projection204B for the records stored in the partition1546.

The projection206includes just the denormalized columns102A and1026and the normalized column103F of the FT100. The projection206does include the key column105A so that the columns102A and1026can be related to the column103F; this is because the columns102C and102D are denormalized from the source table104, and the FT100is related to the source table106via the key column105A. The projection206can in one implementation span a projection206A for the records of the partition154A and a projection206B of the records of the partition154B. As noted above, in one implementation each projection202,204, and206spans or includes a projection for each partition154. However, in another implementation, each projection202,204, and206may just include a single projection for all the partitions154.

FIG. 2Bshows example live-aggregate projections (LAPs)252and254defined on the FT100. Each LAP252and254corresponds to an aggregate column152of the FT100. The LAPs252and254each physically materialize the aggregation of the normalized column103defined by its corresponding aggregate column152over a corresponding denormalized column102. Each of the LAPs252and254is a materialization in that it specifies the aggregate column152to which the LAP252or254corresponds. Each of the LAPs252and254is a physical materialization in that each LAP252and254define where the aggregation defined by its corresponding aggregate column152is stored in physical storage, such as a data file. That is, the LAPs252and254store their aggregations of the normalized columns103over the denormalized columns103redundantly to the storage of the columns102and103within the partitions154.

In the example ofFIG. 2B, the LAP252physical materializes the aggregation of the normalized column103E over the denormalized column102C, as defined by the aggregate column152E corresponding to the LAP252. Therefore, the LAP252can in one implementation include a LAP252A that materializes the aggregation of the values of the column103E for the unique values of the denormalized column102C of the data records stored in the partition154A. The LAP252similarly can in one implementation include a LAP252B materializing the aggregation of the values of the column103E for the unique values of the denormalized column102C of the data records of the partition154B. In another implementation, the LAP252may just include a single projection for all the partitions154.

In the example ofFIG. 2B, the LAP254physically materializes the aggregation of the normalized column103F over the denormalized column102B, as defined by the aggregate column152F corresponding to the LAP254. Therefore, the LAP254can in one implementation include a LAP254A that materializes the aggregation of the values of the normalized column103F for the unique values of the denormalized column102B of the data records of the partition154A. The LAP254can in one implementation similarly include a LAP254B materializing the aggregation of the values of the column103F for the unique values of denormalized column102B of the data records of the partition154B. In another implementation, the LAP254may include just a single projection for all the partitions154.

The FT100itself is refreshed, or updated, manually on-demand, on a per-column and per-partition basis. This means that when the underlying source tables104and106of the denormalized columns102of the FT100change as stored on the partitions154and156, the FT100is not automatically refreshed. Rather, the FT100has to be manually refreshed, on-demand. Furthermore, the FT100does not have to be refreshed in its entirety. Rather, a particular denormalized column(s)102of the FT100for which data records are stored in a particular partition(s)154of the FT100can be specified. Therefore, just the specified denormalized column(s)102over the specified partition(s)154and156are refreshed.

By comparison, the projections202,204, and206, and the LAPs252and254, defined on the FT100are refreshed automatically, when their underlying data is refreshed. The projections202,204, and206are refreshed automatically when their corresponding denormalized columns102of the FT100have been refreshed on-demand. The automatic refreshing of the projections202,204, and206is a granular refreshing, in that just the projections202,204, and206specifying columns102of the FT100that have been refreshed are refreshed. Furthermore, of these projections202,204, and206, the only columns102that may be refreshed are those that have been refreshed in the FT100itself. Such granular refreshing is further on a per-partition basis, so that refreshing of the projections202,204, and206in question occurs just as to the partitions154and156specified in the on-demand refreshing of the FT100.

Similarly, the LAPs252and254are refreshed automatically when the denormalized columns102defined by the aggregate columns152of the FT100corresponding to the LAPs252and254have been refreshed on-demand within the FT100. As with the automatic refreshing of the projections202,204, and206, the automatic refreshing of the LAPs252and254is a granular refreshing. Just the LAPs252and254corresponding to aggregate columns152defining aggregations of the denormalized columns102that have been refreshed in the FT100itself are refreshed. Granular refreshing of the LAPs252and254is also on a per-partition basis. Refreshing of the LAPs252and254in question occurs just as to the partitions154and156specified in the on-demand refreshing of the FT100.

In the examples ofFIGS. 1A, 1B, 2A, and 2B, then, the FT100is decoupled from the source tables104and106, such that changes in the tables104and106do not automatically propagate to the FT100. Rather, the FT100has to be manually refreshed, on-demand. Such refreshing of the FT100can be performed by querying the source tables104and106on a per-column and per-partition basis, for one or more particular denormalized columns102and one or more particular partitions154. Furthermore, the LAPs252and254are coupled to the denormalized columns102defined in the aggregations of the aggregate columns152to which the LAPs252and254correspond. Changes in the FT100can be automatically propagated to the LAPs252and254through one or more intervening projections202,204, and206that include the denormalized columns102in question.

FIG. 3Ashows example refreshing of the FT100, the projections202,204, and206, and the LAPs252and254. In the example ofFIG. 3A, a user like a database administrator has manually initiated an on-demand refresh of the FT100, particularly specifying the partition154B and the denormalized column102C. This means that the data records of just the partition154B (and not those of the partition154A) are updated with respect to their values for denormalized column102C (and not with respect to their values for any of the other denormalized columns102A,102B, and102D). Updating of just the column102C over the partition1546is denoted inFIG. 3Avia shading.

Responsive to this granular on-demand refresh of the FT100, the projections202,204, and206, and the LAPs252and254, are automatically refreshed. More specifically, those of the projections202,204, and206and those of the LAPs252and254affected by the refreshing of the column102C the FT100over the partition1546are automatically refreshed. As such, just those of the projections202,204, and206including the denormalized column102C are updated. Similarly, just those of the LAPs252and254corresponding to aggregate columns152defining an aggregation of the denormalized column102C are updated.

Therefore, the projections202B and204B are updated, which are the projections202and204for the partition1546. The projections202B and204B are updated just as to the denormalized columns102C—as indicated by shading inFIG. 3A—and not with respect to the other columns102A,1026, and102D,103E and103F, which were not updated in the FT100. The projections202and204for the partition154A—i.e., the projections202A and204A—are not updated, even though the projections202and204include the denormalized column102C, because the on-demand refresh of the FT100was just over the partition1546and not over the partition154A. The projection206is not updated for either partition154A or partition1546, because the projection206does not include the column102C. For instance, the projection206B is not updated, even though it pertains to the partition154B, because the projection206as a whole does not include the denormalized column102C.

The LAP252B is also updated, which is the LAP252for the partition154B, as indicated by shading inFIG. 3A. This is because the LAP252B corresponds to the aggregate column152E over the partition154B (i.e., “Grp By C”158C), and just the aggregate column152E and the partition154B are affected by the on-demand refresh of the denormalized column102C over the partition154B, as denoted inFIG. 3Aby shading. The LAP252for the partition154A—i.e., the LAP252A—is not updated, even though the LAP252corresponds to the aggregate column152E defined on the column103E over the denormalized column102C, because the on-demand refresh of the FT100was just over the partition154B and not over the partition154A. The LAP254is not updated for either partition154A or partition154B, because the LAP254corresponds to the aggregate column152F defined on the column103F over the denormalized column102B, which was not part of the on-demand refresh of the FT100.

FIG. 3Bshows an example process by which the refresh of the FT100, the projections202,204, and206, and the LAP252and254inFIG. 3Amay be performed. The denormalized column102C of the FT100is refreshed over the partition154B on-demand (352). In response to refreshing the FT100, which of the projections202,204, and206and which of the LAPs252and254are affected by refreshing the denormalized column102C over the partition154B is determined, to initiate automatic refresh of the projections202,204, and206and the LAPs252and254(354). As noted above, just the projections202B and204B, and just the LAP252B are affected by the on-demand refresh of the column102C over the partition154.

The automatic refresh of the projections202B and204B and the LAP252B may be performed in parallel, as depicted inFIG. 3B. For instance, separate threads may refresh the projections202B and204B and the LAP252B. The threads may run on different processing cores of the same or different processors, such as central processing units (CPUs) or graphical processing units (GPUs).

Updating of the projection202B is thus initiated (356), as is updating of the projection204B (358). By comparison, updating of the LAP252B waits until the projection202B and/or the projection204B has finished updating (360). In this implementation, the LAPs252are updated from updated projections202,204, and206, as opposed to from the columns102and/or103of the FT100itself. In the example being described, the LAP252B can be updated from the projection202B and/or the projection204B. The LAP252B may be updated from the projection202B or projection204B that finishes updating first. It is not necessary to wait to start updating the LAP252B until both the projections202B and204B have finished updating, since they both include the denormalized column102C that was updated in the manual on-demand refresh of the FT100over the partition154B.

In the example ofFIG. 3B, the projection204B finishes updating first (362). Updating the projection204B can include retrieving the updated data records for the denormalized column102C over the partition154B and storing this updated data. Once the projection204B has finished updating, updating of the LAP252B can thus begin (364), even though the projection202B may not yet have finished updating. In the example ofFIG. 3B, the LAP252B is updated from the projection204B. That is, the aggregation of the denormalized column103F defined by the aggregate column152F is recomputed for the unique values of the denormalized column102C over the partition154B as stored by the projection204B. Updating the LAP252B thus does not involve the projection202B, which has not yet finished updating.

In the example ofFIG. 3B, the projection202B finishes updating (366) before the LAP252finishes updating (368). Updating the projection202B, similar to updating the projection204B, can include retrieving the updated data records for the denormalized column102C over the partition154B and storing this updated data. Once those of the projections202,204, and206and those of the LAPs252and254that are affected by the on-demand refresh of the FT100have been updated—i.e., once all three of the projection202B, the projection204B, and the LAP252B have been updated—the refresh of the projections202,204, and206and the LAPs252and254responsive to the on-demand refresh of the FT100can be committed (370).

FIG. 4shows an example method400. The method400can be performed by a computing system running a database management system (DBMS). For instance, the method400can be implemented as program code stored on a non-transitory computer-readable data storage medium that the computing system executes. The DBMS includes a database, such as a relational database like a Vertica database.

An FT100of the database is defined (402). The FT logically materializes denormalized columns102over multiple partitions154, as well as normalized columns103and key columns105. That is, as noted above, the columns102,103,105, and152include denormalized columns from source tables104and106, key columns105interrelating the FT100to the source tables104and106, normalized columns103storing data within the FT100itself, and aggregate columns152. The aggregate columns152each define an aggregation of one of the columns102and/or103over another of the columns102and/or103.

Projections202,204, and206are defined on the FT100(404). Each projection202,204, and206corresponds to a selected set of the columns102,103, and105, but each such set does not include any of the aggregate columns152. The projections202,204, and206can include a super projection202that corresponds to a set of all the columns102and103. LAPs252and254are also defined on the FT100(406). Each LAP252and254corresponds to one of the aggregate columns152, and physically materializes the aggregation of the column102or103defined by the aggregate column152in question.

The FT100can be refreshed on-demand, on a per-column, per-partition basis (408). For instance, a selected denormalized column102of the FT100may be refreshed over a selected partition154. In response, the projections202,204, and206that include the selected denormalized column102are automatically refreshed (410). If more than one projection202,204, and206is refreshed, they can be refreshed in parallel.

The LAPs252and254that correspond to aggregate columns152including the selected denormalized column102are also responsively refreshed automatically (412). Such LAPs252and254can be refreshed from any of the projections202,204, and206that were refreshed in part410, such as the first such projection202,204, or206that finishes refreshing. As such, the LAPs252and254in question can begin refreshing as soon as the first projection202,204, or206that includes the selected denormalized column102has finished refreshing, prior to other of the projections202,204, and206that include the selected denormalized column102have finished refreshing.

FIG. 5shows an example computer-readable data storage medium700. The computer-readable medium500stores program code502that a computing system on which a DBMS is running can execute. The computing system refreshes on-demand a selected denormalized column102of the FT100with respect to a particular partition154storing row data for this column102(504). The computing system responsively automatically refreshes every projection202,204, and206defined on the FT100that includes the selected denormalized column102(506). Any projection202,204, or206that does not include the selected denormalized column102is thus not automatically refreshed in part506.

The computing system also responsively automatically refreshes every LAP252and254corresponding to an aggregate column152defining an aggregation over the selected denormalized column102(508). As noted above, the automatic refresh of each such LAP252and254can begin as soon as automatic refreshing of any projection202,204, or206including the selected denormalized column102has finished, and can be refreshed from the projection202,204, or206that finishes refreshing first. As such, automatic refresh of the LAPs252and254in question can begin prior to the automatic refresh of other projections202,204, and206that include the selected denormalized column102having finished. Any LAP252or254that does not correspond to an aggregate column152defining an aggregation over the selected denormalized column102is not automatically refreshed in part508.

FIG. 6shows an example computing system600. The computing system600may be a server computing device, or a number of such devices interconnected locally or over the cloud as a distributed computing system. The computing system600may be a massively parallel processing (MPP) computing system. The computing system600includes physical resources602and a DBMS604running on the physical resources602.

The physical resources602of the computing system600can include processor resources606, memory resources606B, and storage resources606C, as well as other types of resources. The processor resources606A can include central-processing units (CPUs) having multiple processing cores, as well as GPU. The memory resources606B can include volatile memory such as dynamic randomly accessible memory (DRAM). The storage resources606C can include non-volatile storage devices like hard disk drives and solid-state drives, and store a database608of the DBMS604.

The computing system600includes DBMS logic610. The logic610is said to be implemented by the physical resources in that they run on the physical resources602of the computing system600. For instance, the logic610may be implemented as program code executed by the processing resources606A from the memory resources606B. In the example ofFIG. 6, the DBMS logic610is depicted as being part of the DBMS604.

The DBMS logic610maintains an FT100of the database608that logically materializes columns102,103,105, and152over the partitions154, including the denormalized columns102, the normalized columns103, the key columns105, and the aggregate columns152(612). The DBMS logic610maintains projections202,204, and206defined on the FT100(614), as well as LAPs252and254defined on the FT100(616). The DBMS logic610, responsive to on-demand automatic refreshing of a selected denormalized column102over a selected partition154, automatically refreshes the projections202,204, and206that include the selected denormalized column102(618). The DBMS610also responsively automatically refreshes the LAPs252and254that correspond to aggregate columns152defining aggregations over the selected denormalized column102, as soon as one of the projections202,204, and206that include this column102has finished refreshing (620).

The techniques that have been described therefore provide for the functionality of an MV while ameliorating the disadvantages associated with MVs. An FT100that logically materializes denormalized columns102from source tables104via key columns105, that includes its own normalized columns103, and that logically materializes aggregate columns152defining aggregations of the columns102and/or103, is refreshed on-demand, on a per-partition and per-partition basis. By comparison, LAPs254that physically materialize these aggregations are refreshed automatically.