Re: CONNECT BY / START WITH query to count "families"

My answer isn't as exotic as the previous poster's, but it also simpler
to understand and doesn't involve you labelling the original values
with primes (it may also be less efficient - you'll have to check).
The basic idea is to create a copy (TempRelations) of your Relations
table (assuming that's the ACCT/REL_ACCT table you give an example of
above), then insert one row from it into a new table (MyFamilies),
using its ROWID as a "Family ID".

Then iterate the following steps until no more copying can be done:

1. Insert all ACCTS whose REL_ACCT is in MyFamilies
2. Insert all REL_ACCTS whose ACCT is in MyFamilies
3. Delete all rows from TempRelations which you've inserted into
MyFamilies

Once that dies down, insert a single row from TempRelation into
MyFamilies & repeat again, until there are no more rows left in
TempRelation.

When you're done, the MyFamilies table will have all the families in
it, with FamilyID identifying a family. Because you allow relations to
go both ways (such as rows 2 & 3 in your example), there will be dupes
in the table that you can remove with a SELECT DISTINCT after the fact.

SELECT COUNT(DISTINCT FamilyID) FROM MyFamilies gives you the number of
families.

SELECT DISTINCT ACCT From MyFamilies where FamilyID = X gives you the
members of a family.

You may want to add some indexes to the TempRelation & MyFamily tables
in order to make this more efficient.

Implementation:

CREATE TABLE TempRelation AS
SELECT ACCT
, REL_ACCT
FROM Relations;

CREATE TABLE MyFamilies AS
SELECT ROWID FamilyId
, ACCT
, ROWID OrigRowId
FROM TempRelation
WHERE Rownum <= 1;

BEGIN
DECLARE
MembersCreated NUMBER := 1;
FamiliesCreated NUMBER := 1;
BEGIN
WHILE FamiliesCreated > 0 LOOP
WHILE MembersCreated > 0 LOOP
INSERT
INTO MyFamilies
SELECT MyFamilies.FamilyId
, TempRelation.ACCT
, TempRelation.ROWID
FROM MyFamilies
, TempRelation
WHERE TempRelation.REL_ACCT = MyFamilies.ACCT;

MembersCreated := SQL%ROWCOUNT;

INSERT
INTO MyFamilies
SELECT MyFamilies.FamilyId
, TempRelation.REL_ACCT
, TempRelation.ROWID
FROM MyFamilies
, TempRelation
WHERE TempRelation.ACCT = MyFamilies.ACCT;

MembersCreated := MembersCreated + SQL%ROWCOUNT;

DELETE TempRelation
WHERE ROWID IN
( SELECT OrigRowId
FROM MyFamilies);

END LOOP;

INSERT
INTO MyFamilies
SELECT ROWID FamilyId
, ACCT
, ROWID OrigRowId
FROM TempRelation
WHERE rownum <= 1;

FamiliesCreated := SQL%ROWCOUNT;
MembersCreated := 1;
END LOOP;
END;
END;
/

-Steve

.