Re: Help! This group by stmt takes AGES

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 Received on Thu Apr 25 2002 - 17:29:09 CDT