Re: WHATS WRONG WITH DBA_TABLES?

From: Mark Styles <marks_at_sensible.teleord.co.uk>
Date: 1996/04/16
Message-ID: <4l0ff7$bl1_at_sensible.teleord.co.uk>#1/1


Charles Walker <cwalker_at_explorer.csc.com> wrote:
>When I run the following I get no rows
>
>SQL> SELECT NUM_ROWS FROM SYS.DBA_TABLES WHERE TABLE_NAME ='NEW_MARINES';
>
> NUM_ROWS
>----------
>
>But when I do a count I get rows...whats up?
>
>SQL> SELECT COUNT(*) FROM TFDSS.NEW_MARINES;
>
> COUNT(*)
>----------
> 569438
>

The statistical fields on tables such as dba_tables are not kept up to date automatically, you have to ANALYZE the table regularly. When you ANALYZE a table, you can either COMPUTE or ESTIMATE these statistics, as COMPUTE can take a long time on large tables.

If you can, its nice to analyze all your tables every night, or maybe every weekend (the statistics generated get used by the cost based optimiser to work out the fastest query paths).

How often you analyze depends on how often your table stats are likely to change, and how up to date you need to keep the data.

-- 
** Mark Styles aka Small       -- Opinions expressed here are my own --   **
** marks_at_teleord.co.uk         -- unless otherwise specified         --   **
**           This whole world's wild at heart and weird on top            **
Received on Tue Apr 16 1996 - 00:00:00 CEST

Original text of this message