This discussion is archived
10 Replies Latest reply: Feb 10, 2010 11:32 PM by Ric79 RSS

PARTITIONS AND SUBPARTITIONS

Ric79 Newbie
Currently Being Moderated
Hi all,
here there is my problem with this table:
CREATE TABLE CKEPM.CKCLASS_ALL
(
  DATA_POPOLAMENTO  DATE,
  OBJ_CAT           CHAR(1 BYTE),
  NODE_ID           NUMBER,
  CA_V              VARCHAR2(256 BYTE),
  CB_V              VARCHAR2(256 BYTE),
  C1                NUMBER,
  C2                NUMBER
 )
TABLESPACE TBS_CKEPM_ALL
PARTITION BY RANGE (DATA_POPOLAMENTO)
SUBPARTITION BY LIST (OBJ_CAT)
(  
  PARTITION ALL_20100130204500RQ VALUES LESS THAN (TO_DATE(' 2010-01-30 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE TBS_CKEPM_ALL
  ( SUBPARTITION ALL_20100130204500RQL01 VALUES ('N')    TABLESPACE TBS_CKEPM_ALL,
    SUBPARTITION ALL_20100130204500RQL02 VALUES ('L')    TABLESPACE TBS_CKEPM_ALL ),  
  PARTITION ALL_20100130210000RQ VALUES LESS THAN (TO_DATE(' 2010-01-30 21:15:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE TBS_CKEPM_ALL
  ( SUBPARTITION ALL_20100130210000RQL01 VALUES ('N')    TABLESPACE TBS_CKEPM_ALL,
    SUBPARTITION ALL_20100130210000RQL02 VALUES ('L')    TABLESPACE TBS_CKEPM_ALL )
);

CREATE BITMAP INDEX CKEPM.IX_CKCLASSALL_NODE ON CKEPM.CKCLASS_ALL
(NODE_ID)
  TABLESPACE TBS_CKEPM_ALLX
LOCAL (  
  PARTITION ALL_20100130204500RQ
    TABLESPACE TBS_CKEPM_ALLX
  ( SUBPARTITION ALL_20100130204500RQL01    TABLESPACE TBS_CKEPM_ALLX,
    SUBPARTITION ALL_20100130204500RQL02    TABLESPACE TBS_CKEPM_ALLX ),  
  PARTITION ALL_20100130210000RQ
    TABLESPACE TBS_CKEPM_ALLX
  ( SUBPARTITION ALL_20100130210000RQL01    TABLESPACE TBS_CKEPM_ALLX,
    SUBPARTITION ALL_20100130210000RQL02    TABLESPACE TBS_CKEPM_ALLX )
);
I need to gather statistics for the partitions/subpartitions/index partitions of <b>ALL_20100130204500RQ</b> and then copy all those statistics on <b>ALL_20100130210000RQ</b>

1) Could someone post the DBMS_STATS.gather_table_stats and DBMS_STATS.copy_table_stats script?

I have tried
exec DBMS_STATS.gather_table_stats (ownname               => 'CKEPM',
tabname               => 'CKCLASS_ALL',
partname              => 'ALL_20100130204500RQ', 
estimate_percent      => DBMS_STATS.AUTO_SAMPLE_SIZE ,
CASCADE               => FALSE);

select partition_name , last_analyzed
from user_TAB_PARTITIONS 
where partition_name IN ('ALL_20100130204500RQ, 'ALL_20100130210000RQ);

PARTITION_NAME     LAST_ANALYZED
ALL_20100130204500RQ     09/02/2010 8.56.20
ALL_20100130210000RQ      

exec DBMS_STATS.copy_table_stats (ownname => 'CKEPM', 
tabname => 'CKCLASS_ALL', 
srcpartname => 'ALL_20100130204500RQ', 
dstpartname => 'ALL_20100130210000RQ');

ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 16496
ORA-06512: at line 1

select partition_name , last_analyzed
from user_TAB_PARTITIONS 
where partition_name IN ('ALL_20100130204500RQ, 'ALL_20100130210000RQ);

PARTITION_NAME     LAST_ANALYZED
ALL_20100130204500RQ     09/02/2010 8.56.20
ALL_20100130210000RQ     09/02/2010 8.56.20 
2) When I make unusable the index and the rebuild it, I need also to gather statistics (how?) ?

Thanks to all,
Riccardo

Edited by: user12581838 on Feb 8, 2010 11:58 PM

Edited by: user12581838 on Feb 9, 2010 12:48 AM
  • 1. Re: PARTITIONS AND SUBPARTITIONS
    730428 Guru
    Currently Being Moderated
    Cannot find it in documentation but seems like the copy_table_stats procedure is not able to perform a "deep" copy of statistics.
    When you try to copy statistics between partitions, and subpartitions exist, it copies partitions statistics but doesn't touch subpartition statistics (raising the "Subscript beyond count" exception). While all works fine when you call it at subpartition level:
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
    
    SQL> begin
      2  DBMS_STATS.gather_table_stats (ownname               => 'SCOTT',
      3  tabname               => 'CKCLASS_ALL',
      4  partname              => 'ALL_20100130204500RQ', 
      5  estimate_percent      => DBMS_STATS.AUTO_SAMPLE_SIZE ,
      6  CASCADE               => FALSE);
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select partition_name, 
      2  NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, 
      3  AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, GLOBAL_STATS 
      4  from user_tab_partitions;
    
    PARTITION_NAME                   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
    ------------------------------ ---------- ---------- ------------ ---------- ---------- -----------
    SAMPLE_SIZE LAST_ANAL BUFFER_ GLO
    ----------- --------- ------- ---
    ALL_20100130204500RQ                    0          0            0          0          0           0
                09-FEB-10 DEFAULT YES
    
    ALL_20100130210000RQ
                          DEFAULT NO
    
    
    SQL> select subpartition_name, 
      2  NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, 
      3  AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, GLOBAL_STATS 
      4  from user_tab_subpartitions;
    
    SUBPARTITION_NAME                NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
    ------------------------------ ---------- ---------- ------------ ---------- ---------- -----------
    SAMPLE_SIZE LAST_ANAL BUFFER_ GLO
    ----------- --------- ------- ---
    ALL_20100130204500RQL01                 0          0            0          0          0           0
                09-FEB-10 DEFAULT YES
    
    ALL_20100130204500RQL02                 0          0            0          0          0           0
                09-FEB-10 DEFAULT YES
    
    ALL_20100130210000RQL01
                          DEFAULT NO
    
    ALL_20100130210000RQL02
                          DEFAULT NO
    
    
    -- COPY AT SUBPARTITION LEVEL
    
    SQL> begin
      2  DBMS_STATS.copy_table_stats (ownname => 'SCOTT', 
      3  tabname => 'CKCLASS_ALL', 
      4  srcpartname => 'ALL_20100130204500RQL01', 
      5  dstpartname => 'ALL_20100130210000RQL01');
      6  end;
      7  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select partition_name, 
      2  NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, 
      3  AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, GLOBAL_STATS 
      4  from user_tab_partitions;
    
    PARTITION_NAME                   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
    ------------------------------ ---------- ---------- ------------ ---------- ---------- -----------
    SAMPLE_SIZE LAST_ANAL BUFFER_ GLO
    ----------- --------- ------- ---
    ALL_20100130204500RQ                    0          0            0          0          0           0
                09-FEB-10 DEFAULT YES
    
    ALL_20100130210000RQ
                          DEFAULT NO
    
    
    SQL> select subpartition_name, 
      2  NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, 
      3  AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, GLOBAL_STATS 
      4  from user_tab_subpartitions;
    
    SUBPARTITION_NAME                NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
    ------------------------------ ---------- ---------- ------------ ---------- ---------- -----------
    SAMPLE_SIZE LAST_ANAL BUFFER_ GLO
    ----------- --------- ------- ---
    ALL_20100130204500RQL01                 0          0            0          0          0           0
                09-FEB-10 DEFAULT YES
    
    ALL_20100130204500RQL02                 0          0            0          0          0           0
                09-FEB-10 DEFAULT YES
    
    ALL_20100130210000RQL01                 0          0            0          0          0           0
           2000 09-FEB-10 DEFAULT YES
    
    ALL_20100130210000RQL02
                          DEFAULT NO
    
    
    -- COPY AT PARTITION LEVEL
    
    SQL> begin
      2  DBMS_STATS.copy_table_stats (ownname => 'SCOTT', 
      3  tabname => 'CKCLASS_ALL', 
      4  srcpartname => 'ALL_20100130204500RQ', 
      5  dstpartname => 'ALL_20100130210000RQ');
      6  end;
      7  /
    begin
    *
    ERROR at line 1:
    ORA-06533: Subscript beyond count
    ORA-06512: at "SYS.DBMS_STATS", line 17130
    ORA-06512: at line 2
    
    -- SUBPARTITION STAY UNANALYZED
    
    SQL> select subpartition_name, 
      2  NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, 
      3  AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, GLOBAL_STATS 
      4  from user_tab_subpartitions;
    
    SUBPARTITION_NAME                NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
    ------------------------------ ---------- ---------- ------------ ---------- ---------- -----------
    SAMPLE_SIZE LAST_ANAL BUFFER_ GLO
    ----------- --------- ------- ---
    ALL_20100130204500RQL01                 0          0            0          0          0           0
                09-FEB-10 DEFAULT YES
    
    ALL_20100130204500RQL02                 0          0            0          0          0           0
                09-FEB-10 DEFAULT YES
    
    ALL_20100130210000RQL01                 0          0            0          0          0           0
           2000 09-FEB-10 DEFAULT YES
    
    ALL_20100130210000RQL02
                          DEFAULT NO
    
    -- PARTITION IS NOW ANALYZED...
    
    SQL> select partition_name, 
      2  NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, 
      3  AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, GLOBAL_STATS 
      4  from user_tab_partitions;
    
    PARTITION_NAME                   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
    ------------------------------ ---------- ---------- ------------ ---------- ---------- -----------
    SAMPLE_SIZE LAST_ANAL BUFFER_ GLO
    ----------- --------- ------- ---
    ALL_20100130204500RQ                    0          0            0          0          0           0
                09-FEB-10 DEFAULT YES
    
    ALL_20100130210000RQ                    0          0            0          0          0           0
           2000 09-FEB-10 DEFAULT YES
    Max
    [My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/02/07/aggiornare-una-tabella-con-listruzione-merge/]
  • 2. Re: PARTITIONS AND SUBPARTITIONS
    Ric79 Newbie
    Currently Being Moderated
    Yes, this works.

    One more question. My ckclass_all has 96 * 10 partitions and 960*2 subpartitions. I have seen that
    - the DBMS_STATS.gather_table_stats of one subpartitions needs 1 minute (2 minute for one partition, about 3 hours for one day of partitions)
    - the DBMS_STATS.copy_table_stats of one subpartitions needs about 40 seconds !!!

    With other partitioned-tables the copy_table_stats runs in less of 1 second.

    How can I run faster? Do I need to upgrade to 11.2 in order to avoid the copy_table_stat bug?

    Riccardo
  • 3. Re: PARTITIONS AND SUBPARTITIONS
    730428 Guru
    Currently Being Moderated
    You can estimate stats using the estimate_percent parameter of the DBMS_STATS.GATHER_TABLE_STATS procedure. Depending on how your data is variable you can use a small percentage of rows to estimate the statistics. As far as I know is the only way to speed up the GATHER_TABLE_STATS procedure.

    I could repeat the copy test on an Oracle 11.1 to see whether the "deep" copy is available or not... I'll let you know in the late evening (Italy's time)...


    Max
    [My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/02/07/aggiornare-una-tabella-con-listruzione-merge/]
  • 4. Re: PARTITIONS AND SUBPARTITIONS
    730428 Guru
    Currently Being Moderated
    Hi Riccardo,

    on 11.1.0.6 the copy_table_stats on partitions doesn't raise any error but doesn't copy subpartition statistics:
    SQL> begin
      2  DBMS_STATS.copy_table_stats (ownname => 'MAXR',
      3  tabname => 'CKCLASS_ALL',
      4  srcpartname => 'ALL_20100130204500RQ',
      5  dstpartname => 'ALL_20100130210000RQ');
      6  end;
      7  /
    
    Procedura PL/SQL completata correttamente.
    
    SQL> select subpartition_name,
      2  NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT,
      3  AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, GLOBAL_STATS
      4  from user_tab_subpartitions;
    
    SUBPARTITION_NAME                NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL BUFFER_ GLO
    ------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- --------- ------- ---
    ALL_20100130204500RQL02                 0          0            0          0          0           0             09-FEB-10 DEFAULT YES
    ALL_20100130204500RQL01                 0          0            0          0          0           0             09-FEB-10 DEFAULT YES
    ALL_20100130210000RQL02                                                                                                   DEFAULT NO
    ALL_20100130210000RQL01                                                                                                   DEFAULT NO
    
    SQL> select partition_name,
      2  NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT,
      3  AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, GLOBAL_STATS
      4  from user_tab_partitions;
    
    PARTITION_NAME                   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL BUFFER_ GLO
    ------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- --------- ------- ---
    ALL_20100130204500RQ                    0          0            0          0          0           0             09-FEB-10 DEFAULT YES
    ALL_20100130210000RQ                    0          0            0          0          0           0        2000 09-FEB-10 DEFAULT YES
    
    SQL>
    Max
    [My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/02/07/aggiornare-una-tabella-con-listruzione-merge/]
  • 5. Re: PARTITIONS AND SUBPARTITIONS
    Ric79 Newbie
    Currently Being Moderated
    Hi Massimo,
    I'm plannig to upgrade from 10.1.0.5 to 11.2.

    Could you make a test for me on your DB?

    1)
    Drop table/create table and index....
    
    exec DBMS_STATS.gather_table_stats (ownname               => MAXR',
    tabname               => 'CKCLASS_ALL',
    partname              => 'ALL_20100130204500RQL01', 
    granularity => 'SUBPARTITION',
    estimate_percent      => DBMS_STATS.AUTO_SAMPLE_SIZE ,
    CASCADE               => FALSE);
    
    exec DBMS_STATS.gather_table_stats (ownname               => MAXR',
    tabname               => 'CKCLASS_ALL',
    partname              => 'ALL_20100130204500RQL02', 
    granularity => 'SUBPARTITION',
    estimate_percent      => DBMS_STATS.AUTO_SAMPLE_SIZE ,
    CASCADE               => FALSE);
    
    exec    DBMS_STATS.copy_table_stats (ownname => 'MAXR',
        tabname => 'CKCLASS_ALL',
        srcpartname => 'ALL_20100130204500RQ',
        dstpartname => 'ALL_20100130210000RQ');
        end;   
    - Elapsed time for these operations?
    - Are there Partition Statistics for ALL_20100130204500RQ and ALL_20100130210000RQ ?
    - Are there Subartition Statistics for ALL_20100130204500RQL01/2 and ALL_20100130210000RQL01/2 ?

    2)
    Drop table/create table and index....
    
    exec DBMS_STATS.gather_table_stats (ownname               => MAXR',
    tabname               => 'CKCLASS_ALL',
    partname              => 'ALL_20100130204500RQL01', 
    granularity => 'SUBPARTITION',
    estimate_percent      => DBMS_STATS.AUTO_SAMPLE_SIZE ,
    CASCADE               => FALSE);
    
    exec DBMS_STATS.gather_table_stats (ownname               => MAXR',
    tabname               => 'CKCLASS_ALL',
    partname              => 'ALL_20100130204500RQL02', 
    granularity => 'SUBPARTITION',
    estimate_percent      => DBMS_STATS.AUTO_SAMPLE_SIZE ,
    CASCADE               => FALSE);
    
    exec    DBMS_STATS.copy_table_stats (ownname => 'MAXR',
        tabname => 'CKCLASS_ALL',
        srcpartname => 'ALL_20100130204500RQL01',
        dstpartname => 'ALL_20100130210000RQL01');
        end;   
    
    exec    DBMS_STATS.copy_table_stats (ownname => 'MAXR',
        tabname => 'CKCLASS_ALL',
        srcpartname => 'ALL_20100130204500RQL02',
        dstpartname => 'ALL_20100130210000RQL02');
        end;   
    Same questions...

    Thanks a lot, Massimo

    Riccardo
  • 6. Re: PARTITIONS AND SUBPARTITIONS
    730428 Guru
    Currently Being Moderated
    I'll make the test tonight, I don't have an 11g now.
    - Elapsed time for these operations?
    You should give me the number of records to insert in each partition, and the result is not so significative anyway.

    My version anyway is 11.1.0.6, not 11.2

    Max
    [My Italian Oracle blog| http://oracleitalia.wordpress.com/2010/02/07/aggiornare-una-tabella-con-listruzione-merge/]
  • 7. Re: PARTITIONS AND SUBPARTITIONS
    Ric79 Newbie
    Currently Being Moderated
    Sorry Massimo,
    the elapsed time is just for the copy statistics (in my version for copying the subpartition statistics it needs 40 seconds while for the partition statistics needs less then 1 second).

    However the records in each subpartitions are 150.000

    R11 is quite sufficient because the bug is solved from the R10.2.0.6

    Riccardo
  • 8. Re: PARTITIONS AND SUBPARTITIONS
    Ric79 Newbie
    Currently Being Moderated
    -
  • 9. Re: PARTITIONS AND SUBPARTITIONS
    730428 Guru
    Currently Being Moderated
    SQL> drop table CKCLASS_ALL;
    
    Tabella eliminata.
    
    SQL>
    SQL> CREATE TABLE CKCLASS_ALL
      2  (
      3    DATA_POPOLAMENTO  DATE,
      4    OBJ_CAT           CHAR(1 BYTE),
      5    NODE_ID           NUMBER,
      6    CA_V              VARCHAR2(256 BYTE),
      7    CB_V              VARCHAR2(256 BYTE),
      8    C1                NUMBER,
      9    C2                NUMBER
     10   )
     11  PARTITION BY RANGE (DATA_POPOLAMENTO)
     12  SUBPARTITION BY LIST (OBJ_CAT)
     13  (
     14    PARTITION ALL_20100130204500RQ VALUES LESS THAN (TO_DATE(' 2010-01-30 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     15   ( SUBPARTITION ALL_20100130204500RQL01 VALUES ('N')   ,
     16      SUBPARTITION ALL_20100130204500RQL02 VALUES ('L')  ),
     17    PARTITION ALL_20100130210000RQ VALUES LESS THAN (TO_DATE(' 2010-01-30 21:15:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     18    ( SUBPARTITION ALL_20100130210000RQL01 VALUES ('N')  ,
     19      SUBPARTITION ALL_20100130210000RQL02 VALUES ('L')  )
     20  );
    
    Tabella creata.
    
    SQL> insert into CKCLASS_ALL
      2  select TO_DATE(' 2010-01-30 20:50:00', 'SYYYY-MM-DD HH24:MI:SS'),'N',0,'X','Y',0,0
      3  from dual
      4  connect by level <= 150000;
    
    Create 150000 righe.
    
    SQL>
    SQL> insert into CKCLASS_ALL
      2  select TO_DATE(' 2010-01-30 20:50:00', 'SYYYY-MM-DD HH24:MI:SS'),'L',0,'X','Y',0,0
      3  from dual
      4  connect by level <= 150000;
    
    Create 150000 righe.
    
    SQL>
    SQL> insert into CKCLASS_ALL
      2  select TO_DATE(' 2010-01-30 21:10:00', 'SYYYY-MM-DD HH24:MI:SS'),'N',0,'X','Y',0,0
      3  from dual
      4  connect by level <= 150000;
    
    Create 150000 righe.
    
    SQL>
    SQL> insert into CKCLASS_ALL
      2  select TO_DATE(' 2010-01-30 21:10:00', 'SYYYY-MM-DD HH24:MI:SS'),'L',0,'X','Y',0,0
      3  from dual
      4  connect by level <= 150000;
    
    Create 150000 righe.
    
    
    SQL> CREATE BITMAP INDEX IX_CKCLASSALL_NODE ON CKCLASS_ALL
      2  (NODE_ID)
      3  LOCAL (
      4    PARTITION ALL_20100130204500RQ
      5    ( SUBPARTITION ALL_20100130204500RQL01  ,
      6      SUBPARTITION ALL_20100130204500RQL02  ),
      7    PARTITION ALL_20100130210000RQ
      8    ( SUBPARTITION ALL_20100130210000RQL01  ,
      9      SUBPARTITION ALL_20100130210000RQL02  )
     10  );
    
    Indice creato.
    
    SQL> set timing on
    
    
    SQL> begin
      2  DBMS_STATS.gather_table_stats (ownname  => 'MAXR',
      3  tabname               => 'CKCLASS_ALL',
      4  partname              => 'ALL_20100130204500RQL01',
      5  granularity => 'SUBPARTITION',
      6  estimate_percent      => DBMS_STATS.AUTO_SAMPLE_SIZE ,
      7  CASCADE               => FALSE);
      8  end;
      9  /
    
    Procedura PL/SQL completata correttamente.
    
    Passati: 00:00:04.57
    SQL> begin
      2  DBMS_STATS.gather_table_stats (ownname               => 'MAXR',
      3  tabname               => 'CKCLASS_ALL',
      4  partname              => 'ALL_20100130204500RQL02',
      5  granularity => 'SUBPARTITION',
      6  estimate_percent      => DBMS_STATS.AUTO_SAMPLE_SIZE ,
      7  CASCADE               => FALSE);
      8  end;
      9  /
    
    Procedura PL/SQL completata correttamente.
    
    Passati: 00:00:00.29
    SQL>
    SQL> begin
      2  DBMS_STATS.copy_table_stats (ownname => 'MAXR',
      3      tabname => 'CKCLASS_ALL',
      4      srcpartname => 'ALL_20100130204500RQ',
      5      dstpartname => 'ALL_20100130210000RQ');
      6  end;
      7  /
    
    Procedura PL/SQL completata correttamente.
    
    Passati: 00:00:00.65
    
    SQL> select PARTITION_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED
      2  from dba_tab_partitions
      3  where table_owner='MAXR';
    
    PARTITION_NAME                   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
    ------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- ---------
    ALL_20100130204500RQ               300000       1244            0          0          0          20     10-FEB-10
    ALL_20100130210000RQ
    
    SQL> select subPARTITION_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED
      2  from dba_tab_subpartitions
      3  where table_owner='MAXR';
    
    SUBPARTITION_NAME                NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
    ------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- ---------
    ALL_20100130204500RQL01            150000        622            0          0          0          20   150000 10-FEB-10
    ALL_20100130204500RQL02            150000        622            0          0          0          20   150000 10-FEB-10
    ALL_20100130210000RQL01
    ALL_20100130210000RQL02
    
    
    -- DI NUOVO DROP/CREATE/INSERT
    
    SQL> begin
      2  DBMS_STATS.gather_table_stats (ownname               => 'MAXR',
      3  tabname               => 'CKCLASS_ALL',
      4  partname              => 'ALL_20100130204500RQL01',
      5  granularity => 'SUBPARTITION',
      6  estimate_percent      => DBMS_STATS.AUTO_SAMPLE_SIZE ,
      7  CASCADE               => FALSE);
      8  end;
      9  /
    
    Procedura PL/SQL completata correttamente.
    
    Passati: 00:00:00.34
    SQL>
    SQL>
    SQL> begin
      2  DBMS_STATS.gather_table_stats (ownname               => 'MAXR',
      3  tabname               => 'CKCLASS_ALL',
      4  partname              => 'ALL_20100130204500RQL02',
      5  granularity => 'SUBPARTITION',
      6  estimate_percent      => DBMS_STATS.AUTO_SAMPLE_SIZE ,
      7  CASCADE               => FALSE);
      8  end;
      9  /
    
    Procedura PL/SQL completata correttamente.
    
    Passati: 00:00:00.18
    SQL>
    SQL> begin
      2  DBMS_STATS.copy_table_stats (ownname => 'MAXR',
      3      tabname => 'CKCLASS_ALL',
      4      srcpartname => 'ALL_20100130204500RQL01',
      5      dstpartname => 'ALL_20100130210000RQL01');
      6  end;
      7  /
    
    Procedura PL/SQL completata correttamente.
    
    Passati: 00:00:00.06
    SQL>
    SQL> begin
      2  DBMS_STATS.copy_table_stats (ownname => 'MAXR',
      3      tabname => 'CKCLASS_ALL',
      4      srcpartname => 'ALL_20100130204500RQL02',
      5      dstpartname => 'ALL_20100130210000RQL02');
      6  end;
      7  /
    
    Procedura PL/SQL completata correttamente.
    
    Passati: 00:00:00.04
    SQL>
    
    SQL> select PARTITION_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED
      2  from dba_tab_partitions
      3  where table_owner='MAXR';
    
    PARTITION_NAME                   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
    ------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- ---------
    ALL_20100130210000RQ               300000       1244            0          0          0          20     10-FEB-10
    ALL_20100130204500RQ               300000       1244            0          0          0          20     10-FEB-10
    
    Passati: 00:00:00.03
    SQL> select subPARTITION_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED
      2  from dba_tab_subpartitions
      3  where table_owner='MAXR';
    
    SUBPARTITION_NAME                NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
    ------------------------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- ---------
    ALL_20100130204500RQL01            150000        622            0          0          0          20   150000 10-FEB-10
    ALL_20100130204500RQL02            150000        622            0          0          0          20   150000 10-FEB-10
    ALL_20100130210000RQL01            150000        622            0          0          0          20   150000 10-FEB-10
    ALL_20100130210000RQL02            150000        622            0          0          0          20   150000 10-FEB-10
    HTH

    Max
  • 10. Re: PARTITIONS AND SUBPARTITIONS
    Ric79 Newbie
    Currently Being Moderated
    Perfect the copy statistics runs in less than 1 sec.

    The copy statistics bug persists also in your version, but it's solved in 11.2

    Thanks a lot!

    Riccardo

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points