|
Trends
Logic in DB
Drivers
ODBC
JDBC
OLE DB
.NET
Podcast
SQL:2003
MS SQL 2005
Webcast
SQL:2003
MS SQL 2005
|
|
<<Previous 1 2 3 4 Next>>
With hierarchical processing being a natural subset of relational processing, its results are both hierarchically accurate and SQL correct.
Hierarchical processing is based on solid hierarchical principles. These principles are automatically followed in the non-procedural SQL query processing of multi-leg hierarchical structures. An example of this nonlinear multi-leg processing is selecting data from one leg of the structure based on data from another leg of the structure. This level of hierarchical processing is not being supported today because of the two-dimensional mindset influence of relational processing. Even XQuery, now used in SQL to supplement XML integration, does not easily support multi-leg queries. It requires complex procedural code to correlate the semantics between the legs of the structure specific to the query. The more legs that are referenced in a query, the more complex the correlation programming must be. This usually makes complex multi-leg processing in XQuery impractical in many cases.
|
SQL’s natural hierarchical processing inherently supports multi-leg queries because the correlation logic required, known as Lowest Common Ancestor (LCA) logic, is automatically being performed by the relational engine. This naturally forms Cartesian products under the lowest common relationship nodes where its processing automatically simulates LCA processing.
|
Distracters of this advanced hierarchical processing would downplay this full multi-leg processing capability as not being necessary. Again this is short sighted two-dimensional relational thinking because multi-leg processing naturally includes the capability to processes any single leg query non-procedurally and without knowledge of the hierarchical structure.
The multi-leg capability also increases the value of the customers’ data. This happens by the inherent utilization of powerful semantics in the hierarchical structure brought about by the natural LCA processing. This correlation of semantics between legs increases the meaning of standard multi-leg SQL queries, producing more meaningful results than each single leg queried separately. This automatic LCA process also ensures valid hierarchical processing is performed for single or multi-leg queries. For these reasons, the value and use of this multi-leg nonlinear processing capability can not be dismissed easily.
SQL hierarchical processing works -- what about its efficiency?
SQL’s natural hierarchical processing works so seamlessly because the left outer join syntax naturally and exactly models hierarchical structures and left outer join semantics defines its hierarchical results. This is not magic; these operations are defined in the ANSI SQL specification. With this established, the efficiency of this solution has been called into question by SQL vendors. This is because native XML documents have been shown to often contain very deep hierarchies that severely affect the efficiency of outer join access. Two-dimensional relational mindset thinking assumes the use of outer join logic to access the XML hierarchical structure. This is not necessary. When we access hierarchical physical structures like XML, we use the outer join definition only to specify the hierarchical structure metadata. We access the hierarchical data source in the most hierarchically straightforward and efficient manner possible. This is performed by returning the outer join defined rowset result without having to simulate expensive outer join operations, only its results. In this way, there is no overhead from the outer join definition, because no join processing is taking place.
The processing of outer joins against hierarchically-defined table relationships can be efficient using hierarchical access optimization. This eliminates the access of unneeded hierarchical paths defined in SQL hierarchical views. This can be a dynamic process, performed at execution time, when it can be determined what data is actually needed for the query. Without changing its meaning, a query can ignore unneeded hierarchical paths. This allows the use of global SQL hierarchical views with no overhead, regardless of what data is required. Coupled with SQL’s non-procedural navigation-less access, this has significant synergy for user friendly and efficient processing of complex hierarchical structures.
The use of hierarchical optimization greatly reduces the Cartesian product data explosions that would occur with large views causing many unnecessary joins. One current industry efficiency solution for this multi-leg data explosion problem is the Sorted Outer Union (SOU) technique. The SOU avoids joins by:
1. Separating different legs of a structure by selecting them separately
2. Unioning them next to each other.
Another similar industry solution performs separate queries on each leg. These current efficiency solutions use two-dimensional relational linear thinking, which prevents normally automatic SQL nonlinear multi-leg processing from occurring naturally in the relational engine. This demonstrates a total unawareness of the full multi-leg hierarchical processing occurring naturally in SQL.
<<Previous 1 2 3 4 Next>>
Database Server Watch SQL Summit Home Page Articles
© 2005, Ken North Computing LLC, All rights reserved.
|
|
|
