Re: Removing duplicate subtrees from CONNECT-BY query

Riku,

    Great question. Unfortunately, CONNECT merely refers to a particular way of traversing the rows, and there is, to my knowledge, nothing to tell Oracle to prune a subdirectory it has already visited - it would mean remembering all the nodes, ouch. Duplicates are just a natural thing in trees. We all have ancestors who appear several times in our family tree. You could of course try to manage it yourself with a cursor, a PL/SQL table and a stored procedure but I think you mentioned performance ...

If I understand well your problem, the question is "what are the descendants of <some thing that identify a row here> (condition #1) that also verify <something that tells you you have what you want> (condition #2).
What about reversing the problem? If condition #2 is selective enough, perhaps it would be simpler to search for condition #2 first, and check that there is a row among the "ancestors" of the row found that verify condition #1? I assume implicitly, of course, that you don't want to do this for millions of rows, because then all things considered a brutal DISTINCT on the result set may well be the most efficient ...

Short of that, I would question the design ...

HTH Stéphane Faroult

Riku Räsänen wrote:
> Hello,
>
> Certain application has a table with huge hierarchy, and a subtree of
> the hierarchy is allowed to exist in several places in the tree. No
> loops are allowed though.
>
> The problem is that this hierarchy has to be searched effectively.
> Querying a certain top-level hierarchy returns over 600 000 DISTINCT
> nodes. With the duplicate subtrees, the query returns almost 6 000 000
> nodes, where certain node appears 2000 times in the result.
>
> Of course the requirement is that the search has to be effective and
> even this worst case of 6M rows should be handled in reasonable time
> (this is an OLTP application). This "subtree allowed to appear several
> times" is completely new case for me in the land of CONNECT BY's. Did
> RTFM, did "use the Google, Luke" etc, but did not find anything sensible.
>
> So the question is: What is the most efficient way of removing the
> duplicates of this resultset? Currently there is no way to identify
> duplicate subtrees from the data except that the same ID appears
> multiple times. Is there a way to make CONNECT BY -operator to
> identify and prune the duplicate subtrees?

--
http://www.freelists.org/webpage/oracle-l