Re: Calling all the SQL Guru's

You're right - it depends what the purpose of the count is and then on how quickly the stats become stale. It's just that the information required may already be in the database.

Cheers
Simon Irvin

<postbus_at_sybrandb.demon.nl> wrote in message news:tahif7c82b6q80_at_beta-news.demon.nl...
> Do you think that is dependable? If the statistics are stale this could
 end
> up in utterly incorrect results.
> IMO, if you really want to use a count, don't rely on the statistics.
>
> Regards,
>
> Sybrand Bakker, Oracle DBA
>
> "Simon Irvin" <sirvin_at_synomics.com> wrote in message
> news:3aa8a87b_1_at_nnrp1.news.uk.psi.net...
> > How about using the row count generated generated by the analyze
 command?
> >
> > "John Doe" <jhon_at_doe.com> wrote in message
> > news:98a5in$uke$1_at_news.btv.ibm.com...
> > > Try this it's a bit more simple and it does the same
> > >
> > > select 'select count(*) from '||owner||'.'|| table_name||';' from
> > > dba_all_tables;
> > >
> > > Regards
> > > John D
> > >
> > > "Martin Haltmayer" <Martin_Haltmayer_at_d2mail.de> wrote in message
> > > news:3AA7C4A5.A3D4614E_at_d2mail.de...
> > > > Try this:
> > > >
> > > > create or replace
> > > > function my_count (i_tabname in user_tables.table_name%type) return
 number
 is
> > > > type t is ref cursor;
> > > > r t;
> > > > l_retval number;
> > > > begin
> > > > open r for 'select count (*) from "' || i_tabname || '"';
> > > > fetch r into l_retval;
> > > > close r;
> > > > return l_retval;
> > > > exception
> > > > when others then
> > > > if r%isopen then
> > > > close r;
> > > > end if; -- r%isopen then
> > > > raise;
> > > > end my_count;
> > > > /
> > > > show errors
> > > >
> > > > select table_name, my_count (table_name)
> > > > from user_tables
> > > > order by 1;
> > > >
> > > > I tested it on NT 4.0, Oracle 8.1.6.3.1
> > > >
> > > > Martin
> > > >
> > > >
> > > >
> > > > Mick Rice wrote:
> > > > >
> > > > > I was trying to devise a single piece of sql to generate a list of
 all
> > > > > the tables in the database and a count of the number of rows on
 each
> > > > > table. I started out confident that I could code this easily using
 a
> > > > > subquery and 'group by' statement. However an hour later, and
> > > > > confidence diminished, I havn't been able to get the syntax right.
 The
> > > > > best I could come up with was the pretty lame looking query
 following
> > > > > which wasn't what I was aiming for at all.
> > > > >
> > > > > set pagesize 0;
> > > > > spool count.sql;
> > > > > select 'select count(*) from '||owner||'.'|| table_name||';' from
> > > > > all_tables;
> > > > > spool off;
> > > > >
> > > > > Can anyone come up with a single statement which does this ?
> > > > >
> > > > > It's put me out of my misery,
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Mick.
> > > > >
> > > > > --
> > > > > Back it up !
> > > > >
> > > > > Sent via Deja.com
> > > > > http://www.deja.com/
> > > >
> > >
> > >
> >
> >
>
>
Received on Fri Mar 09 2001 - 09:13:44 CST