Re: Ugly PL/SQL Code

cdavis10717_at_comcast.net (Charles) wrote in message news:<83dbb3cd.0402051050.249941e4_at_posting.google.com>...
> I am clearly no PL/SQL expert, but can anymore make this select less
> complex.
>
> I wanted to get a count of the files for each tablespace within the
> file systems of my server.
>
> The first 21 characters of all my Oracle database file names are the
> file systems names they live on.
>
> It was a one-time thing for me, I got it working, but it could have
> been easier.
>
> What could be done do this to make it less complex? Thanks.
>
> Charles
>
> select
> TS,
> FS,
> count(*)
> from
> (select
> TS,
> substr(DF,1,21) AS FS,
> 1
> from
> (select
> *
> from
> (select
> a.name TS,
> b.name DF
> from
> v$tablespace a,
> v$datafile b
> where
> a.ts# = b.ts#
> order by
> 1, 2)
> order by
> TS,
> DF)
> )
> group by
> TS,
> FS
> order by
> TS,
> FS
> ;

Less aliasing, fewer inline views, and only the final order by affects the output.

Something like this.

SQL> select TS, FS, count(*)
  2 from (
  3 select a.name TS, substr(b.name,1,21) FS   4 from v$tablespace a, v$datafile b   5 where a.ts# = b.ts#
  6 )
  7 group by TS, FS
  8 order by TS, FS;

Hth

-- 
Martin Burbridge
Add one to email for a currently valid address.