Re: Help! This group by stmt takes AGES

I'm not sure what the problem would be...

Have you tried to do a simple count(*) from the same query, without the group by? This would at least give you an idea of how many rows you are looking at... You can then work it from there. I have a feeling that there's a join that's not being performed and you're ending up with a cartesian product.

JP
frankzaum_at_yahoo.de (Frank Zaum) wrote in message news:<4c1e5bbe.0204252257.e7e8f1e_at_posting.google.com>...
> 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 - 06:51:52 CDT