TechNotes
Logic in DB
Beyond Java
Data Access
SOX
SQL/XML
Restructuring
Trees
Open Source
BizIntel
MySQL
Drivers
ODBC
JDBC
OLE DB
.NET
Podcast
SQL:2003
MS SQL 2005
XML DBs
XQuery
Webcast
XQuery
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.
|
|
|
