If I remember right, global stats are computed/inferred from partition level stats if its available. If thats the case, then it may make sense to compute/estimate the stats at the partition level. You also have the advantage of being able to run analyze each partition in parallel.
Regards, Denny Quoting "Koivu, Lisa" <[EMAIL PROTECTED]>: > Hi everyone, > > Back to the lovely world of Oracle :) I've been reading up on > statistics. > Out of the 8.1.7 doco: > /* > Partitioned schema objects may contain multiple sets of statistics. They > can > have statistics which refer to the entire schema object as a whole > (global > statistics), they can have statistics which refer to an individual > partition, and they can have statistics which refer to an individual > subpartition of a composite partitioned object. > > Unless the query predicate narrows the query to a single partition, > the > optimizer uses the global statistics. Because most queries are not > likely to > be this restrictive, it is most important to have accurate global > statistics. Intuitively, it may seem that generating global statistics > from > partition-level statistics should be straightforward; however, this is > only > true for some of the statistics. For example, it is very difficult to > figure > out the number of distinct values for a column from the number of > distinct > values found in each partition because of the possible overlap in > values. > Therefore, actually gathering global statistics with the DBMS_STATS > package > is highly recommended, rather than calculating them with the ANALYZE > statement > > */ > The table I need to generate stats for is currently 32GB and grows by > ~2GB > per week. Even the smallest estimate with calculating global stats > will > take a long long time and I may not be able to spring for all the > required > temp space. > > How does the list feel about global stats? Does anyone agree with the > documentation that they "most important"? I'm thinking my partitioned > statistics are the "most important". > > Any input is appreciated. Thanks > > Lisa Koivu > Oracle Database Administrator > Fairfield Resorts, Inc. > 5259 Coconut Creek Parkway > Ft. Lauderdale, FL, USA 33063 > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
