Estoy teniendo muchísimo tiempo con esto. Estoy tratando de escribir una consulta (usando Oracle), en una tabla con una relación recursiva (jerárquica) y obtener el número total de registros almacenados en otra tabla en y debajo de cada nodo en el árbol. La otra tabla solo tiene registros asociados con los nodos de hoja. Sin embargo, quiero obtener totales en y debajo de cada nodo en el árbol. Por ejemplo, digamos que tengo dos tablas. DIRS contiene los nombres de directorio y una relación recursiva que identifica la estructura de los directorios, y FILES contiene información del archivo con una clave externa a DIRS que indica el directorio en el que reside el archivo:

DIRS
====
DIR_ID 
PARENT_DIR_ID
DIR_NAME

FILES
=====
FILE_ID
FILE_NAME
DIR_ID
FILE_SIZE

Si DIRS contiene:

DIR_ID   PARENT_DIR_ID   DIR_NAME
======   =============   ========
1                        ROOT
2        1               DIR1_1
3        1               DIR1_2
4        2               DIR2_1
5        2               DIR2_2

y archivos contiene

FILE_ID   FILE_NAME   DIR_ID   FILE_SIZE
=======   =========   ======   =========
1         test1.txt   5        100
2         test2.txt   5        200
3         test5.txt   5         50 
4         test3.txt   3        300
5         test4.txt   3        300
6         test6.txt   4        100

Quiero una consulta que devuelva la ruta junto con el número de archivos en o debajo de cada nodo en la jerarquía. Básicamente un rollup de la cantidad de archivos. Entonces el resultado de la consulta se vería algo así como:

Path                    File_Count
=====                   ===========
/ROOT                   6
/ROOT/DIR1_1            4
/ROOT/DIR1_1/DIR2_1     1
/ROOT/DIR1_1/DIR2_2     3
/ROOT/DIR1_2            2

ACTUALIZAR Script SQL para crear las tablas con datos de ejemplo para que coincidan con los anteriores:

create table DIRS (dir_id number(38) primary key
    , parent_dir_id number(38) null references DIRS(dir_id)
    , dir_name varchar2(128) not null);

create table FILES (file_id number(38) primary key
    , file_name varchar2(128) not null
    , dir_id number(38) not null references DIRS(dir_id)
    , file_size number not null
    , unique (dir_id, file_name));

insert into DIRS 
select 1, null, 'ROOT' from dual
union all select 2, 1, 'DIR1_1' from dual 
union all select 3, 1, 'DIR1_2' from dual 
union all select 4, 2, 'DIR2_1' from dual 
union all select 5, 2, 'DIR2_2' from dual;

insert into files
select 1, 'test1.txt', 5, 100 from dual
union all select 2, 'test2.txt', 5, 200 from dual
union all select 3, 'test5.txt', 5, 50 from dual
union all select 4, 'test3.txt', 3, 300 from dual
union all select 5, 'test4.txt', 3, 300 from dual
union all select 6, 'test6.txt', 4, 100 from dual;

commit;