Re: Check out the system tables fellas

Caveat: This might be a bit dated, but the NUM_ROWS column used to be set only by ANALYZE, so it was only accurate as of the last time you ran ANALYZE on the table.

If you use:

    SELECT COUNT(primary_key_column) FROM table Oracle will use only the index, which is faster than reading the whole table. I think this may work for with any index, or at least any UNIQUE one. You can see the effect of this in SQL*Plus with the SET AUTOTRACE ON command.
(This may also be what happens when you SELECT COUNT(1) ...)

--Barry

"Venkat Iyer" <venkat_p_iyer_at_yahoo.com> wrote in message news:3aaa1c92.0_at_news.tm.net.my...
> guys....guys...
>
> there is a system table called ALL_TABLES.
> in this table there is a column called NUM_ROWS
> in the same table there is also a column called OWNER...
>
> catch my drift ??????
>
> also a point referring to john doe's soln....never say
> count(*)....instead say count(1) or count('X')..it gives the same output
 but
> works much much faster...
>
> ciao
> venkat
>
>
> "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 Tue Mar 13 2001 - 23:21:20 CST