Table not analyzed after partition has been split On my table I split a subpartition successfully. After that I rebuild the indexes and gather statistics.

ALTER TABLE T_MO_DPI SPLIT SUBPARTITION ACTIVE_RELATIONS VALUES (24) INTO (
   SUBPARTITION ACTIVE_RELATIONS_2G, 
   SUBPARTITION ACTIVE_RELATIONS);

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_MO_DPI');


Now, when I check the statistics then apparently table is not analyzed, i.e. table statistics are not present:

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'T_MO_DPI';

+---------------------------------------------------------------+
|TABLE_NAME|SUBPARTITION_NAME      |NUM_ROWS|LAST_ANALYZED      |
+---------------------------------------------------------------+
|T_MO_DPI  |ACTIVE_ELEMENTS        |9146    |18.10.2018 22:18:51|
|T_MO_DPI  |ACTIVE_CELLS           |39812   |13.06.2019 22:32:33|
|T_MO_DPI  |ACTIVE_RELATIONS_2G    |        |                   |
|T_MO_DPI  |ACTIVE_RELATIONS       |        |                   |
|T_MO_DPI  |DELETED_ELEMENTS       |1160    |19.11.2018 22:18:14|
|T_MO_DPI  |DELETED_CELLS          |14102   |10.06.2019 22:17:53|
|T_MO_DPI  |DELETED_RELATIONS      |14865562|11.03.2019 23:10:10|
+---------------------------------------------------------------+


Any idea what I am doing wrong?

Running

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_MO_DPI', 'ACTIVE_RELATIONS_2G');
exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_MO_DPI', 'ACTIVE_RELATIONS');


does not help.
Use the proper GRANULARITY option.

CREATE TABLE quarterly_regional_sales
      (deptno number, item_no varchar2(20),
       txn_date date, txn_amount number, state varchar2(2))
  PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
         )
      );

SQL&gt; exec dbms_stats.gather_table_stats(user, 'QUARTERLY_REGIONAL_SALES');

PL/SQL procedure successfully completed.

SQL&gt;


SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'QUARTERLY_REGIONAL_SALES';

TABLE_NAME                     SUBPARTITION_NAME                NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHWEST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHCENTRAL                    0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHCENTRAL                    0 2019-07-04 11:46:40


Test1:

SQL&gt; exec DBMS_STATS.SET_PARAM ('GRANULARITY', 'GLOBAL');

PL/SQL procedure successfully completed.

SQL&gt; alter table QUARTERLY_REGIONAL_SALES split subpartition q1_1999_northwest into (subpartition q1_1999_northwest_1 values('OR'), subpartition q1_1999_northwest_2);

Table altered.

SQL&gt; exec dbms_stats.gather_table_stats(user, 'QUARTERLY_REGIONAL_SALES');

PL/SQL procedure successfully completed.

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS
  3  WHERE TABLE_NAME = 'QUARTERLY_REGIONAL_SALES';

TABLE_NAME                     SUBPARTITION_NAME                NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_1
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_2
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHWEST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHCENTRAL                    0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHCENTRAL                    0 2019-07-04 11:46:40

7 rows selected.


Test2:

SQL&gt; exec DBMS_STATS.SET_PARAM ('GRANULARITY', 'SUBPARTITION');

PL/SQL procedure successfully completed.

SQL&gt; alter table QUARTERLY_REGIONAL_SALES split subpartition Q1_1999_SOUTHEAST into (subpartition Q1_1999_SOUTHEAST_1 values ('FL'), subpartition Q1_1999_SOUTHEAST_2);

Table altered.

SQL&gt; exec dbms_stats.gather_table_stats(user, 'QUARTERLY_REGIONAL_SALES');

PL/SQL procedure successfully completed.

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS
  3  WHERE TABLE_NAME = 'QUARTERLY_REGIONAL_SALES';

TABLE_NAME                     SUBPARTITION_NAME                NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_1                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_2                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST_1                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST_2                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHWEST                       0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHEAST                       0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHCENTRAL                    0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHCENTRAL                    0 2019-07-04 11:52:31

8 rows selected.

SQL&gt;