Q:

oracle ancestors in hierarchical queries

Given table T_Person (name, parent) containing data

+--------+--------+
| name   | parent |
+--------+--------+
| john   | peter  |
| peter  | ronald |
| ronald | george |
| george |        |
+--------+--------+

one can find relations using this query:
select name, parent, LEVEL
from T_Person
connect by prior name = parent
start with parent is null;

the result would be:

+--------+--------+-------+
| name   | parent | LEVEL |
+--------+--------+-------+
| john   | peter  | 4     |
| peter  | ronald | 3     |
| ronald | george | 2     |
| george |        | 1     |
+--------+--------+-------+

That's fine so far. But I want to have a result containing all relations that looks this way:

+--------+--------+----------------+
| name   | parent | relation_level |
+--------+--------+----------------+
| john   | peter  | 1              |
| peter  | ronald | 1              |
| ronald | george | 1              |
| john   | ronald | 2              |
| peter  | george | 2              |
| john   | george | 3              |
+--------+--------+----------------+

(relation_level: 1 = father, 2 = grand father, 3 = grand grand father and so on)
Is there a fast oracle way to receive this result, other than select on the whole table for each relation level?

A:

Or you can remove the start with clause
SELECT name, ancestor AS parent, l
FROM
(
  SELECT name, parent, LEVEL-1 l, connect_by_root name ancestor
    FROM T_Person
 CONNECT BY PRIOR name = parent
) t
WHERE t.ancestor <> t.name

Here is a sqlfiddle demo

A:

create table ancestor (name varchar2(200), parent varchar2(200));

insert into ancestor values ('john','peter');
insert into ancestor values ('peter','ronald');
insert into ancestor values ('ronald','george');
insert into ancestor values ('george',null);

 select  name,parent,relation_level from 
(

 SELECT CONNECT_BY_ROOT name name, parent,level relation_level
 FROM ancestor
 CONNECT BY name = PRIOR parent
  START WITH name IN (SELECT name FROM ancestor)
 )
where parent is not null;

SQLFIDDLE

