Re: Why takes this select AGES (and hogs CPU like ...)

Explain plan for the queries ?
"Frank Zaum" <frankzaum_at_yahoo.de> wrote in message news: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
> 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
> aa, dba_tables bb
> 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 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 Mon Apr 29 2002 - 12:27:01 CEST