Re: Oracle10g, Need to find number of rows in each table in a schema

On Fri, 03 Nov 2006 16:04:59 -0800, RA wrote:

>>
>> SELECT table_name, num_rows
>> FROM all_tables
>> WHERE owner = <some_value>;
>>
>> won't work for what reason?

except for getting info about chaining, using analyze is deprecated. So even this is not quite right. Step 1 should be more along running the dbms_stats gathering routines.

> 2) SELECT table_name, num_rows FROM all_tables WHERE owner =
> <some_value>
>
>
> But my question is why even bother to analyze.
>

If the num_rows is incorrect, the cost based optimizer will be using the wrong statistics. If that has happened, either the DBA has made a conscious choice to use manufactured or stale statistics or the CBO is about to bark up the wrong tree.

If the number of rows varies enough that the statistics are wrong, it's probably time to gather new stats. So the stats should be pretty close just to keep the system operating reasonably well - taking into account the above exception.

Is /accuracy/ on the count of /all/ tables REALLY that important? I'd be interested in understanding the business case that requires that level of accuracy.

The exceptions I can see are related to theoretical analysis at a level far deeper than I'd expect from someone using deprecated commands. (No insult intended, just an observation.)

Until then, I have to admit it sounds a lot like a make-work project.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.