Re: double linked list

--CELKO-- wrote:

> >> 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)
> ORDER BY PX.part_nbr, PY.part_nbr;
>
> 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.

What Richard said! And triple it!

Your hyperbole was not directed at a single feature that, as you have demonstrated, can be ignored by those technically proficient enough to do so. But rather at the entire product. This type of generalization is not only meaningless ... it is counterproductive.

Daniel Morgan Received on Wed Feb 05 2003 - 11:16:50 CST