1

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.

1

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> exec dbms_stats.gather_table_stats(user, 'QUARTERLY_REGIONAL_SALES');

PL/SQL procedure successfully completed.

SQL>


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> exec DBMS_STATS.SET_PARAM ('GRANULARITY', 'GLOBAL');

PL/SQL procedure successfully completed.

SQL> 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> 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> exec DBMS_STATS.SET_PARAM ('GRANULARITY', 'SUBPARTITION');

PL/SQL procedure successfully completed.

SQL> 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> 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>
  • Yes it worked. Current value for GRANULARITY is AUTO, I wonder why Oracle does not select it properly. Documentation just says Determines the granularity based on the partitioning type. – Wernfried Domscheit Jul 4 at 11:15
  • @WernfriedDomscheit One more thing that I have just found: DBMS_STATS Does Not Collect Statistics For Hash Sub-partitions When Default Granularity Is Used (Doc ID 2238660.1) – Balazs Papp Jul 4 at 11:49
  • My table uses List-Range Partitioning, so the bug should not apply. – Wernfried Domscheit Jul 4 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.