Re: WHATS WRONG WITH DBA_TABLES?

marks_at_sensible.teleord.co.uk (Mark Styles) wrote:

>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.

Charles,

You need to do the same thing with indexes (ANALYZE INDEX ...) or else the cost based optimizer will not work at top efficiency.

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!