I'm open to abuse if I'm wrong, but I think that the intended distinction was:
let's assume table T with partitions P1 and P2
analyze table t compute statistics ; <--- global statistics
analyze table t partition (p1) compute statistics ; <--- local statistics on partition p1
analyze table t partition (p2) compute statistics ; <--- local statistics on partition p2
> -----Original Message-----
> From: Odland, Brad [mailto:[EMAIL PROTECTED]]
>
> Please exlain the difference between local and global statistics.
> Is this statisitics on a partition?
>
> -----Original Message-----
>
> Brad, you're absolutely correct. Explain plan is what is
> intended, but
> isn't what always happens. It probably is most of the time,
> but not always.
> I recently had a circumstance in which a long running job
> (the explain plan
> of) was telling me it was reading from a big partitioned
> table and using the
> index that it should have been using, but a 10046 trace
> showed reads from a
> different index and no reads from the index identified by
> explain plan. A
> full analyze compute (using dbms_stats) later, this job works fine.
> I suspect in my case the problem was with statistics. The job was
> originally set up by the developer to add data each night,
> then analyze the
> current partition. The last time global stats were gathered
> on this table
> was last October. I suspect that over time with data loads
> and updates the
> data distribution, etc. got skewed as compared to the global
> stats, so the
> optimizer didn't have enough valid information to make a good choice.
> This query was reading data from 5 or 6 partitions of a 54
> partition table.
> That's important information because a couple of weeks ago I
> was reading the
> "Oracle 8i Designing and Tuning for Performance" document and
> came across
> this statement:
> "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."
> Pretty interesting to think about. It's gather stats global
> and local from
> now on for me.
