Nonlinear Hierarchical Processing is More Powerful
Previosly I have described SQL’s inherent hierarchical Outer Join Data Modeling Language capabilty and how to specify it. SQL can automatically perform hierarchical processing by executing these standard SQL hierarchical outer join specifications. This article will demonstrate how to define and utilize global SQL views to easily join these SQL defined hierarchical structures so that non technical users can do this without knowing the structure of the data structures involved in the query.
Below you will notice that hierarchical views ABC and XYZ are created. Then a global view is created by joining the full structures of the ABC and XYZ views together. The represented structures are shown modeled next to their view definitions. In these examples, the SQL tables A, B, C, X, Y and Z also represents nodes in the hierarchical structures being modeled. These tables can also be thought of as XML elements and their data types.
Model ABC View
CREATE VIEW ABC ASA SELECT * FROM A| LEFT JOIN B ON A.a=B.bB LEFT JOIN C ON B.b=C.c| C
Model XYZ View
CREATE VIEW XYZ AS SELECT * FROM XX LEFT JOIN Y ON X.x=Y.y/\ LEFT JOIN Z ON X.x=Z.z YZ
The above two hierarchical SQL views are hierarchically joined to create the GLOBAL view below. In actuality, since this is a view the join operation is not performed until the view is invoked in real-time.
Create GLOBAL View
CREATE VIEW GLOBAL AS SELECT * FROMABC LEFT JOIN XYZ ON A.a=X.x
The modeled GLOBAL view above generated the SQL that defines its joined structures shown below. Notice how the lower level view, XYZ, is nested under the upper view, ABC, by its natural SQL view expansion with the automatic placement of the ON clauses. This causes the lower view, XYZ, to be fully materialized before being joined to the upper view, ABC. This allows for joining of other physical type structures such as XML or performing advanced hierarchical capabilities such as look-ahead or linking below the root of the lower structure.
GLOBAL View’s SQL and Structure
SELECT * FROM AA LEFT JOIN B ON A.a=B.b/\ LEFT JOIN C ON B.b=C.cBX LEFT JOIN X |/\ LEFT JOIN Y ON X.x=Y.yCYZ LEFT JOIN Z ON X.x=Z.z ON A.a=X.x
The SQL statement below submits a query against the GLOBAL view structure. This is a global view because not all of the nodes were referenced for return and there was no overhead. This means any query can be specified for this view. This would normally cause additional overhead in SQL because a smaller view tailored to the query request could be used to avoid accessing unnecessary pathways.
Navigationless SQL Query
SELECT A, C, X, Z FROM GLOBAL With hierarchical structures and their hierarchical data preservation, unnecessary pathways can be ignored without affecting the results. This optimization is supported in relational theory by the Universal Relation operation which can be used with the hierarchical OJDML specification. This optimization makes global views very powerful and easy to use for the user, who does not need to navigate or even know the data structure. The below structure is produced from the query shown above.
Produced Structure: A / \ C X | Z The above structure is produced from the previous query above run against the GLOBAL view. It only returned the nodes that were selected for return as shown in the SQL query above. The Y node was not selected in the SELECT List and was optimized out without being processed at all. The removal of the intervening B node by not being selected in the query SELECT list caused node promotion of the preserved C node which was included in the query SELECT list. This is standard for hierarchical processing. Interestingly, this node promotion operation is mirrored in relational processing as are most hierarchical processing operations performed in SQL. The B table while not included in the SQL SELECT List still required access and temporary storage in the working set in order to gain access to the qualifying node occurrences of the C table. After the C table is retrieved into the SQL working set, the B table can be sliced out leaving the Ctable and C node directly related under the A node. This node promotion is automatically performed by relational projection driven directly the SELECT list operation.