Why takes this select AGES (and hogs CPU like ...)
Date: 25 Apr 2002 12:18:22 -0700
Message-ID: <4c1e5bbe.0204251118.42b821eb_at_posting.google.com>
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
select distinct aa.datum, round(sum(aa.hwm)/256,2) from space_values
aa, dba_tables bb
but this takes AGES !! And i don't know why since there are only 800
rows in space_values.
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
where bb.tablespace_name = 'tablespacename'
and bb.table_name = a.table_name
and bb.owner = aa.owner
group by datum
when i remove the group by and the datum in the statement, the query
is pretty fast.
takes all day 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 Received on Thu Apr 25 2002 - 21:18:22 CEST