THE DATA ADMINISTRATION NEWSLETTER – TDAN.com
ROBERT S. SEINER – PUBLISHER

Introduction

This is the third and final part in this series of articles on ANSI SQL’s inherent hierarchical processing capability and its support of transparent native XML integration. Part 1, Using XML to Link Below the Root, described how SQL can naturally integrate native XML at a full hierarchical processing level utilizing the Left Outer Join operation to inherently model and process hierarchical data structures.  Part 2, Processing XML’s Complex Hierarchical Structures, demonstrated how standard SQL can naturally support advanced XML hierarchical operations such as logical network structures, variable structures, and structural transformations.

This third and final article will address issues of efficiency and ease of use utilizing SQL hierarchical views and how they come together to form an extremely powerful combination. The synergy created from this combination delivers a totally seamless, hierarchically optimized unified heterogeneous virtual view defined entirely in standard SQL. This assures an accurate, consistent, and efficient ANSI SQL hierarchical operation across the entire heterogeneous view.

Figure 1 (below) demonstrates the processing of a heterogeneous hierarchical ANSI SQL query. The query involves the dynamic hierarchical joining and processing of a physical XML hierarchical structure and a logical hierarchically modeled relational structure. It will be used to demonstrate the SQL hierarchical capabilities described in this article necessary for ANSI SQL seamless native XML integration.

Hierarchical Views

As shown at the top of Figure 1, the left outer joins that model and define the XML documents and the relational hierarchical structures can be defined separately in their own SQL views. An important concept here is that these outer joins are also defining the hierarchical structure metadata and do not necessarily have to represent or perform join operations as in the case of XML physical structures. In all cases, they still represent their structure and data portion in the query’s virtual view shown on the right middle side of Figure 1. The ability to utilize this hierarchical metadata raises these SQL views to a higher more abstract level allowing significantly more freedom in how they are used to perform their ANSI SQL function.

Hierarchical SQL views can be embedded in other hierarchical views or joined dynamically on the invoking SQL statement to hierarchical create larger views. The latter allows for ad hoc data modeling or joining of hierarchical structures at query invocation. This is demonstrated in Figure 1 starting at the top down where the XML view is joined over the RDB view which follows the semantics of the left join operation with its hierarchical data preservation. The ON clause is used to specify the exact join points between structures.

Figure 1 demonstrates a typical hierarchical joining of hierarchical structures where the lower level structure is joined by its root node in a standard manner, keeping the newly formed structure and its hierarchical semantics intuitive. This does not have to be case, Part 1 used an example where the lower level structure was joined under its root node. This powerful view combining capability still produces a valid hierarchical result. It significantly increases the hierarchical view’s flexibility and transparency, and establishes SQL’s left outer join syntax as an advanced hierarchical data modeling language.

Unified Heterogeneous Virtual View

The natural view expansion of the invoking SQL query shown in the middle of Figure 1 demonstrates how the heterogeneous view types automatically merge together to form a single unified SQL view. Since this unified view also includes XML data which may be remotely located, it is also heterogeneous and virtual. This enables a consistent SQL operation across heterogeneous structure types. Also of interest is that the expanded views are automatically nested. This nesting is controlled by the delayed matching ON clause placement caused by expanding embedded views inserting additional joins and their matching ON clauses. Since outer joins are not performed until their matching ON clause is processed, this causes SQL views to be fully materialized before being joined. This occurs in the example in Figure 1 where the lower level RDB view is materialized fully before being joined to the previously materialized XML view. This operation, like the hierarchical operations already covered, is naturally present in the ANSI SQL standard and occurs automatically.

While the nesting of views is not necessary for the query in Figure 1, there are many situations where this automatic operation becomes necessary as in the linking of the lower level structure below its root described in Part 1. It also protects other participating views from possible side effects caused by a view materialization because the nesting process causes the current view being materialized to be isolated in its own working set. This view nesting operation demonstrates that the left outer join is a powerful data modeling language for logical and physical structures and possesses many recombinant SQL capabilities.

For distributed and heterogeneous processing there is an inverse operation to the recombinant SQL capability. When the virtual view is broken up and sent as separate SQL sections to their remote locations, the left outer join query statements that represent these different substructures also inherently carry their hierarchical structure metadata. This automatically enables the distributed processing to be naturally carried out hierarchically in autonomous pieces. It will be described later how the hierarchical processing can enable the distributed processing to perform in a linear fashion.

It is worth mentioning that the left outer join hierarchical data modeling does produce less logic and coding errors. This is because it is more formal in nature and adheres to sound hierarchical principles. This is very similar to the advantages gained by structured programming. These characteristics also enable powerful semantic optimizations that are described in the next section.

Optimization

Earlier in this article, it was mentioned that the data modeling outer join views can be used as metadata that defines the hierarchical structure rather than a more physical (code) definition. This allows both the hierarchical definition of physical structures like XML and the logical data modeling of relational structures. This also allows the hierarchical optimization of physical and logical structures by pruning the unneeded legs of the hierarchical data structure to avoid physical access navigation or logical join operations. This is possible by utilizing the hierarchical semantics of the metadata. Basically, only referenced  nodes and nodes on the path to referenced nodes need to be accessed and processed. Referenced nodes are determined at runtime, so that every request is optimally optimized based on the data requested which is easily added or deleted from the SQL SELECT list at execution.

Notice in Figure 1, the Relational Working Set located along the lower left side and the corresponding Hierarchical Working Data along the right side, do not contain the M or B nodes specified in their respective views. These nodes have been identified in the Unified SQL View by their dashed box and dashed connection lines indicating that they are not referenced or on a path to a referenced node.  This is shown halfway down the right side of Figure 1. This optimization not only reduces data access, but significantly decreases Cartesian product data explosions which cause replicated data and their required additional processing. This has been a significant efficiency problem with XML integration in SQL.

Standard inner join views can not utilize this powerful hierarchical optimization. With inner joins, all tables (or nodes) must be accessed to detect missing data (dangling tuples) in order to remove their incomplete rows from the result. This is not the case with left outer joins that hierarchically preserve rows with missing data. One reason that most SQL databases do not support this optimization is because they do not recognize hierarchical structures. Another reason is that the ANSI SQL specification defines the outer join operation in terms of the inner join causing this optimization not to be considered.

Use of this powerful hierarchical optimization is easily triggered when recognizing the hierarchical virtual structure, and can be applied simply as a pre-step to the normal inner join optimization which is still applicable. This also makes these hierarchical outer join views even more powerful and user friendly than the older inner join views because a single large hierarchical view can do the job of many tailored inner join views.

Normally views comprised of code would be very difficult to optimize globally, code is usually optimized in chunks, but this is not so for hierarchical outer join views with their hierarchical structure metadata. Their Unified SQL Virtual View defined by the expanded outer join views, shown midway down the right side of Figure 1, can be globally optimized utilizing its hierarchical structure metadata. This total control over the virtual view enables the transparent replacement of the relational Cartesian product engine with a hierarchical engine. This will completely eliminate the Cartesian product data replication efficiency problems discussed earlier and enable a linear processing.

The use of a hierarchical engine is depicted near the bottom of Figure 1 where the left side represents relational processing and the right side demonstrates its corresponding hierarchical processing for the same query. Note that in hierarchical processing, the XML data access routine at the bottom center can supply the data directly in hierarchical data form. This complete hierarchical processing can be extremely efficient and can approach linear processing because there is no replicated data and the hierarchical joins are now performed by simple linking operations.

Hierarchical View and Optimization Synergy

Combining the hierarchical SQL view capability with the hierarchical optimization capabilities significantly enhances and extends ANSI SQL’s hierarchical processing capabilities. By optimizing views,  larger more global views can be utilized without increasing overhead, this cuts down on the number of hierarchical views needed. This increases reuse, view abstraction, and ease of use. More significantly, this also allows a predefined view to automatically process any single or multi-leg query under its global view control. This allows fully optimized general purpose views and flexible ad hoc queries unique to SQL for hierarchical processing and native XML integration.

Conclusion

The first two articles in this series demonstrated that ANSI SQL can naturally support basic through advanced native XML hierarchical integration nonprocedurally. This third article has shown how the natural ANSI SQL support of hierarchical views and additional hierarchical optimization have supplied the ease of use and efficiency that makes this SQL native XML integration world class. In addition, the left outer join hierarchical metadata was shown to provide significant flexibility in how the hierarchical processing is carried out internally. This allows the hierarchical processing performed by the Cartesian product relational engine to be  transparently replaced by a hierarchical engine which is considerably more suited for hierarchical processing.

There are two extremely powerful leverage points that ANSI SQL can capitalize on for SQL native XML integration. This is SQL’s inherent hierarchical processing capability and through this capability the automatic utilization of the vast amount of hierarchical semantics naturally in the data structure being accessed. This allows for a full nonprocedural operation where the hierarchical semantics naturally available are utilized automatically to supply the correct hierarchical semantic processing. Because of this automatic operation, the user does not have to specify how the data is to be processed hierarchically. The user just specifies what hierarchical results are needed. Because this nonprocedural processing can automatically process hierarchical queries involving extremely complex hierarchical logic utilizing the data’s own semantics, it increases the value of the data by making this advanced processing more practical and available.

The nonprocedural capabilities described above are not being fully utilized today by XML query products. This is evident by their required procedural programming of the complex hierarchical semantics needed to process the query. For further information on the SQL hierarchical processing and native XML integration capabilities described in this article please refer to: www.adatinc.com.

Recap of unique SQL Native XML integration capabilities:

   *  Full Nonprocedural Multi-leg Hierarchical Processing

• Efficient and accurate hierarchical processing

• Advanced XML processing naturally supported

• Uses hierarchical semantics to increase the value of data

   *  Uses Only ANSI SQL Centric Syntax

• XML integration is transparent and complete

• All results are ANSI SQL accurate

• Fully dynamic operation supports ad hoc queries

   *  Hierarchical Unified SQL Virtual Views

• Ease of use, user does not need to know the data structure

• Seamless heterogeneous processing

• Single global view works for all SQL queries under its control

Copyright 2004 by Advanced Data Access Technologies, Inc.