Início / dba / Perguntas / 242063
Accepted
Wernfried Domscheit
Asked: 2019-07-05 01:30:13 +0800 CST

Tabela não analisada após a divisão da partição

  • 772

Na minha mesa eu divido uma subpartição com sucesso. Depois disso, reconstruo os índices e coleto estatísticas.

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');

Agora, quando eu verifico as estatísticas, aparentemente a tabela não é analisada, ou seja, as estatísticas da tabela não estão presentes:

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|
+---------------------------------------------------------------+

Alguma ideia do que estou fazendo de errado?

Corrida

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');

não ajuda.

oracle partitioning

1 respostas

  1. Best Answer

    Use a opção adequada GRANULARITY.

    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

    Teste1:

    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.

    Teste2:

    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>
    • 1

relate perguntas