Re: determine num rows in a table without doing a count(*)

From: Rocr <rolland.cright_at_pwgsc.gc.ca>
Date: Tue, 27 Mar 2001 13:54:13 -0500
Message-ID: <99qnke$s6u3@shark.ncr.pwgsc.gc.ca>

I could do a count but I am writing a script that will identify tables that have large amounts of records and candidates for re-orgs. However I want to set a threshold (20,000 records).

I am trying to write an elegant query that will do this for me

  select a.table_name
   from dba_tables a
    where 20000 <= (select num_rows from some table where table_name = a.table_name)

I was wishing...

Thanks anyhow. ( For some reason I cannot get the powers that be to run stats, go figure).
"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3AC0A9FE.FE8027E5_at_exesolutions.com...
> > Is there a data dictionary table that holds a record of the number of
rows
> > in a table.
> >
> > I need to determine the number of rows without doing a count(*).
>
> The number of rows in a table is accessible through the field num_rows in
the
> views DBA_TABLES, ALL_TABLES, and USER_TABLES.
>
> But you must run analyze on the table or schema before the values are
populated.
>
> But I am fascinated ... why can't you run SELECT COUNT(*)?
>
> Daniel A. Morgan
>
Received on Tue Mar 27 2001 - 12:54:13 CST