Re: Can anyone suggest a better way to write this query?
Date: Thu, 19 Nov 2009 15:26:44 -0800 (PST)
Message-ID: <c8cb5449-9fff-460a-baf9-fd4b26e52c68_at_r5g2000yqb.googlegroups.com>
On Nov 19, 5:05 pm, C M <cmcmzz..._at_gmail.com> wrote:
> On Nov 19, 4:33 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > This appears to be a hard problem. To avoid headaches, make certain
> > that each of the DIR_NAMES ends with \
>
> > Let's start here:
> > SELECT
> > 'c:\aaa\bbb\ccc\ddd\' DIR_NAME,
> > 100 MBYTES
> > FROM
> > DUAL;
>
> > DIR_NAME MBYTES
> > -------------------- ----------
> > c:\aaa\bbb\ccc\ddd\ 100
>
> > In your example, you would like to put 100MB into the following
> > directories based on the above:
> > c:\
> > c:\aaa\
> > c:\aaa\bbb\
> > c:\aaa\bbb\ccc\
> > c:\aaa\bbb\ccc\ddd\
>
> > You somehow need to be able to break that one row into 5 rows. The
> > following might help
> > SELECT
> > LEVEL L
> > FROM
> > DUAL
> > CONNECT BY
> > LEVEL<=20;
>
> > L
> > ---
> > 1
> > 2
> > 3
> > 4
> > 5
> > 6
> > 7
> > 8
> > 9
> > 10
> > 11
> > 12
> > 13
> > 14
> > 15
> > 16
> > 17
> > 18
> > 19
> > 20
>
> > If we join those two row sources together we might be able to create 5
> > rows from the one row:
> > SELECT
> > SUBSTR(DIR_NAME,1,INSTR(DIR_NAME,'\',1,L)) DIR_NAME2,
> > MBYTES
> > FROM
> > (SELECT
> > 'c:\aaa\bbb\ccc\ddd\' DIR_NAME,
> > 100 MBYTES
> > FROM
> > DUAL) DIR_SIZE,
> > (SELECT
> > LEVEL L
> > FROM
> > DUAL
> > CONNECT BY
> > LEVEL<=20) C
> > WHERE
> > SUBSTR(DIR_NAME,1,INSTR(DIR_NAME,'\',1,L)) IS NOT NULL;
>
> > DIR_NAME2 MBYTES
> > -------------------- ----------
> > c:\ 100
> > c:\aaa\ 100
> > c:\aaa\bbb\ 100
> > c:\aaa\bbb\ccc\ 100
> > c:\aaa\bbb\ccc\ddd\ 100
>
> > Now, if we performed the same process for all of the rows in the
> > DIR_SIZE table, grouping on DIR_NAME2, we might be able to find the
> > SUM of the MBYTES column.
>
> > I will leave the rest for your experimentation. You probably will not
> > have 20 \ characters in the DIR_NAME column, so you could optimize the
> > SQL statement a bit.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
> Thanks for the suggestion. I suspect the best way will involve some
> kind of recursive processing. The tricky bit is the matching of the
> rows in the directories table to the rows in the dir_size table. We
> need to do a "like" (which we can't, of course) which is why I thought
> of the instr.
>
> CM
The LIKE keyword is not necessary.
Notice how closely the output of the following SQL statement:
SELECT
'c:\aaa\bbb\ccc\ddd\' DIR_NAME,
100 MBYTES
FROM
DUAL;
Matches the row created by one of your insert statements:
insert into dir_size values ('c:\aaa\bbb\ccc\ddd', 100);
You might try replacing in the above examples:
SELECT
'c:\aaa\bbb\ccc\ddd\' DIR_NAME,
100 MBYTES
FROM
DUAL;
With a SQL statement that selects all of the rows from your DIR_SIZE
table - the results might surprise you IF each of the DIR_NAME values
end with a \
You really need more variety in the insert statements to see what is
happening, for example:
insert into dir_size values ('c:\ddd\', 800);
insert into dir_size values ('c:\ddd\kkk\', 300);
The first of the above SQL statements will increase the calculated SUM in the c:\ directory by 800, and the second insert statement will increase the SUM in both of the c:\ and c:\ddd\ directories by 300 if you modify my original example to use the DIR_SIZE table rather than the DUAL table.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Thu Nov 19 2009 - 17:26:44 CST