Получение подсчетов / итогов на каждом уровне иерархического запроса с использованием CONNECT BY

У меня чертовски много времени с этим. Я пытаюсь написать запрос (с использованием Oracle) к таблице с рекурсивным отношением (иерархическим) и получить общее количество записей, хранящихся в другой таблице на каждом узле дерева и ниже. Другая таблица имеет только записи, связанные с конечными узлами. Тем не менее, я хочу получить итоги в и ниже каждого узла в дереве. Например, скажем, у меня есть две таблицы. DIRS содержит имена каталогов и рекурсивные отношения, идентифицирующие структуру каталогов, а FILES содержит информацию о файле с внешним ключом для DIRS, указывающим каталог, в котором находится файл:

DIRS
====
DIR_ID 
PARENT_DIR_ID
DIR_NAME

FILES
=====
FILE_ID
FILE_NAME
DIR_ID
FILE_SIZE

Если DIRS содержит:

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

и ФАЙЛЫ содержат

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

Я хочу запрос, который возвращает путь вместе с количеством файлов в или ниже каждого узла в иерархии. В основном сведение количества файлов. Таким образом, результат запроса будет выглядеть примерно так:

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

UPDATE SQL-скрипт для создания таблиц с примерами данных, соответствующих приведенному выше:

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;

Ответы на вопрос(2)

Ваш ответ на вопрос