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

What is the actual value that you use for "datum" and what is your column type of "datum"? Post your table structure (desc space_tables) and explain plan, so we might be able to help.

Also you have "bb.table_name = a.table_name", should it be "bb.table_name = aa.table_name"?

Guang

"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:<Vd9z8.10783$e5.69410_at_news.indigo.ie>...
> 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 - 22:17:47 CEST