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

Elegant is nice. But getting the job done has its own elegance. Here's what you could do.

Open a cursor that selects table_name from user_tables. Then using native dynamic SQL select count(*) into a variable. Then evaluate the value of the variable against the 20K threshold.

You could, of course, set up DBMS_JOB to run schema_analyze at night but I don't know how accurate you need to be or how timely.

Daniel A. Morgan

> 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 Wed Mar 28 2001 - 01:06:28 CST