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

My best guess is that 'the powers that be' know or believe that the application runs best with the optimiser mode set to RULE. I have to doubt this , at least in currently supported versions of oracle, however..

In addition of course you may be able to run analyse_schema at night, do your selects and then run analyse_schema again with the delete stats option. This is getting very silly by this stage.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message
news:3AC18D74.B2EB3D5C_at_exesolutions.com...


> 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

> > >

>