RE: Partition Statistics

Hi Steve

>When I query dba_ind_partitions all the stats are there but the value =
in
>the global_stats column was 'NO' so I did this:
>EXEC =3D
>dbms_stats.gather_index_stats(ownname=3D3D>'oname',indname=3D3D>'iname',=
 -
>partname=3D3D>'P20041207',granularity=3D3D>'PARTITION');
>After the above the value in the global_stats column is 'YES' for the
>specific partition recalc'd so now my question is what changed?=20

Only DBMS_STATS can gather global statistics! For this reason if you = gather them with CREATE INDEX or ANALYZE the flag is always NO.

>Does the
>optimizer look at the dba_ind_partitions.global_stats column and does =
it
>make a difference?=3D20

I have never seen the optimizer to change an execution plan when the = flag changes. If you get different execution plans it's simply because = the statistics are different. In fact global stats are sometimes very = different from non-global stats.=20

>On another experiment I tried this:
>EXEC =3D
>dbms_stats.gather_index_stats(ownname=3D3D>'oname',indname=3D3D>'iname',=
 -
>partname=3D3D>'P20041206',granularity=3D3D>'DEFAULT');
>
>With this granularity the system was churning away a long time so I
>interrupted it. What was it doing? Was it gathering "global" status by
>looking at all 700+ partitions even though I specified a single
>partition? (There is just the one index on the table and it will be
>exceeding 100,000,000 rows sometime next week.)

It was gathering the statistics at table level and the statistics at = partition level only for the partition you specified (PARTITION means = that the statistics at TABLE and partition level are gathered). If you = want to gather statistics at partition level you must use the PARTITION = or SUBPARTITION option.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l