Can anyone suggest a better way to write this query?

From: C M <cmcmzzzz2_at_gmail.com>
Date: Thu, 19 Nov 2009 12:10:01 -0800 (PST)
Message-ID: <b4ccc6eb-f3ef-448a-93e7-615a74d785de_at_k17g2000yqh.googlegroups.com>

Table dir_size stores the mbytes of storage used in a given directory. Table directories stores various directory names which may or may not exist in table dir_size.
For every directory in table directories, report the cumulative storage in that directory and all its subdirectories. This solution uses a cartesian join. I imagine it will not scale well.

Thanks in advance!

create table dir_size (

  dir_name     varchar2(40),
  mbytes       number

);

create table directories (
  dir_name varchar2(40)
);

insert into dir_size values ('c:\aaa\bbb\ccc\ddd', 100);
insert into dir_size values ('c:\aaa\bbb\ccc', 100);
insert into dir_size values ('c:\aaa\bbb', 100);
insert into dir_size values ('c:\aaa', 100);
insert into dir_size values ('c:\', 100);

insert into directories values ('c:\aaa\bbb\ccc\ddd');
insert into directories values ('c:\aaa\bbb\ccc');
insert into directories values ('c:\aaa\bbb');
insert into directories values ('c:\aaa');

insert into directories values ('c:\');
insert into directories values ('c:\xxx\yyy\zzz');

commit;

select dir_name, sum(mbytes) from (
  select directories.dir_name,
    instr(dir_size.dir_name, directories.dir_name) INSTR,     mbytes
  from directories, dir_size
)
where INSTR = 1
group by dir_name
order by 1;

DIR_NAME                                 SUM(MBYTES)
---------------------------------------- -----------
c:\                                              500
c:\aaa                                           400
c:\aaa\bbb                                       300
c:\aaa\bbb\ccc                                   200
c:\aaa\bbb\ccc\ddd                               100

Received on Thu Nov 19 2009 - 14:10:01 CST