Um caso que ocorre com frequência é ter estruturas hierárquicas em
tabelas. Um exemplo típico será uma tabela empregado, que pode ter e/ou
ser um supervisor. Ou seja, temos uma chave estrangeira que aponta para
a chave da própria tabela, algo tipo

ID_EMP  NOME           SUPERVISOR
------- -------------- ------------
1       João           NULL
2       Pedro          1
3       Ricardo        1
4       José           2

Em que o João é supervisor do Pedro e do Ricardo e o Pedro é supervisor do José.

Por vezes, temos uma estrutura deste tipo mais ou menos complexa,
com mais ou menos níveis de profundidade e queremos saber, com base em um
identificador, todos os resultados hierarquicamente – neste caso, ao
perguntar quais os supervisionados hierárquicos do João, teríamos Pedro, Ricardo e José.

Tipicamente, até agora fazia uma função em pl/sql que fizesse isto,
pois não conhecia algumas das facilidades que o Oracle fornece para
isto.

Introducing CONNECT BY

DBAs e pessoal com alguma experiência em Oracle devem conhecer isto, mas para mim foi uma novidade e foi-me imensamente útil.
Com esta diretiva, consigo fazer o seguinte:

SELECT
  NOME, LEVEL
FROM
  EMPREGADO
START WITH
  NOME='João'
CONNECT BY NOCYCLE
  PRIOR ID = SUPERVISOR

O resultado desta query seria aproximado a:

NOME           LEVEL
-------------- -------
João           1
Pedro          2
Ricardo        2
José           3

O que se fez foi dizer que queríamos começar com o registro com o
nome João e “ligá-lo” aos seus filhos. Isto é feito com a cláusula
CONNECT BY e usando PRIOR num dos campos. Podemos pensar seguindo da
raiz, de registro em registro iterativamente, sendo que na primeira
iteração o PRIOR ID é o campo do registro raiz e estamos selecionando,
como próximos campos, todos os que tenham SUPERVISOR = PRIOR ID.

A palavra NOCYCLE é para não se entrarem em ciclos, e a coluna LEVEL que
está sendo usada é uma pseudo-coluna que indica o nível em que está o
registro em relação à raiz (sendo a raiz o nível 1).

Podem encontrar uma explicação mais completa aqui.