Como realizar consultas jerarquicas en Oracle

He encontrado por ahí un viejo cuaderno donde me apuntaba cosillas del curro. Echandole un vistazo, tristemente esta todo desfasado y ya es de poca utilidad. Pero he encontrado una cosilla que no esta del todo mas. Y es como hacer consultas jerarquicas o recursivas en Oracle.

Vamos a partir de un esquema muy básico para que podamos explicarlo. Y para poder obtener los datos de forma jerarquica necesitaremos de dos comandos principales: 'start with' y 'connect by'. Si solo queremos hacerlas jerarquicas usaremos 'connect by prior'. Ejemplo:

select * from jerarquia start with ID=1 connect by prior ID = PARENT_ID;

   ID   |   PARENT_ID   |
   01   |
   03   |      01
   05   |      01

Además podemos añadirle clausulas al 'connect by' para filtrar aún más la búsqueda jerarquica:

select * from jerarquia start with ID=1 connect by ID != 3 AND prior ID = PARENT_ID;

   ID   |   PARENT_ID   |
   01   |
   05   |      01

También podemos mostrar el nivel de profundidad en el que se encuentran los registros:

select ID, PARENT_ID, LEVEL from jerarquia start with ID=1 connect by prior ID = PARENT_ID;

   ID   |   PARENT_ID   | LEVEL
   01   |               |   1
   03   |      01       |   2
   05   |      01       |   2

También podemos hacer busquedas inversas, simplemente cambiando el orden de la clausula:

select ID, PARENT_ID, LEVEL from jerarquia start with ID=5 connect by ID prior PARENT_ID = ID;

   ID   |   PARENT_ID   | LEVEL
   05   |      01       |   1
   01   |               |   2

También podemos hacer que se muestre el camino de la jerarquía

SELECT ID, PARENT_ID, LEVEL, SYS_CONNECT_BY_PATH(ID,'->') ARBOL from jerarquia start with ID=1 connect by ID prior ID = PARENT_ID;

   ID  |  PARENT_ID   | LEVEL | ARBOL
   01  |              |   1   |  ->1
   03  |     01       |   2   |  ->1->3
   05  |     01       |   2   |  ->1->5

También permite ordenar los registros en base a un tercer campo. El ejemplo sería algo así:

select * from jerarquia start with ID=1 connect by prior ID = PARENT_ID ORDER SIBLINGS BY TERCER_CAMPO;

Podeis ver más información en la página oficial.