Re: Help! This group by stmt takes AGES

Daniel Morgan <damorgan_at_exesolutions.com> wrote in message news:<3CC88333.C2696B90_at_exesolutions.com>...
> Frank Zaum wrote:
>
> > Hello All,
> >
> > I have created a table (space_values) that contains the out parameters
> > of the dbms_space package. on each day, a new row
> > is created in this table. in a separate table (space_tables) i have
> > the table names and owners of the tables i analyze
> > with dbms_space. Now i want to query the growth of the tablespaces
> > using
> > my space_values and dba_tables (or all_tables).
> > For that purpose, i wrote this query:
> > (with hwm=(total_blocks-unused_blocks-1) and datum = date when
> > analyzed
> > in space_values are columns owner, table_name and datum the primary
> > key
> >
> > select distinct aa.datum, round(sum(aa.hwm)/256,2) from space_values
> > a, dba_tables b
> > where bb.tablespace_name = 'tablespacename'
> > and bb.table_name = a.table_name
> > and bb.owner = aa.owner
> > group by datum
> >
> > but this takes AGES !! And i don't know why since there are only 800
> > rows in space_values.
> > when i remove the group by and the datum in the statement, the query
> > is pretty fast.
> > The idea was to get the amounts of space for each day and each
> > tablespace with one query, but it
> > takes ages even when i query
> > only one tablespace, as you see above. What am I doing wrong?
> >
> > Any help would be greatly appreciated!
> >
> > Frank Zaum, Salzgitter
>
> Please define 'ages'
> Do you run Oracle on any hardware? Does it have a configuration?
> Does the hardware have an operating system?
> Does the Oracle software have a version number?
> Do indexes exist on your table (not that that would necessarily be a good
> thing with 800 rows)
> How do other things run in this system?
>
> Daniel Morgan

Oracle 8.1.7.3 on SP2 OPS

Ages means more than 7 hours. indexes are only on (owner, datum and table_name)
i dont think that the hardware is part of the problem, as i pointed out the same query without group by is rather fast when a fixed date is given. there must be something wrong with the statement.

tia, Frank Zaum Received on Fri Apr 26 2002 - 01:57:02 CDT