A recursive relationship is one where a table has a foreign key to itself. for example, the scott/tiger table EMP has an EMPNO as the primary key and the MGR as a foreign key to itself. That relationship is recursive -- a person is both an EMPLOYEE and a MANAGER
potentially.
To query that we use the connect by statement, for example:
select lpad( '*', level, '*' ) || ename ename
from emp
start with mgr is NULL
connect by prior empno = mgr
ENAME
-------------------------
*KING
**JONES
***SCOTT
****ADAMS
***FORD
****SMITH
**BLAKE
***ALLEN
***WARD
***MARTIN
***TURNER
***JAMES
**CLARK
***MILLER
14 rows selected.
That starts with MGR is null which is KING. He has no manager and is at the top of our hierarchy. We then connect this starting record to every other record such that the other records MGR column equals this records EMPNO column. We do that for every record we find until we hit the end of the recursion.
The level psuedo column tells us how deep into the tree we are. I'm using it here to indent the data so you can see the hierarchy.