Sign up to receive free email alerts when patent applications with chosen keywords are publishedSIGN UP

Abstract:

Methods and systems for using algorithms in relational database
management systems (RDBMSs) for incremental maintenance of materialized
views with outerjoins are disclosed. The algorithms achieve the following
goals with respect to a class of materialized outerjoin views and the
performance of update operations: relax the requirement for the existence
of the primary key attributes in a select list of the view to only some
of the relations (i.e., the relations referenced as a preserved side in
an outerjoin); relax null-intolerant property requirements for some
predicates used in the view definition (i.e., predicates referencing
relations which can be null-supplied by more than one outerjoin); and
implement maintenance of outerjoin views by using one update statement
(e.g., MERGE, UPDATE, INSERT, or DELETE) per view for each relation
referenced in the view. The algorithms allow design and implementation of
the incremental maintenance of materialized views with outerjoins to be
integrated into an RDBMS.

Claims:

1. A computer implemented method for maintaining immediate materialized
outerjoin views, comprising: creating, using a computing device, a before
image of a base table of a database; executing, by the computing device,
an update statement on the base table; creating, by the computing device,
an after image of the base table, wherein the after image indicates
updated rows of the base table; computing, by the computing device, a
delta of the base table, wherein the computing comprises comparing the
before image to the after image; computing, by the computing device, a
view delta of a previously defined immediate materialized view (iMV)
based on the computed delta of the base table; and updating, by the
computing device, the iMV using the computed delta of the iMV to reflect
changes in the delta of the base table.

2. The computer implemented method of claim 1, wherein properties of the
iMV comprise null-tolerant properties for a subset of predicates used to
define the iMV.

3. The computer implemented method of claim 2, wherein the subset of
predicates reference relations which can be null-supplied by outerjoins.

4. A computer-based system for optimizing data updates in a database,
comprising: a table update module configured to update a base table of
the database, wherein the base table is referenced by one or more
immediate materialized views (iMVs); a trigger determining module
configured to determine whether an immediate materialized view (iMV)
trigger exists for the base table; a trigger creation module configured
to create an iMV trigger in response to determining, by the trigger
determining module, that an iMV trigger does not exist for the base
table; and a trigger execution module configured to execute the iMV
trigger, wherein execution of the iMV trigger updates each iMV
referencing the base table.

5. An article of manufacture including a computer-readable medium having
instructions stored thereon that, if executed by a computing device,
cause said computing device to perform operations comprising: identifying
immediate materialized views (iMVs) referencing a base table; generating
an update statement for each identified iMV, wherein the update
statement, upon execution by the database, merges a delta of an iMV into
a corresponding, identified iMV; creating a trigger for the base table,
wherein the trigger, upon execution by the database, updates identified
iMVs to reflect changes in the base table; and saving a trigger for iMVs
updates.

6. The article of manufacture of claim 5, wherein the generating an
update statement comprises generating an update statement for maintaining
an outerjoin view.

7. The article of manufacture of claim 6, wherein the maintaining is
implemented by generating one update statement for each iMV referencing
the base table.

8. The article of manufacture of claim 7, wherein the base table is a
base table of a database, and wherein the one update statement is a
structured query language (SQL) MERGE, INSERT, UPDATE, or DELETE
statement.

9. A method for creating an internal representation of an immediate
materialized outerjoin view, comprising: determining whether a previously
defined immediate materialized view (iMV) references a base table in a
database; in response to determining that an iMV references the base
table, building a normalized join operator tree, wherein the tree is
based upon a definition of the iMV; determining whether the tree
satisfies predicate conditions; in response to determining that the tree
satisfies the predicate conditions, performing a Preserved Side/Null
Supplied Side (PSNS) annotation of the tree to create an annotated tree;
determining whether the annotated tree satisfies select list conditions
of the iMV; in response to determining that the annotated tree satisfies
the select list conditions, generating an update statement for the iMV
after an update of the base table from the annotated tree.

10. The method of claim 9, wherein the database is a relational database.

11. The method of claim 10, further comprising: forming an internal
representation of the iMV using the annotated tree; integrating the
internal representation with a query optimizer of a relational database
management system (RDBMS); and wherein the generating further comprises:
generating exactly one structured query language (SQL) UPDATE statement
for the iMV to be used in response to detecting that the base table is
updated, wherein the exactly one UPDATE statement relaxes restrictions
imposed by the definition of the iMV by: not requiring intermediate
temporary tables for updating not-null-supplied tuples and null-supplied
tuples; and computing not-null-supplied tuples in the same time to
null-supplied tuples for updating the iMV.

13. The method of claim 10, wherein the update statement is a MERGE,
INSERT, UPDATE, or DELETE statement.

14. The method of claim 9, wherein the select list conditions of the iMV
do not require a primary key.

15. The method of claim 14, wherein the select list conditions of the iMV
do not require a primary key for each relation referenced in the
outerjoin view.

16. The method of claim 15, wherein the select list conditions of the iMV
include a subset of relations having primary keys.

17. The method of claim 16, wherein the select list conditions of the iMV
include a subset of relations that must have primary keys.

18. The method of claim 17, wherein the subset of relations comprises
relations referenced as a preserved side in an outerjoin.

19. The method of claim 9, wherein the update of the base table is based
on a computed delta of the base table.

20. The method of claim 19, wherein the delta of the base table is
computed based upon a comparison of a before image of the base table to
an after image of the base table.

Description:

CROSS-REFERENCE TO RELATED APPLICATIONS

[0001] The present applications claims the benefit of U.S. provisional
application No. 61/371,413, filed on Aug. 6, 2010, and entitled
"Incremental Maintenance of Immediate Materialized Views with Outerjoins"
(Atty. Dkt. No. 1933.1110000), which is incorporated herein by reference
in its entirety.

FIELD OF THE INVENTION

[0002] The present invention is generally directed to database management
systems, and, more specifically, to database management systems providing
materialized views, incremental maintenance of materialized views and in
particular directed to a system and methodology for maintenance of
materialized views containing outerjoins.

BACKGROUND OF THE INVENTION

[0003] SYBASE® SQL ANYWHERE® is an ANSI SQL-compliant relational
database management system (RDBMS) designed to run on a variety of
platforms from server-class installations to mobile devices using the
Windows Mobile operating system. SQL ANYWHERE® is a self-managing
RDBMS with high reliability, high performance, synchronization
capabilities, small footprint, and a full range of SQL features across a
variety of 32- and 64-bit platforms.

[0004] Currently available products, such as SQL ANYWHERE® support
manual materialized views. A manual materialized view, which can only be
refreshed by complete re-computation, may be defined by any complex
query. However, some query optimizers, such as the SQL ANYWHERE®
Optimizer, can use only a certain class of materialized views in the view
matching process during query optimization. Although SQL ANYWHERE®
supports incremental maintenance of certain materialized views for
GROUP-SELECT-PROJECT-JOIN views, what is needed is a more general class
of materialized views that can be incrementally maintained. Accordingly,
what is needed are methods and systems for incremental maintenance for
immediate Materialized Views (iMVs). What is further needed is an RDBMS
that supports an extended class of immediate materialized views, namely
outerjoin views with and without aggregation. In an embodiment of the
invention, the algorithms described herein may be implemented in a
database or data warehouse system such as, but not limited to, SQL
ANYWHERE®.

[0005] Outerjoin queries are used more and more frequently in new systems
and external tools where database administrators (DBAs) or experienced
database developers are not at hand to fine-tune the generated SQL
statements. An example of such generated query is depicted in FIG. 1. For
example, the SQL ANYWHERE® Optimizer has sophisticated techniques for
processing outerjoin queries from semantics transformations to view
matching using outer-join views. It is then a necessity to extend the
support to incremental maintenance of materialized views with outerjoins,
as this can speed up many of the applications using the SQL ANYWHERE®
RDBMS. The goals for an efficient support of immediate materialized views
with outerjoins are multifold.

[0006] Some traditional techniques for incremental maintenance of
materialized views with outerjoins are based on the join-disjunctive
normal form representation. A join-disjunctive normal form encodes an
outerjoin query as a sequence of minimum unions of different joins. The
example in FIG. 3 shows the parent-child relationship graph corresponding
to the join-disjunctive normal form of the query V1 defined in Table
1. Such incremental maintenance algorithm consists of a series of steps:
one step for computing and applying the primary delta (not null-supplied
tuples), then a set of subsequent steps for applying secondary deltas
(null-supplied tuples) to delete or insert null-supplied tuples. The
primary delta is saved and reused in the computation of the secondary
deltas. This computation may need to access again the base relations in
order to correctly compute the null-supplied tuples. As these traditional
techniques require a separate SQL statement to implement each of the
needed steps, they incur inefficiencies and require resources for parsing
and executing multiple SQL statements. For example, for the relation
X2 of the view V1 (defined in Table 1) depicted in FIG. 3, the
view update algorithm will consist of five steps: computing and applying
the primary delta, and computing and applying four secondary deltas
corresponding to the join-disjunctive normal form terms
R1R2T1T2, R1R2, X1Y1Y2, and
X1.

[0007] Accordingly, what is needed are methods and systems for incremental
maintenance of materialized views with outerjoins through use of a single
maintenance update statement for each materialized outer-join view.

BRIEF SUMMARY OF THE INVENTION

[0008] The invention includes systems, methods, and computer program
products for optimizing queries including outerjoins and materialized
views.

[0009] In an embodiment of the invention, the Preserved Side/Null Supplied
Side (PSNS) algorithm is proposed for incremental maintenance of
materialized views with outerjoins. The algorithm enables generation of
just a single maintenance update statement for each materialized
outerjoin view, which will be used to incrementally update the
materialized view when a referenced base table is updated. As this is a
usual SQL statement that will be executed by RDBMS, this in turn allows
powerful optimizations to be applied while processing the update
statements, in order to achieve better performance.

[0010] Secondly, as the generated SQL statement computes in the same time
not-null-supplied tuples and null-supplied tuples, this allows an
extended class of immediate materialized views to be supported, due to
fewer restrictions being imposed on the definitions of the views.

[0011] Thirdly, since only one update statement is used, no intermediate
temporary tables need to be saved during a view update operation. This
also brings a substantial improvement in performance as compared to
traditional solutions.

[0012] Further features and advantages of the invention, as well as the
structure and operation of various embodiments of the invention, are
described in detail below with reference to the accompanying drawings. It
is noted that the invention is not limited to the specific embodiments
described herein. Such embodiments are presented herein for illustrative
purposes only. Additional embodiments will be apparent to persons skilled
in the relevant art(s) based on the teachings contained herein.

BRIEF DESCRIPTION OF THE DRAWINGS/FIGURES

[0013] The accompanying drawings, which are incorporated herein and form a
part of the specification, illustrate the present invention and, together
with the description, further serve to explain the principles of the
invention and to enable a person skilled in the relevant art to make and
use the invention.

[0015] FIG. 2 depicts the PSNS-annotated normalized join operator tree,
for the view V1 (defined in Table 1) in accordance with an
embodiment of the present invention.

[0016] FIG. 3 illustrates a parent-child relationship graph corresponding
to the join-disjunctive normal form of the view V1 (defined in Table
1).

[0017] FIGS. 4-6 are flowcharts illustrating steps by which immediate
materialized views (iMVs) are incrementally updated in accordance with
embodiments of the present invention.

[0018] FIG. 7 provides a modular view of a system in which the present
invention may be implemented.

[0019] FIGS. 8 and 9 are flowcharts illustrating steps by which the
generated update statements are created from the PSNS-annotated
normalized join operator trees built for immediate materialized views
(iMVs) with outerjoins, in accordance with embodiments of the present
invention.

[0020] FIG. 10 depicts an example computer system in which the present
invention may be implemented.

[0021] The present invention will now be described with reference to the
accompanying drawings. In the drawings, generally, like reference numbers
indicate identical or functionally similar elements. Additionally,
generally, the left-most digit(s) of a reference number identifies the
drawing in which the reference number first appears.

[0023] The following detailed description of the present invention refers
to the accompanying drawings that illustrate exemplary embodiments
consistent with this invention. Other embodiments are possible, and
modifications can be made to the embodiments within the spirit and scope
of the invention. Therefore, the detailed description is not meant to
limit the invention. Rather, the scope of the invention is defined by the
appended claims.

[0024] It would be apparent to one of skill in the art that the present
invention, as described below, can be implemented in many different
embodiments of software, hardware, firmware, and/or the entities
illustrated in the figures. Any actual software code with the specialized
control of hardware to implement the present invention is not limiting of
the present invention. Thus, the operational behavior of the present
invention will be described with the understanding that modifications and
variations of the embodiments are possible, given the level of detail
presented herein.

[0026] According to an embodiment of the invention, the algorithms can be
integrated with a query optimizer that supports materialized views, such
as, but not limited to, the SQL ANYWHERE® Optimizer. The update of
iMVs can be achieved using internally generated triggers, one for each
relation referenced in an iMV. Internally generated triggers contain
update statements which, given a ΔT update relation representing
the updated rows of a based table T, perform updates to the iMVs that
reference T in their definition. In an embodiment, the update statements
are SQL statements internally generated which are processed and optimized
by the query optimizer just like any other query taking full advantage of
query optimization techniques present in any query optimizer. Hence, the
incremental maintenance of iMVs with outerjoins can be done in a similar
fashion, which is achieved via MERGE/INSERT/UPDATE statements, in
accordance with an embodiment. One of skill in the relevant arts will
appreciate that other programming languages, database platforms, and
technologies can be used to implement the logic and algorithms discussed
herein.

[0027] Secondly, given the current landscape of the applications using a
RDBMS server, the restrictions imposed to the class of the outerjoin
views which can be immediately maintained have to be kept to a minimum.
Traditional solutions are very restrictive with respect to the content of
the select list and the null-intolerant properties imposed to the
predicates. Embodiments of the invention described herein improve upon
traditional methods by relaxing null-intolerant property restrictions
(i.e., by being null-tolerant).

[0028] Thirdly, the extra information needed by the new generation
algorithms for outerjoin update statements is efficiently added to
internal representation of the outerjoin query blocks used by a query
optimizer, such as, but not limited to, the SQL ANYWHERE® Optimizer.

[0029] The Algorithm PSNS ( ) described herein achieves exactly that.

GLOSSARY AND NOMENCLATURE

[0030] The following definitions are offered for purposes of illustration,
not limitation, in order to assist with understanding the discussion that
follows.

[0031] Join: A join clause combines records from two or more tables, such
as, but not limited to, database tables. As used herein, in an
embodiment, a database may be one or more of a relational database, an
in-memory database, a file system, a collection of web server documents,
files, or resources, or any collection of electronic data.

[0032] Outerjoin: An outerjoin is a join that does not require each record
in the two joined tables to have a matching record. The joined table
retains each record, even in cases where no other matching record exists.
Outerjoins may be left outerjoins, right outerjoins, or full
outerjoins--depending on which table(s) one preserves the rows from
(left, right, or both).

[0033] Relation: A relation is defined as a set of tuples (rows) that have
the same attributes (columns).

[0034] Relational Database: A relational database is a collection of data
stored in relations (sometimes called tables). The relational database
was invented by E. F. Codd at IBM in 1970.

[0035] Structured Query Language (SQL): SQL: SQL stands for Structured
Query Language. The original version called SEQUEL (structured English
query language) was designed by IBM in the 1970's. SQL-2008 is the latest
standard for SQL as set out in a document published by the International
Standards Organization in 2008; see e.g., "SQL Foundation" published in
July 2008.

[0037] Tuple: A tuple t over the Schema(T) is an assignment of values to
attribute names of Schema(T), t=a1, . . . , an).
t[Ai]=ai, denotes the value of tuple t for the attribute
Ai. An instance of the relation T is a set of tuples defined over
the attributes in the Schema(T), T={t1, . . . , tk}. For a
tuple t defined over the schema of the relations T1, . . . , Ti
the notation t[Ti] denotes the values of the tuple t for the
attributes of the relation Ti. The notation n(t[T]) denotes that all
attributes in the Schema(T) have Null in the tuple t, while nn(t[T])
denotes that at least one attribute in the Schema(T) is not Null. Also,
t=( . . . , n(T), . . . , nn(R) . . . ) indicates that the tuple t is
Null on T, i.e., n(t[T]), and not-null on R, i.e., nn(t[R]).

[0039] Outerunion: As used herein, in an embodiment, the term outerunion
of the two relations T1 and T2 is denoted by T1T2,
and it is computed by first padding the tuples of each relation Ti,
i=1, 2 with Null for the attributes in
(Schema(T1)∪Schema(T2))\Schema(Ti), and then
computing the union of the resulting sets.

[0040] Join, antijoin, left, right, and full outerjoins: For two relations
T1 and T2 the join, antijoin, left, right, and full outerjoins
are defined as following, respectively:

[0041] null-supplied tuple: A tuple for which all attributes of a relation
T are null-padded or null-extended is denoted by T-null-supplied tuple.

[0042] null-supplied tuple: A tuple for which at least one attribute of a
relation T is not null is denoted by T-null-supplied tuple.

[0043] Dominating tuple: A tuple t dominates a tuple r if they are defined
on the same schema, and t[A]=r[A], .A-inverted.A.di-elect cons.Schema(t)
for which r[A] is not null.

[0044] Duplicate tuple: t and r are tuples defined on the same schema. If
.A-inverted.A.di-elect cons.Schema(t), t[A] is not distinct from r[A],
i.e., they are both Null or they are equal, then t is a duplicate of r.

[0045] Best match operator: The best match operator 13 as defined to be
β(R)={r|r.di-elect cons.R, r is not dominated or a duplicate by any
tuple in R and it has at least one non null value}. Best match operator
applied to a relation R eliminates all tuples which are dominated by or
duplicates of other tuples, hence β(R) has no two tuples which
dominate each other or are duplicates.

[0046] Duplicate Elimination operator: δ(R) for the duplicate
elimination operator: δ(R)={r|r.di-elect cons.R, r is not a
duplicate of any tuple in R}.

[0047] Strong predicate: A predicate p is strong if it does not evaluate
to True if any of the attribute in Attributes(p) has value Null.

[0048] NS-intolerant predicate: A predicate p(T1, . . . , Tn) is
NS-intolerant on a relation T.di-elect cons.{T1, . . . , Tn} if
the predicate doesn't evaluate to True for tuples which are
T-null-supplied. The distinction between a NS-intolerant predicate and a
strong predicate is a that the strong predicate p is required to be
null-intolerant on any attribute in Schema(p). For example, T.X IS NOT
DISTINCT FROM R.X1 AND rowid(T) IS NOT NULL is not a strong
predicate but it is NS-intolerant on the relation T according to
NS-intolerant definition: T.X can be NULL but still the predicate can
evaluate to True. In our experience, a large class of customer queries do
not use strong predicates even in the ON clauses as it is the case with
the query from FIG. 1 (for example, the ON predicates from lines 16-17,
34-35). When the property of a predicate to be NS-intolerant for a
certain relation Ti is important, it is denoted by underlying the
relation Ti e.g., p(T1, . . . , Ti, . . . , Tn).
1 The IS NOT DISTINCT FROM predicate was introduced in ANSI SQL:
1999 and it is equivalent to the predicate T.X=R.X OR (T.X IS NULL AND
R.X IS NULL).

[0049] Logical operator tree: The logical operator tree is a
representation of a query where internal nodes are binary joins while
leaves are relations. Each node in a logical operator tree may have a
predicate referencing relations from left and right subtrees

[0050] Outerjoin query: As used herein, in an embodiment, the teem
outerjoin query is a query which contains left and full outerjoins (the
right outerjoins can be transformed into left outerjoins), and inner
joins. An outerjoin query can be represented by a join operator tree
whose internal nodes are binary joins and the leaves are relations.

[0051] Null-supplying outerjoin: For an internal join node of a join
operator tree of type left outerjoin, it is said that the outerjoin
null-supplies the relations from its right hand side. For a full
outerjoin node, the outerjoin null-supplies both its sides. Note that in
an outerjoin query, the same base relation can be null-supplied by more
than one outerjoin.

[0052] Direct outerjoin: For any relation T in an outerjoin query
represented by an operator tree, the direct outerjoin of T is the first
ancestor node of type left outerjoin or full outerjoin which
null-supplies T. Any relation T can have at most one direct outerjoin.

[0053] Indirect outerjoin: For any relation T in an outerjoin query
represented by an operator tree, an indirect outerjoin is an outerjoin
node which null-supplies T but it is not the direct outerjoin of T.

[0054] Materialized View with Outerjoin: A materialized view whose
definition is an outerjoin query is a materialized view with outerjoins
with and without aggregation functions.

[0055] In an embodiment, incrementally maintained materialized views with
outerjoins are assumed to have the following properties regarding the
predicates used in the view definition. The predicate of an outerjoin J
is an NS-intolerant predicate only on a relation, which has a direct
outerjoin, and this direct outerjoin is different than J. NS-intolerant
property imposed on some of the outerjoin predicates assures the
null-supplying rippling effect: if a relation T is null-supplied by its
direct outerjoin, then all its indirect outerjoins must also null-supply
their whole null-supplying sides. Hence, any tuple generated by an
outerjoin query having T null-supplied will have all related relations
null-supplied as well. For example, for the outerjoin query

( R lo p , ( R , T ) T ) fo p ( T _ , S )
##EQU00002##

S the above condition requires that only the predicate p(T, S) of the
full outerjoin

fo p ( T _ , S ) ##EQU00003##

is NS-intolerant on the relation T which can be null-supplied by its
direct left outerjoin R

lo p ( R , T ) T . ##EQU00004##

The predicate

lo p , ( R , T ) ##EQU00005##

does not have to be NS-intolerant as the rippling effect doesn't hold for
the other outerjoin

fo p ( T _ , S ) . fo p ( T _ , S )
##EQU00006##

will null-supply both relations R and T or none, hence it has no impact
on the result of the left outerjoin

R lo p , ( R , T ) T . ##EQU00007##

[0056] In an embodiment, it is assumed that the materialized view has a
unique index with nulls not distinct on attributes that maybe null. For
example, if the unique index is defined on the attributes (A, B), two
tuples (α, Null) and (α, Null) are considered equals and
cannot exist in the same time in the view because they violate the
uniqueness of the index. Extra requirements for the immediate
materialized views with outerjoins will be discussed later when the PSNS
algorithm is described.

PSNS--Preserved Side/Null Supplied Side Algorithm

[0057] For a given outerjoin query which defines an immediate materialized
view V, the proposed algorithms find a representation of the
null-supplied properties of a base relation T that will give the correct
formula for an update statement for the view V after update operations on
the relation T. The main goal is to impose as few restrictions as
possible to the view definition, and also the view update statement to be
very efficient and perform incremental maintenance of the materialized
view V.

[0058] The assumption is that the relation T being updated is
null-supplied by at least one outerjoin in the view definition2. If
the view V references a set of relations T1, T2, . . . ,
Tn and the relation T, for an instance of the relations T1, . .
. , Tn and T, V(nn(T))={t\t.di-elect cons.V(T, T1, T2, . .
. , Tn), nn(t[T])} denotes the set of all tuples in the instance V (T,
T1, T2, . . . , Tn) which are not null-supplying the
relation T (T-null-supplied). V(nn(T)) can be computed by using the
original view definition where all outerjoins which null-supply T are
transformed into inner or left outerjoin such that T is no longer
null-supplied. 2 For relations that cannot be null-supplied by an
outerjoin, the view update statements are similar to the formulas for
innerjoin immediate materialized views.

V(n(T)) denotes the set of all T-null-supplied tuples in the instance V
(T, T1, . . . , Tn).

[0060] The maximum set of the T-null-supplied tuples in V, denoted by Null
(T, V(T, T1, T2, . . . , Tn), as the set of all possible
T-null-supplied tuples in any instance of the view V computed over the
fix relations T1, T2, . . . , Tn and any instance of the
relation T. I.e., Null(T, T1, T2, . . . ,
Tn)=δ(∪any instance of TV(n(T)))). As used herein,
in embodiments of the invention, the assumption is that, for a
materialized view V, only one relation is updated and all others
referenced by the view are left unchanged. Hence, whenever possible, the
simplified notations are used where the fix relations are not mentioned,
e.g., Null (T, V)=Null (T, V (T1, T1, T2, . . . , Tn), and
V(T, T1, T2, . . . , Tn)=V(T). Null (T, V) can be obtained
by using the original view definition where the relation T is replaced by
empty set (i.e., Null (T, V)=V(T→φ)). In the previous example,

Null (T, V) contains all possible T-null-supplied tuples which can be
present in V regardless of the content of T. In other words, for any
instance of the relation T, any T-null-supplied tuple in V is present in
Null (T, V) as long as the content of all other base relations is
unchanged, i.e., V(n(T)).OR right.Null (T, V). In the previous example,
if R={r0, r1}, S={s0}, any T-null-supplied tuple that can
exist in V3 is one of the following tuples: Null (T, V)={(r0,
Null, Null), (r1, Null, Null). (Null, Null, s0)}.

[0061] For incremental maintenance of the materialized views with
outerjoins, it is known, for each insert or delete operation on the
relation T using the set ΔT, what are the tuples to be inserted or
deleted from V(nn(T)). This set, denoted by V(nn(ΔT)) can be
obtained by using ΔT in the definition of V(nn(T)) instead of T.
However, each such operation can have a side effect related to the
T-null-supplied tuples. For a delete operation on T, deleting tuples from
V, namely, V(nn(ΔT)), may leave the view V in need of new
T-null-supplied tuples. Furthermore, inserting new tuples in V, namely
V(nn(ΔT)), may leave some spurious old tuples in V which are now
dominated by new tuples in V(nn(ΔT)). The term NS-compensation is
used to describe the operation of inserting or deleting T-null-supplied
tuples in V, after V was updated using V(nn(ΔT)).

[0062] The goal is to design an algorithm having the following properties:
(1) for each tuple t.di-elect cons.V(nn(ΔT)), compute, in the same
time with the computation of V(nn(ΔT)), the set of potential
T-null-supplied tuples, Null (t, T, V), without accessing the view V.
Null (t, T, V) is actually the set of all tuples dominated by t in
Null(T, V). Null (t, T, V) will be used for NS-compensation after the
insert, delete, or update operations; (2) for each tuple t'.di-elect
cons.Null (t, T, V), decide, using the view V, if t' is to be deleted
(for insert and update operations) or inserted (for delete and update
operations) into V to NS-compensate for the inserted and deleted tuples
from V(nn(ΔT)). (3) there is no need to save partial deltas into
temporary tables, hence a single statement should be used for both the
update operation using V(nn(ΔT)) and NS-compensation using Null
(ΔT, T, V).

[0063] The algorithm PSNS (Algorithm 2 in Table 4) computes, for each base
relation T, a set PSNS (T) (Preserved Sides/Null-supplied Sides) which is
a set of pairs of subsets (psi, nsi), psi, nsi,
.di-elect cons.Relations (V) where each such pair describes exactly one
T-null-supplied tuple, Null (i, t, T, V), which can be obtained from a
tuple t.di-elect cons.V(nn(ΔT)). Moreover, a pair (psi,
nsi)PSNS (T) provides the condition used to check the need for using
the tuple Null (i, t, T, V) for NS-compensation of V. The set nsi
contains all the relations in Relations(V) which has to be null-supplied
together with Tin the tuple Null (i, t, T, V). The set psi
represents all the relations which are preserved in the tuple Null (i, t,
T, V). Of course, the tuple Null (i, t, T, V) can exist if and only if
the original tuple t does not null-supply the relations in psi. The
values for the rest of relations in
don'tcare(i,T,V)=Relations(V)\(nsi∪psi) are left
unchanged in Null (i, t, T, V). Any tuple Null (i, t, T, V) is dominated
by the tuple t, hence they cannot both exist in the view V in the same
time.

[0064] The formal definitions for the set Null (i, t, T, V), Null (t, T,
and Null (ΔT, T, V) are given by:

[0065] Note that the set Null (t, T, V) doesn't contain duplicates or
spurious tuples as each pair (ps, ns) .di-elect cons.PSNS (T) produces a
distinct tuple, i.e, Null (t, T, V)=δ(Null (t, T, V)). However, the
set Null (ΔT, T, V) may contain duplicates since two distinct
tuples in ΔT may produce the same T-null-supplied tuple. It can be
proven that if V has all the properties required by PSNS algorithm, Null
(T, V) as defined above is obtained by the formula in the Equation 2. In
other words, all the T-null-supplied tuples can be computed from any
instance of the view V:

Null(T,V)=V(T→φ,T1,T2, . . . ,Tn)

=δ(∪any instance of T,t.di-elect
cons.V(nn(T))Null(T,V)) Equation (2)

[0066] For an example, consider the view V1, defined in Table 1 and
FIG. 3, and the relation X2 being updated with ΔX2. For a
tuple t.di-elect cons.V(nn(ΔX2)) it is known that t[X2]
must be not null (the set (V(nn(ΔX2)) contains only
X2-null-supplied): t=(r1, r2, t1, t2, x1,
x2, y1, y2). The set PSNS (X2) is computed to be
{({X1}, {R1, R2, T1, T2, X2}), ({R1},
{X1, X2, Y1, Y2})}. Intuitively, the first (ps1,
ns1)=({X1}, {R1, R2, T1, T2, X2})
represents the X2-null-supplied tuples where the relation X1 is
to be preserved. As X2 is null-supplied in Null (1, t, X2,
V1), X1 is to be preserved, and the predicate p(r1,
x1, Null, y2) True for the full outerjoin s0fo, then
the other side of the full outerjoin is null-supplied as well beside
X2. This explains why ns1={R1, R2, T1, T2,
X2} is exactly the other side of the full outerjoin S0fo.
If t[X1]=x1 is not null, then Null (1, t, X2,
V1)=(Null, Null, Null, Null, x1, Null, y1, y2) with
y1 and y2 left the same as for tuple t. y1 and y2 can
be null or non-null values and the computation of Null (1, t, X2,
V1) is independent of their values. Similarly, Null (2, t, X2,
V1)=(r1, r2, t1, t2, Null, Null, Null, Null) if
t[R1]=r1 is not null. The preserved relation in Null (2, t,
X2, V1) is R1, and the values r2, t1, t2
are left unchanged from t. However, the full outerjoin s0fo
null-supplies the whole right hand side as the predicate p(r1,
x1, Null, y2)True. If the update operation on the relation
X2 is an insert, if the tuple Null (i, t, X2, V1) exists
in V, it will be deleted as it is a spurious tuple being dominated by the
new tuple t. If the update operation is a delete, the tuple Null (i, t,
X2, V1) can be used for NS-compensation if, after the delete of
V(nn(ΔT)) from V, no tuple has the value t[psi] in V, i.e.,
.di-elect cons.V, t'[psi]=t[psi].

[0067] The Algorithm 2 describe in Table 4 computes the PSNS ( ) sets for
each base relation in an outerjoin immediate materialized view V. The
algorithm is using as input a normalized join operator tree, which is a
compact representation of a query operator tree where the outerjoins,
which could be unnested, were `flattened` in the same subplan. An example
of such normalized operator tree is provided in FIG. 2. PSNS-annotated
normalized join operator tree was designed to support the optimization of
the statements with outerjoins in a query optimizer, including, but not
limited to, the SQL ANYWHERE® Optimizer. The main property of a
normalized join is that its relations are all null-supplied together by
the direct or indirect outside outerjoins. For example, in FIG. 3, the
normalized join s1fo representing the table expression

Intuitively, a subplan represents, as a set of relations, atomic
null-supplying sides of outerjoins. In an embodiment, the algorithm PSNS
( ) uses the postorder traversal of the normalized join operator tree and
computes, for each subplan S, based on the predicates used for
outerjoins, the (ps, ns).di-elect cons.PSNS (S) sets. ns are sets of
other relations, which are null-supplied if S is null-supplied. ps are
sets of preserved relations, which are preserved if S is null-supplied.
After the PSNS algorithm is applied, the PSNS-annotated normalized join
operator tree contains all the metadata needed to generate, for any
relation T.di-elect cons.Relations(V), the update statements used for the
incremental maintenance of the materialized view V. FIG. 3 shows the
final PSNS sets for each subplan Six where x.di-elect cons.{lo,
fo, j}. In the section below, embodiments are described with reference to
Tables 1-4 wherein PSNS ( ) sets are used to generate the update
statements for immediate materialized views.

[0068] According to an embodiment, materialized views which can be
incrementally maintained or used in the query optimization process are
represented internally as PSNS-annotated normalized join operator trees
(nT) which can be the same representation used by a cost-based query
optimizer such as, but not limited to, the SQL ANYWHERE® Optimizer. nT
is built after semantic transformations and query analysis are applied to
the original query operator tree. Both the view matching algorithm and
the generation of the update statements for the incremental maintenance
of the materialized views are using nT representing the definition of the
views.

[0069] In an embodiment, for an immediate materialized view, the
PSNS-annotated nT is built at the first reference to the view since the
server was started. If a relation T is updated, an internally generated
trigger is created containing all the update statements for any immediate
materialized view referencing the relation T. This section describes some
of the update statements generated for these internal triggers for iMVs
with outerjoins. Each update statement is a SQL statement, which can be
an INSERT, UPDATE, or MERGE statement. The update statements are
generated from the PSNS-annotated nT representation of the materialized
views. The execution of an internally generated trigger is done after any
update operation on the relation T when ΔT is passed on to the
trigger. Each generated update statement is processed like any other SQL
query, hence all the optimizations supported in a query optimizer such as
exploitation of foreign key constraints, outer and inner join elimination
are applied to find efficient execution plans. The generation algorithm
is designed to produce correct up-date statements, which can be
efficiently optimized by a query optimizer, such as, but not limited to,
the SQL ANYWHERE® Optimizer. In an exemplary embodiment of the
invention, the techniques described herein are implemented in SQL
ANYWHERE® 12.0.

[0070] The operations sections below describe the SQL statements generated
for iMVs with outerjoins. In all the statements, the derived table {tilde
over (V)}(nn(ΔT)) is equivalent to V(nn(ΔT)) but preserves
all the columns needed to compute the select list of V(nn(ΔT)) as
well as tuples in Null (ΔT, T, V). Note that complex expressions of
the select list in Null(ΔT, T, V) cannot be computed directly from
V(nn(ΔT)) as columns needed maybe projected out in the final select
list. For the delete and update operations, V(nn(ΔT)) is applied by
using the unique index which the materialized view must have. The
condition V.ui IS NOT DISTINCT FROM V(nn(ΔT)).ui (see line 22 in
Table 6 describing INSERT operation, and line 24 in Table 9 describing
the UPDATE operation) contains all the IS NOT DISTINCT FROM predicates
applied to the columns of the unique index. For the delete and update
operations, the computation of Null (ΔT, T, V) is done after
V(nn(ΔT)) is applied to the view. This is achieved by using
embedded (see lines 18-23, Table 8 describing DELETE operation) and
(lines 21-32, Table 9 describing UPDATE operation) statements,
respectively.

INSERT Operation (Table 5, Table 6)

[0071] According to an embodiment, for an insert operation, the
computation of the set Null(ΔT, T, V) can see the view data before
V(nn(ΔT)) is applied, as the spurious T-null-supplied tuples are
present in the view before the insert operation. Hence, the MERGE
statement computes V(nn(ΔV)) (Table 6, lines 7-8) in the same time
with the Null (ΔT, T, V) (Table 6, lines 9-20). For efficiency,
only rowids of the tuples found in V∩Null(ΔT, T, V) are
passed to be processed by the WHEN MATCHED clause. In an embodiment, the
MERGE statement processes first the spurious tuples which are deleted by
the WHEN MATCHED clause (Table 6, line 24) (this is the NS-compensation
operation). The rows from V(nn(ΔT)) are inserted next by the WHEN
NOT MATCHED clause (Table 6, lines 25-26). Table 5 below depicts
exemplary logic for computing an applying Null(i, t, T, V) for INSERT
operations. Table 6 below provides an exemplary MERGE SQL statement for
INSERT operations, in accordance with an embodiment of the invention.

[0072] According to an embodiment, for the delete operation, the
computation of the set Null(ΔT, T, V) must see only the view data
after V(nn(ΔT)) is applied, such that a tuple Null (i, t, T, V) is
generated only if it is needed for the NS-compensation operation.

[0073] Hence, in an embodiment, the INSERT statement first computes (Table
8, line 21) and applies (Table 8, line 18) V(nn(ΔT)). An embedded
statement (Table 8, lines 18-23) is used for computing and applying
V(nn(ΔT)). (The embedded update statements (also known as
select-from-DML) are executed first, hence the rest of the query sees
only the modified data, in our case, the modified view after
V(nn(ΔT)) was applied.) The rest of the statement will see the
modified data of the view V when the conditions for NS-compensation are
checked (Table 8, lines 9 and 16).

[0074] The set Null(ΔT, T, V) is computed after the statement
(Table 8, lines 5-17) and the tuples in Null (ΔT, T, V) are
inserted into the view (this is the NS-compensation operation).

[0075] In accordance with an embodiment of the invention, for the update
operation, a statement is used. As with the delete operation, in an
embodiment, the computation of the set Null(ΔT, T. V) must see only
the view data after V(nn (ΔT)) is applied, such that a tuple
Null(i, t, T, V) is generated only if it is needed for the
NS-compensation operation. Hence, an embedded MERGE statement (Table 9,
lines 21-32) first computes (Table 9, line 22) and applies (Table 9, line
28-30) V(nn(ΔT)). The rest of the statement will see the modified
data of the view V when the conditions for NS-compensation are checked
(Table 9, lines 10 and 16). The set Null(ΔT, T, V) is processed by
the WHEN [NOT] MATCHED clauses (Table 9, lines 35-37).

[0076] FIGS. 4-6 are flowcharts illustrating steps by which immediate
materialized views (iMVs) are created, in accordance with embodiments of
the present invention.

[0077] FIG. 4 is a flowchart 400 illustrating steps by which an immediate
materialized view (iMV) is updated, in accordance with an embodiment of
the present invention.

[0078] More particularly, flowchart 400 illustrates the steps by which an
iMV update based upon an update to a base table is performed, according
to an embodiment of the present invention.

[0079] The method begins at step 402 and proceeds to step 404 where an
update statement is executed on a base table T. In one embodiment of the
invention, T is a database table in a relational database. After the
update statement is executed, the method proceeds to step 406.

[0080] In step 406, a ΔT, or delta indicating changes base table T,
is computed. In an embodiment, this step is performed by comparing before
and after images of updated rows in base table T that were updated in
step 404. After the ΔT is computed, the method proceeds to step
408.

[0081] In step 408, a ΔiMV is computed. According to an embodiment,
ΔiMV is computed based on ΔT computed in step 406. After the
ΔiMV is computed, the method proceeds to step 410.

[0082] In step 410, the iMV view is updated and control is passed to step
412 where the method ends.

[0083] In accordance with an embodiment of the invention, this step is
performed using ΔiMV to reflect changes in ΔT computed in
step 406.

[0084] FIG. 5 is a flowchart 500 illustrating steps by which a trigger is
executed for an immediate materialized view (iMV), in accordance with an
embodiment of the present invention.

[0085] More particularly, flowchart 500 illustrates the steps by which iMV
trigger that will be used to update each iMV referencing a base table, is
created, and then executed according to an embodiment of the present
invention.

[0086] The method begins at step 502 and proceeds to step 504 where a base
table T is updated. According to an exemplary embodiment of the
invention, T is a database table in a relational database. After base
table T is updated, the method proceeds to step 506.

[0087] In step 506, an evaluation is made regarding whether an iMV trigger
exists for the base table T updated in step 504. If it is determined that
an iMV trigger exists, control is passed to step 510. If it is determined
that no iMV trigger exists, then control is passed to step 508 where an
iMV trigger is created.

[0088] In step 508, after an iMV trigger is created which corresponds to
base table T updated in step 504, the method proceeds to step 510.

[0089] In step 510, the iMV trigger is executed to update each iMV that
references base table T and control is passed to step 512 where the
method ends.

[0090] FIG. 6 is a flowchart 600 illustrating steps by which a trigger is
saved for an immediate materialized view (iMV), in accordance with an
embodiment of the present invention.

[0091] More particularly, flowchart 600 illustrates the steps by which an
iMV trigger is generated and saved for base table updates, according to
an embodiment of the present invention.

[0092] The method begins at step 602 and proceeds to step 604 where all
iMVs referencing a base table T are identified. According to an exemplary
embodiment of the invention, T is a database table in a relational
database. After the iMVs are identified, the method proceeds to step 606.

[0093] In step 606, for each iMV identified in step 604, an update
statement is generated.

[0094] In an embodiment, this step is performed by generating MERGE SQL
statements to merge a ΔiMV into an iMV identified in step 606.
After the update statements are generated, the method proceeds to step
608.

[0095] In step 608, a trigger, iMV(T), is saved for base table T updates
and control is passed to step 610 where the method ends.

[0096] FIG. 8 is a flowchart 800 illustrating steps by which a SQL update
statement is generated for an iMV after an update of a base table T, in
accordance with an embodiment of the present invention.

[0097] More particularly, flowchart 800 illustrates the steps by which a
SQL update statement is generated for an iMV after an update of a base
table T by a ΔT from a PSNS annotated normalized join operator
tree, according to an embodiment of the present invention.

[0098] The method begins at step 802 and proceeds to step 804 where a
determination is made as to whether an iMV references a base table T.
According to an exemplary embodiment of the invention, T is a database
table in a relational database. After the iMVs are identified, the method
proceeds to step 806.

[0099] In step 804, an evaluation is made regarding whether an iMV
references a base table T. If it is determined that an iMV references a
base table T, control is passed to step 808. If it is determined that no
an iMV references a base table T, then control is passed to step 806
where the process ends.

[0100] In step 808, a Normalized Join Operator Tree, nT(iMV), is built
based on the definition of the iMV that references base table T. After
the Normalized Join Operator Tree nT is built, the method proceeds to
step 810.

[0101] In step 810, an evaluation is made regarding whether the Normalized
Join

[0102] Operator Tree built in step 808, nT(iMV), satisfies predicate
conditions. If it is determined that the Normalized Join Operator Tree
satisfies predicate conditions, control is passed to step 814. If it is
determined that the Normalized Join Operator Tree does not satisfy
predicate conditions, then control is passed to step 812 where the
process ends.

[0103] In step 814, a PSNS annotation of the Normalized Operator Join Tree
is performed to create PSNS-nT(iMV). After the Normalized Operator Join
Tree is annotated, control is passed to step 816.

[0104] In step 816, an evaluation is made regarding whether the Normalized
Join Operator Tree annotated in step 814, PSNS-nT(iMV), satisfies select
list conditions. If it is determined that the annotated Normalized Join
Operator Tree satisfies select list conditions, control is passed to step
820. If it is determined that the annotated Normalized Join Operator Tree
does not satisfy select list conditions, then control is passed to step
818 where the process ends.

[0105] In step 820, a SQL update Statement for an iMV is generated which
will be used after an update of based table T by a ΔT, from
PSNS-nT(iMV), and control is passed to step 822 where the method ends.

[0106] FIG. 9 is a flowchart 900 illustrating steps by which a logical
join operator tree is normalized, in accordance with an embodiment of the
present invention.

[0107] More particularly, flowchart 900 illustrates the steps by which a
logical operator tree is normalized based upon the type of relations and
joins for nodes of the tree, according to an embodiment of the present
invention.

[0108] The method begins at step 902 where an operator tree T, a join node
n and normalized join nJ is read as input. After the tree is read and
parsed, the method proceeds to step 904 where a determination is made as
to what type of operation is represented by node n in tree T. In an
embodiment, tree T is a logical operator tree, which is a representation
of a query where internal nodes n are joins and leaves, are relations. In
an embodiment, each node n in a logical operator tree T has a predicate
referencing relations from left and right subtrees.

[0109] In step 904, an evaluation is made regarding what type of node n
is. If the n is leaf of tree T representing a relation, control is passed
to step 906 where the relation information is passed to step 908. If n is
a node representing an inner join, control is passed to step 910 where
the join information is passed to step 912.

[0110] If n is a node representing a left outerjoin, control is passed to
step 916 where the left outerjoin information is passed to step 914. If n
is a node representing a full outerjoin, control is passed to step 920
where the full outerjoin information is passed to step 918.

[0111] In step 908, the normalized join nJ is augmented with the new node
n based upon the information passed in step 906. In an embodiment, the
logic for the function performed in this step is expressed as:

nJ.p=nJ.p AND p//add the predicate of the node n to the normalized join
nJ nJ.ProperRelations U={T}//relation T is part of the normalized join nJ
T.Parent=nJ//relation T has the direct parent the normalized join nJ

[0112] After the function of step 908 is performed, the method proceeds to
step 922.

[0113] In step 912, the normalized join nJ is augmented with the new join
n based upon the join information passed in step 910. In an embodiment,
the logic for the function performed in this step is expressed as:

nJ.p=nJ.p AND p//add the predicate of the node n to the normalized join
nJ CALL nT(T, LEFT CHILD n, nJ)//traverse left child for building the
normalized join operator tree CALL nT(T, RIGHT CHILD n, nJ)//traverse
right child for building the normalized join operator tree

[0114] After the function of step 912 is performed, the method proceeds to
step 922.

[0115] In step 914, the normalized join nJ is augmented with the new
outerjoin n based upon the left outerjoin information passed in step 916.
In an embodiment, the logic for the function performed in this step is
expressed as:

[0116] CALL nT(T, RIGHT CHILD n, nJ)//traverse right child for building
the normalized join operator tree

nJL=NEW JOIN//create a new normalized join which will represent the
null-supplying side of the outerjoin n nJL.type=LEFT OUTERJOIN
nJL.p=p//the ON predicate is the predicate of the new normalized
join nJL nJL.Parent=nJ//the new normalized join nJL has
as a direct parent the input normalized join nJ nJ.nJoins
U={nJL}//nJ is the direct parent of the new normalized join nJL
CALL nT(T, LEFT CHILD n, nJL)//build the new normalized join
nJL by traversing the null-supplying side of the outerjoin n

[0117] After the function of step 914 is performed, the method proceeds to
step 922.

[0118] In step 918, the normalized join nJ is augmented with the new
outerjoin n based upon the full outerjoin information passed in step 920.
In an embodiment, the logic for the function performed in this step is
expressed as:

nJF=NEW JOIN//create a new normalized join nJF of type full
outerjoin nJF.type=FULL OUTERJOIN nJF.Parent=nJ//the new
normalized join has the direct parent nJ nJF.p=p//the new normalized
join has the predicate the ON predicate of the join n nJ.nJoins
U={nJF}//the new normalized join has the direct parent nJ
nJL=NEW JOIN//create a new normalized join nJL which will
represent the left subtree of the join n nJL.type=INNER OUTERJOIN
nJL.Parent=nJF//the new normalized join nJL has the parent
nJF nJR=NEW JOIN//create a new normalized join nJR which
will represent the right subtree of the join n nJR.type=INNER
OUTERJOIN nJR.Parent=nJF//the new normalized join nJR has
the parent nJF nJF.nJoins U={nJL, nJR}//the
normalized join nJF has two direct children nJL and nJR
CALL nT(T, LEFT CHILD n, nJL)//build the new normalized join
nJL by traversing one of the null-supplying sides of the full
outerjoin n CALL nT(T, RIGHT CHILD n, nJR)//build the new normalized
join nJR by traversing one of the null-supplying sides of the full
outerjoin n

[0119] After the function of step 914 is performed, the method proceeds to
step 922.

[0120] In step 922, a function is performed for all relations T in
Relations (p), where p is the predicate of the node n. In an embodiment,
logic for the function performed in step 922 is expressed as:

FOR ALL T IN Relations(p)

[0121] IF T.Parent !=nJ, nJF, nJL, nJR

[0122] THEN p is NS-intolerant.

[0123] After the function in step 922 is performed, the method ends.

Immediate Materialized View System

[0124] FIG. 7 is an example of a system 700 in which the algorithms and
methods described above can be implemented. The exemplary system 700
includes a server 704 with a database 706. In an embodiment, server 704
includes relational database management system (RDBMS) and database 706
is a relational database. In embodiments of the system, a plurality of
base tables T and immediate materialized views (iMVs) are stored and
maintained in database 706. Although a single client 702, server 704, and
database 706 is depicted in system 700, it is understood that a plurality
of clients 702 and servers 704 can access a plurality of databases 706.

[0125] In the exemplary system 700 depicted in FIG. 7, client 702 accesses
base tables and iMVs residing in database 706 via server 704. As would be
appreciated by one of skill in the relevant arts, other clients 702, such
as, but not limited to, mobile clients, laptops, and mobile phones can be
used to access server 704 and database 706. In an embodiment, database
706 is an in-memory database implemented without storing data files or
data from a variety of data sources (not shown) on disks of server 706.

Example Computer System Implementation

[0126] Various aspects of the present invention can be implemented by
software, firmware, hardware, or a combination thereof. FIG. 10
illustrates an example computer system 1000 in which the present
invention, or portions thereof, can be implemented as computer-readable
code. For example, the method illustrated by the flowcharts 400, 500,
600, 800, and 900 of FIGS. 4-6, 8, and 9 can be implemented in system
1000. Also, for example, the components of system 700 depicted in FIG. 7
can be implemented in system 1000. Various embodiments of the invention
are described in terms of this example computer system 1000. After
reading this description, it will become apparent to a person skilled in
the relevant art how to implement the invention using other computer
systems and/or computer architectures.

[0127] Computer system 1000 includes one or more processors, such as
processor 1004. Processor 1004 can be a special purpose or a
general-purpose processor. Processor 1004 is connected to a communication
infrastructure 1006 (for example, a bus, or network).

[0128] Computer system 1000 also includes a main memory 1008, preferably
random access memory (RAM), and may also include a secondary memory 1010.
Secondary memory 1010 may include, for example, a hard disk drive 1012, a
removable storage drive 1014, flash memory, a memory stick, and/or any
similar non-volatile storage mechanism. Removable storage drive 1014 may
comprise a floppy disk drive, a magnetic tape drive, an optical disk
drive, a flash memory, or the like. The removable storage drive 1014
reads from and/or writes to a removable storage unit 1018 in a well-known
manner. Removable storage unit 1018 may comprise a floppy disk, magnetic
tape, optical disk, etc. which is read by and written to by removable
storage drive 1014. As will be appreciated by persons skilled in the
relevant art(s), removable storage unit 1018 includes a computer usable
storage medium having stored therein computer software and/or data.

[0129] In alternative implementations, secondary memory 1010 may include
other similar means for allowing computer programs or other instructions
to be loaded into computer system 1000. Such means may include, for
example, a removable storage unit 1022 and an interface 1020. Examples of
such means may include a program cartridge and cartridge interface (such
as that found in video game devices), a removable memory chip (such as an
EPROM, or PROM) and associated socket, and other removable storage units
1022 and interfaces 1020 which allow software and data to be transferred
from the removable storage unit 1022 to computer system 1000.

[0130] Computer system 1000 may also include a communications interface
1024. Communications interface 1024 allows software and data to be
transferred between computer system 1000 and external devices.
Communications interface 1024 may include a modem, a network interface
(such as an Ethernet card), a communications port, a PCMCIA slot and
card, or the like. Software and data transferred via communications
interface 1024 are in the form of signals, which may be electronic,
electromagnetic, optical, or other signals capable of being received by
communications interface 1024. These signals are provided to
communications interface 1024 via a communications path 1026.
Communications path 1026 carries signals and may be implemented using
wire or cable, fiber optics, a phone line, a cellular phone link, an RF
link or other communications channels.

[0131] Computer system 1000 may additionally include computer display
1030. According to an embodiment, computer display 1030, in conjunction
with display interface 1002, can be used to display a user interface (UI)
or a database administrator (DBA) console at client 702 or server 704.

[0132] In this document, the terms "computer program medium" and "computer
usable medium" are used to generally refer to media such as removable
storage unit 1018, removable storage unit 1022, and a hard disk installed
in hard disk drive 1012. Signals carried over communications path 1026
can also embody the logic described herein. Computer program medium and
computer usable medium can also refer to memories, such as main memory
1008 and secondary memory 1010, which can be memory semiconductors (e.g.
DRAMs, etc.). These computer program products are means for providing
software to computer system 1000.

[0133] Computer programs (also called computer control logic) are stored
in main memory 1008 and/or secondary memory 1010. Computer programs may
also be received via communications interface 1024. Such computer
programs, when executed, enable computer system 1000 to implement the
present invention as discussed herein. In particular, the computer
programs, when executed, enable processor 1004 to implement the processes
of the present invention, such as the steps in the methods illustrated by
flowcharts 400, 500, 600, 800, and 900 of FIGS. 4-6, 8, and 9 discussed
above. Accordingly, such computer programs represent controllers of the
computer system 1000. Where the invention is implemented using software,
the software may be stored in a computer program product and loaded into
computer system 1000 using removable storage drive 1014, interface 1020,
hard drive 1012, or communications interface 1024.

[0134] The invention is also directed to computer program products
comprising software stored on any computer useable medium. Such software,
when executed in one or more data processing device, causes a data
processing device(s) to operate as described herein. Embodiments of the
invention employ any computer useable or readable medium, known now or in
the future. Examples of computer useable mediums include, but are not
limited to, primary storage devices (e.g., any type of random access
memory), secondary storage devices (e.g., hard drives, floppy disks, CD
ROMS, ZIP disks, tapes, magnetic storage devices, optical storage
devices, MEMS, nanotechnological storage device, etc.), and communication
mediums (e.g., wired and wireless communications networks, local area
networks, wide area networks, intranets, etc.).

CONCLUSION

[0135] While various embodiments of the present invention have been
described above, it should be understood that they have been presented by
way of example only, and not limitation. It will be understood by those
skilled in the relevant art(s) that various changes in form and details
may be made therein without departing from the spirit and scope of the
invention as defined in the appended claims. It should be understood that
the invention is not limited to these examples. The invention is
applicable to any elements operating as described herein. Accordingly,
the breadth and scope of the present invention should not be limited by
any of the above-described exemplary embodiments, but should be defined
only in accordance with the following claims and their equivalents.