dbms_stats.gather_table_stats only calculating stats for first partition of range interval - list composite partitioned table

  • From: Peter Hitchman <pjhoraclel@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 10 Dec 2009 14:20:27 +0000

Oracle 11.1.0.6 EE
OEL V4 64 bit.

All,
I am stumped by what has happened to the statistics for a table that I have
that is range interval - list partitioned.

I gather the stats like this:
DBMS_STATS.GATHER_TABLE_STATS(
         OWNNAME=>'&owner_name'
        ,TABNAME=>'TSPS_TRACKER'
        ,PARTNAME=>'&PARTITION_NAME'
        ,ESTIMATE_PERCENT=> NULL
        ,BLOCK_SAMPLE => FALSE
        ,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'
        -- ,DEGREE => DBMS_STATS.AUTO_DEGREE
        ,DEGREE => NULL
        ,GRANULARITY=>'AUTO'
        ,CASCADE=>TRUE
        ,FORCE=>TRUE);

but only the first partition (the one you have to declare) has stats
gathered for it at the partition level, even though they are gathered for
all of the subpartitions for all partitions.
Global stats are also created.

The table DDL:

CREATE TABLE TSPS_TRACKER
( PATENT_SK NUMBER(9) NOT NULL
 ,KEY NUMBER(6) NOT NULL
 ,ARRIVED TIMESTAMP NOT NULL
)
PARTITION BY RANGE (ARRIVED) INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
SUBPARTITION BY LIST (KEY)
SUBPARTITION TEMPLATE
(SUBPARTITION DEL  VALUES (5,10) TABLESPACE CSGDATA
 ,SUBPARTITION NEW  VALUES (100,300,400,1000,1100,1200,1300,1390,1400)
TABLESPACE CSGDATA
 ,SUBPARTITION SVA  VALUES (1800,1900,2000,2100,2200,2300,2500) TABLESPACE
CSGDATA
 ,SUBPARTITION EVA  VALUES
(2900,3000,3100,3145,3150,3300,3400,3500,3600,3700,3800) TABLESPACE CSGDATA
 ,SUBPARTITION PUB  VALUES (3900,4000,4100,4200,7000) TABLESPACE CSGDATA
 ,SUBPARTITION REIR VALUES (5000,5005,5010,5015,5020,5025,5040,5050)
TABLESPACE CSGDATA
 ,SUBPARTITION REIS VALUES (5100,5200,5400,5500,5900,6000,6100) TABLESPACE
CSGDATA
 ,SUBPARTITION MISC VALUES (DEFAULT) TABLESPACE CSGDATA
)
( PARTITION TT_01 VALUES LESS THAN ('01-APR-2008:00:00:00')
)
ENABLE ROW MOVEMENT
/

it also has two local indexes. The same thing happens for the indexes, only
the first partition has partition stats collected.

This makes SQL that is only going to use 1 partition choose very inefficient
plans (when the partition in question is not the first one of course).

Anyone know of a reason why this should be so?

Thanks

Pete

Other related posts: