Re: Check out the system tables fellas

num_rows is indeed set by analyze. This has 2 implications

1. It is guaranteed to be out of date by at least a bit - hence Sybrands comment about not relying on it.
2. It is of no use whatsoever in a rule based environment. (they do still exist)

Finally

It may well have been true once upon a time that count(1) was faster than count(*), but I have not been able to reproduce this on any Oracle system since 8.0.5 (and only once in perhaps special conditions on a 7.3.4 instance).

regards

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Barry Schader" <barryaz1_at_home.com> wrote in message
news:kdDr6.25799$Xt3.3970141_at_news1.rdc1.az.home.com...


> 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/

> > > > >

> > > >

> > > >

> > >

> > >

> >

> >

>

>