Re: double linked list

>> But unless you are going to state that the above can NOT be done in
Oracle ... the fact that CONNECT BY exists is not license to call the product, as you did, 'horrible'. <<

How about if I say the code to do tree manipulation with CONNECT BY is orders of magnitude slower, not Standard, not portable and quickly becomes a nightmare of nested subqueries to maintain? Once more, let me demonstrate my points with actual code. (If that dos not work, what will convince you that I have a valid position?)

The query "Show all subcomponents of part A1, including the substructure" can be handled by the following Oracle SQL statement:

 SELECT LEVEL AS pathlength, assemblyno, subassemblyno    FROM Blueprint
  START WITH assemblyno = 'A1'
CONNECT BY PRIOR subassemblyno = assemblyno;

The CONNECT BY ... PRIOR clause provides traversal but not support for recursive aggregate functions. For example, it is not possible to sum the weights of all subcomponents of part A1 to find the weight of A1. The only recursive function supported by the CONNECT BY ... PRIOR clause is the LEVEL function.

Another limitation of the CONNECT BY ... PRIOR clause is that it does not permit the use of joins. The reason for disallowing joins is that the order in which the rows are returned in the result is important. The parent nodes appear before their children, so you know that if the pathlength increases, these are children; if it does not, they are new nodes at a higher level.

This also means that an ORDER BY can destroy any meaning in the results. This means, moreover, that the CONNECT BY ... PRIOR result is not a true table, since a table by definition does not have an internal ordering. In addition, this means that it is not always possible to use the result of a CONNECT BY query in another query.

A trick for working around this limitation, which makes indirect use of the CONNECT BY ... PRIOR clause, is to hide it in a subquery that is used to make a JOIN at the higher level. For example, to attach a product category description, form another table to the parts explosion.

SELECT part_nbr, category_name
  FROM Parts, ProductCategories
 WHERE Parts.category_id = ProductCategories.category_id    AND part_nbr IN (SELECT subassemblyno

                     FROM Blueprint
                    START WITH assemblyno = 'A1'
                  CONNECT BY PRIOR subassemblyno = assemblyno);

The subquery has only one table in the FROM clause and complies with the restriction that there must be no joins in any query block that contains a CONNECT BY ... PRIOR. On the other hand, the main query involves a JOIN of two tables, which would not be possible with direct use of the CONNECT BY ... PRIOR clause. Another query that cannot be processed by direct use of the CONNECT BY ... PRIOR clause is one that displays all parent-child relationships at all levels. A technique to process this query is illustrated by the following SQL:

SELECT DISTINCT PX.part_nbr, PX.pname, PY.part_nbr, PY.pname   FROM Parts AS PX, Parts AS PY
 WHERE PY.part_nbr IN (SELECT Blueprint.subassemblyno

                       FROM Blueprint
                      START WITH assemblyno = PX.part_nbr
                    CONNECT BY PRIOR subassemblyno = assemblyno)

Again, the outer query includes a JOIN, which is not allowed with the CONNECT BY ... PRIOR clause. Note that the correlated subquery references PX.part_nbr.
As you try to do more, the subquery nesting gets worse.

The basic problem is that CONNECT BY is a cursor (procedural code) and SQL is set-oriented. They don't work together very well

>> So are you complaining because there is a less superior method
available for those not as technically proficient as you, are you complaining because that less superior implementation doesn't exist in Transact SQL, or are you complaining just because you don't personally work in Oracle? <<

Why do you think I like T-SQL? The T-SQL newsgroups have more SQL programming problems than other product newsgroups, which tend to focus on particulars of their product. While SQL Server is getting better (so is Oracle)and I hope Yukon cleasns up some of the old "Sybase Code Museum" problems, but it still has some major problems in the basic underlying model.

I am trying to figure out "those not as technically proficient as me"?? I think that anyone can understand the nested sets or nested intervals model if they understand HTML or XML or work in a block structured language -- it is the same concept. It is all just "parentheses in disguise", not rocket science. I have not lost an audience when I teach it and several times programmers who were introduced to the technique for the first time came up with some stuff I had not thought of before -- they started drawing circles inside circles (i.e. set diagrams) instead of "boxes and arrows" (i.e. sequential travesals) and looked at their problems differently. Received on Wed Feb 05 2003 - 03:49:04 CET