Incremental Statistics Gathering Not Working At Hash Sub-Partition Level (Doc ID 1390718.1)

Last updated on JANUARY 18, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms


  • Incremental Statistics gathering not working on hash sub-partition tables.
  • Gather stats using dbms_stats.gather_table_stats / dbms_stats.gather_database_stats with granularity set to AUTO do not collect statistics on hash sub-partition tables.

As the example shows below, no statistics have been collected for the hash sub-partitions:
CREATE TABLE SH.SALES_SUB_PART
( PROD_ID NUMBER NOT NULL ENABLE,
CUST_ID NUMBER NOT NULL ENABLE,
TIME_ID DATE NOT NULL ENABLE,
CHANNEL_ID NUMBER NOT NULL ENABLE,
PROMO_ID NUMBER NOT NULL ENABLE,
QUANTITY_SOLD NUMBER(10,2) NOT NULL ENABLE,
AMOUNT_SOLD NUMBER(10,2) NOT NULL ENABLE)
PARTITION BY RANGE (TIME_ID) SUBPARTITION BY hash (CHANNEL_ID)
SUBPARTITIONS 4
(
PARTITION SALES_Q3_2001 VALUES LESS THAN (TO_DATE(' 2001-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION SALES_Q4_2001 VALUES LESS THAN (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
) ;

insert /*+APPEND*/ into SALES_SUB_PART select * from sales where time_id >=
TO_DATE(' 2001-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') and time_id < TO_DATE(' 2001-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN');
commit;

-- collect statistics with granularity => 'AUTO'
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SH', tabname => 'SALES_SUB_PART', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, granularity => 'AUTO');

select table_name, to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI') as "Last Analyzed" from dba_tables where table_name='SALES_SUB_PART';

select table_name, partition_name, SUBPARTITION_NAME, to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI') as "Last Analyzed",num_rows from dba_tab_statistics where table_name='SALES_SUB_PART'
order by partition_position ;


SQL v110202> select table_name, to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI') as "La

TABLE_NAME           Last Analyzed
-------------------- ------------------------------
SALES_SUB_PART       01/09/2012 11:19

SQL v110202>
SQL v110202> select table_name, partition_name, SUBPARTITION_NAME, to_char(LAST_AN
  2  order by partition_position ;
TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME    Last Analyzed                    NUM_ROWS
-------------------- -------------------- -------------------- ------------------------------ ----------
SALES_SUB_PART       SALES_Q3_2001        SYS_SUBP10727
SALES_SUB_PART       SALES_Q3_2001        SYS_SUBP10728
SALES_SUB_PART       SALES_Q3_2001        SYS_SUBP10726
SALES_SUB_PART       SALES_Q3_2001        SYS_SUBP10725
SALES_SUB_PART       SALES_Q3_2001                             01/09/2012 11:19                    65769
SALES_SUB_PART       SALES_Q4_2001        SYS_SUBP10732
SALES_SUB_PART       SALES_Q4_2001        SYS_SUBP10730
SALES_SUB_PART       SALES_Q4_2001        SYS_SUBP10729
SALES_SUB_PART       SALES_Q4_2001                             01/09/2012 11:19                        0
SALES_SUB_PART       SALES_Q4_2001        SYS_SUBP10731
SALES_SUB_PART                                                 01/09/2012 11:19                    65769


11 rows selected.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms