Re: number of rows in a table

A nice feature of 8.1 though, if you aren't worried too much about exact answers is that a table can be MONITORed

    alter table XXX monitoring.

Then Oracle keeps a count of inserts updates and deletes so that it can determine the staleness of the stats.

This gets written back to tab$ (user_tables) every 3 hours by SMON, and even if it hasn't you can take

        NUM_ROWS in user_tablse
    + inserts from user_tab_modifications

• deletes from user_tab_modifications

I haven't checked how accurate it is, and if the values can get lost (e.g. by restart, flush shared pool), although I think an abort is bound to lose them.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Kenneth C Stahl wrote in message <37F34BD2.48BA6805_at_Unforgettable.com>...
>The ONLY way that you can determine the number of rows in a table without
>opening a cursor and sweeping through each row is with the count(*)
>function.
>
>The num_rows column in user_tables, all_tables, dba_tables, etc. is only
>populated when statistics are run and even then it is only accurate as of
>that moment since the count is not updated dynamically.
>
Received on Thu Sep 30 1999 - 06:52:12 CDT