Patent ID: 6735587
Filing Date: 2004-05-11
Classification: G06F,Y10S

Abstract:
A computer-implemented method for maintaining pre-computed aggregate views, comprising:(A) receiving a pre-computed aggregate view V derived from one or more base tables, the pre-computed aggregate view V including a pre-computed aggregate table MV and a view definition VD, wherein g1, . . . , gm are grouping columns of VD, and a1, . . . , an are aggregation expressions; (B) receiving changes to the one or more base tables, the changes being non-minimal; (C) defining result set &dgr;F as {&dgr;+FÃ—{x}}{&dgr;âˆ’FÃ—{y}}, where x and y are some arbitrary but distinct literals of the same type,  is duplicate preserving union, and x is a duplicate preserving cross product, wherein F is a table in a FROM clause of the view definition VD and &dgr;+F and &dgr;âˆ’F are changes to F representing inserted and deleted rows, respectively, of the received changes, and wherein result set &dgr;F contains all the inserted and deleted rows, with an additional column to distinguish the inserted and deleted rows; (D) defining result set &dgr;G as Vâ€²D(&dgr;F), wherein Vâ€²D is obtained by modifying VD by replacing F in VD with &dgr;F and replacing each aggregation expression ai, in VD, with three functions a+i, aâˆ’i, and adi defined as follows: (i) if ai is count, then a+i is count(case when f=x then z else NULL end), and aâˆ’i is count(case when f=y then z else NULL end) where z is expr if the aggregation expression is count(expr) and is some non-null literal, otherwise (i.e., if it is count(*)), (ii) if ai is sum(expr), then a+i is sum(case when f=x then expr else NULL end), and aâˆ’i is sum(case when f=y then expr else NULL end), (iii) adi is defined as follows: adi=a+iâˆ’aâˆ’i, if ai is count =case when isnull(aâˆ’i) then a+i â€ƒwhen isnull(aâˆ’i) then a+i â€ƒelse a+iâˆ’aâˆ’i end, if a is sum, wherein a+i represents contributions from changes that are insertions and aâˆ’i represents contribution from changes that are deletions; (E) defining result set &dgr;J as &dgr;GpMV, wherein the outerjoin condition, p, is: &dgr;F.g1N=MV.g1, . . . , &dgr;F.gmN=MV.gm., and wherein the=operator is optionally replaced by a regular equality=operator for each grouping column g, that is not nullable; (F) defining result set &dgr;Jâ€² as &sgr;p(&dgr;J), wherein p is (isnotnull(MV.rowid))(c+>câˆ’) and MV.rowid is the rowid, which can be any non-nullable column of MV in the result set &dgr;J, and wherein c denotes one of the count(*) or count( ) expressions and c+ and câˆ’ denote the corresponding a+i and aâˆ’i expressions described in (D)(i) and (D)(ii); (G) defining result set &dgr;I as Ip,m(MV, &dgr;Jâ€²), wherein p is isnull(MV.rowid) and a mapping m of expressions from source columns of &dgr;Jâ€² to columns of a target of the insert, MV is as follows: each grouping column from the &dgr;G component of &dgr;Jâ€² maps to the corresponding grouping column in MV, wherein for each aggregation expression ai in VD, a corresponding adi column from a &dgr;G component of &dgr;Jâ€² maps to a corresponding aggregation column in MV, and wherein the reference to MV.rowid in the predicate p is to the MV.rowid column in the input &dgr;Jâ€²; (H) defining result set &dgr;D as DMV.rowid,p(MV, &dgr;I) wherein p is câˆ’=MV.c+c+; and (I) defining result set &dgr;U as UMV.rowid,TRUE,m(MV, &dgr;D), wherein a mapping m of source expressions to target columns is as follows: each grouping column gi in &dgr;D maps to a corresponding grouping column in MV, and for each aggregation expression ai in VD, a corresponding uai, maps to a corresponding aggregation column ai in MV, and wherein uai is as follows: uai=MV.ai+a+iâˆ’aâˆ’i, if ai is count(expr) or count(*), uai=null-sub(null-add(MV.ai,a+i), aâˆ’i) if ai is sum(expr).