COPY_TABLE_STATS

The COPY_TABLE_STATS procedure copies the statistics from the source partition (or subpartition) to the destination partition (or subpartition).

Syntax

DBMS_STATS.COPY_TABLE_STATS (
  ownname          VARCHAR2,
  tabname          VARCHAR2,
  srcpartname      VARCHAR2,
  dstpartname      VARCHAR2,
  scale_factor     VARCHAR2,
  flags            NUMBER DEFAULT NULL,
  force            BOOLEAN DEFAULT FALSE);

Parameters

Exceptions

Considerations

This procedure updates the minimum and maximum values of the destination partition based on the following rules:

• If the partitioning type is HASH, the minimum and maximum values of the destination partition are the same as those of the source partition.

• If the partitioning type is LIST, the minimum and maximum values of the destination partition depend on the setting of the destination partition.

  • If the destination partition is a DEFAULT partition:

    • The minimum value of the destination partition is set to that of the source partition.
    • The maximum value of the destination partition is set to that of the source partition.

  • If the destination partition is a NOT DEFAULT partition:

    • The minimum value of the destination partition is set to the minimum value in the value list that describes the destination partition.
    • The maximum value of the destination partition is set to the maximum value in the value list that describes the destination partition.

• If the partitioning type is RANGE:

  • When the destination partition is the first partition:

    • If the upper bound of the destination partition is MAXVALUE:

      • The destination partition and source partition are the same, and you do not need to copy or update the minimum and maximum values. In other words, you do not need to set its minimum or maximum value.

    • If the upper bound of the destination partition is not MAXVALUE:

      • Both the maximum and minimum values of the destination partition are set to the upper bound of the destination partition.

  • When the destination partition is not the first partition:

    • If the partitioning key of the destination partition is a single column:

      • In the case that the source partition has a single value and the value is equal to the lower bound of the source partition, and that the upper bound of the destination partition is not MAXVALUE, both the maximum and minimum values of the destination partition are set to the lower bound of the destination partition.

      • Otherwise:

        • If the upper bound of the destination partition is MAXVALUE, both the maximum and minimum values are set to the lower bound of the destination partition, namely, the upper bound of the previous partition.
        • If the upper bound of the destination partition is not MAXVALUE, the maximum value is set to its upper bound and the minimum value is set to its lower bound.

    • If the partitioning key of the destination partition contains multiple columns:

      • If the source partition has only one value that is equal to its lower bound, the maximum and minimum values of the destination partition are both set to the lower bound of the destination partition.

      • Otherwise, the foregoing rule is used for the current column C. For a subsequent column Cx:

        • If MAXVALUE of Cx-1 is the same in the destination partition and the previous partition, the maximum value of the destination partition is set to its upper bound.
        • Otherwise, the maximum value of the destination partition column is set to the larger one of the upper bound of the destination partition and the maximum value of the source partition column.

Examples

Create a HASH-partitioned table named test_copy_tbl1 and perform operations related to statistics collection.

1. (Optional) If a table named test_copy_tbl1 already exists, execute the following statement to drop it:

   DROP TABLE test_copy_tbl1;
   

2. Execute the following statement to create a table named test_copy_tbl1, which contains two integer columns col1 and col2, and use the HASH function to partition the table by the col1 column into four partitions: p1, p2, p3, and p4.

   CREATE TABLE test_copy_tbl1(col1 INT, col2 INT) PARTITION BY HASH(col1) (PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4);
   

3. Execute the following statement to insert 10 rows into the test_copy_tbl1 table. To be specific, 1 to 10 are inserted into the col1 column and 2 to 11 are inserted into the col2 column.

   INSERT INTO test_copy_tbl1 SELECT level, level + 1 FROM dual CONNECT BY LEVEL <= 10;
   

   The return result is as follows:

   Query OK, 10 rows affected (0.060 sec)
   Records: 10  Duplicates: 0  Warnings: 0
   

4. Execute the following statement to call the gather_table_stats procedure in the DBMS_STATS package to collect the statistics of the P1 partition in the test_copy_tbl1 table. The method_opt parameter specifies the histogram size for all columns, which is 32 buckets here.

   CALL dbms_stats.gather_table_stats('SYS','TEST_COPY_TBL1', 'P1', method_opt=>'for all columns size 32');
   

5. Execute the following statement to call the copy_table_stats procedure in the DBMS_STATS package to copy the statistics of the P1 partition in the test_copy_tbl1 table to the P2 partition based on the scale factor 2.

   CALL dbms_stats.copy_table_stats('SYS','TEST_COPY_TBL1','P1','P2', scale_factor=>2);
   

6. Execute the following statement to query the all_tab_statistics view for the statistics of all partitions in the TEST_COPY_TBL1 table owned by the SYS user, including the table name, partition names, number of rows, number of blocks, average row length, whether the statistical type is locked, whether the statistics are outdated, and last analysis time.

   SELECT table_name, partition_name, num_rows, blocks, avg_row_len, stattype_locked locked, stale_stats, last_analyzed FROM all_tab_statistics WHERE table_name = 'TEST_COPY_TBL1' AND owner = 'SYS';
   

   The return result is as follows:

   +----------------+----------------+----------+--------+-------------+--------+-------------+---------------+
   | TABLE_NAME     | PARTITION_NAME | NUM_ROWS | BLOCKS | AVG_ROW_LEN | LOCKED | STALE_STATS | LAST_ANALYZED |
   +----------------+----------------+----------+--------+-------------+--------+-------------+---------------+
   | TEST_COPY_TBL1 | NULL           |       10 |   NULL |          40 | NULL   | NO          | 08-JAN-24     |
   | TEST_COPY_TBL1 | P1             |        2 |   NULL |          40 | NULL   | NO          | 08-JAN-24     |
   | TEST_COPY_TBL1 | P2             |        4 |   NULL |          40 | NULL   | NO          | 08-JAN-24     |
   | TEST_COPY_TBL1 | P3             |     NULL |   NULL |        NULL | NULL   | NULL        | NULL          |
   | TEST_COPY_TBL1 | P4             |     NULL |   NULL |        NULL | NULL   | NULL        | NULL          |
   +----------------+----------------+----------+--------+-------------+--------+-------------+---------------+
   5 rows in set
   

   For more information about the all_tab_statistics view, see ALL_TAB_STATISTICS.

7. Execute the following statement to query the all_part_col_statistics view for the statistics of the partition columns of the TEST_COPY_TBL1 table owned by the SYS user, and sort the statistics by partition name and column name.

   SELECT * FROM all_part_col_statistics WHERE table_name = 'TEST_COPY_TBL1' AND owner = 'SYS' ORDER BY partition_name, column_name;
   

   The return result is as follows:

   +-------+----------------+----------------+-------------+--------------+-----------+------------+---------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
   | OWNER | TABLE_NAME     | PARTITION_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | SAMPLE_SIZE | LAST_ANALYZED | GLOBAL_STATS | USER_STATS | NOTES | AVG_COL_LEN | HISTOGRAM |
   +-------+----------------+----------------+-------------+--------------+-----------+------------+---------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
   | SYS   | TEST_COPY_TBL1 | P1             | COL1        |            2 | 2         | 7          |     .25 |         0 |           2 |           2 | 08-JAN-24     | NO           | NO         | NULL  |          20 | FREQUENCY |
   | SYS   | TEST_COPY_TBL1 | P1             | COL2        |            2 | 3         | 8          |     .25 |         0 |           2 |           2 | 08-JAN-24     | NO           | NO         | NULL  |          20 | FREQUENCY |
   | SYS   | TEST_COPY_TBL1 | P2             | COL1        |            2 | 2         | 7          |     .25 |         0 |           2 |           2 | 08-JAN-24     | NO           | NO         | NULL  |          20 | FREQUENCY |
   | SYS   | TEST_COPY_TBL1 | P2             | COL2        |            2 | 3         | 8          |     .25 |         0 |           2 |           2 | 08-JAN-24     | NO           | NO         | NULL  |          20 | FREQUENCY |
   | SYS   | TEST_COPY_TBL1 | P3             | COL1        |         NULL | NULL      | NULL       |    NULL |      NULL |        NULL |        NULL | NULL          | NULL         | NULL       | NULL  |        NULL | NULL      |
   | SYS   | TEST_COPY_TBL1 | P3             | COL2        |         NULL | NULL      | NULL       |    NULL |      NULL |        NULL |        NULL | NULL          | NULL         | NULL       | NULL  |        NULL | NULL      |
   | SYS   | TEST_COPY_TBL1 | P4             | COL1        |         NULL | NULL      | NULL       |    NULL |      NULL |        NULL |        NULL | NULL          | NULL         | NULL       | NULL  |        NULL | NULL      |
   | SYS   | TEST_COPY_TBL1 | P4             | COL2        |         NULL | NULL      | NULL       |    NULL |      NULL |        NULL |        NULL | NULL          | NULL         | NULL       | NULL  |        NULL | NULL      |
   +-------+----------------+----------------+-------------+--------------+-----------+------------+---------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
   8 rows in set
   

   For more information about the all_part_col_statistics view, see ALL_PART_COL_STATISTICS.

8. Execute the following statement to query the all_part_histograms view for the histogram statistics of the partition columns of the TEST_COPY_TBL1 table owned by the SYS user, and sort the statistics by partition name and column name.

   SELECT * FROM all_part_histograms  WHERE table_name = 'TEST_COPY_TBL1' AND owner = 'SYS' ORDER BY partition_name, column_name;
   

   The return result is as follows:

   +-------+----------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
   | OWNER | TABLE_NAME     | PARTITION_NAME | COLUMN_NAME | ENDPOINT_NUMBER | ENDPOINT_VALUE | ENDPOINT_ACTUAL_VALUE | ENDPOINT_ACTUAL_VALUE_RAW | ENDPOINT_REPEAT_COUNT |
   +-------+----------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
   | SYS   | TEST_COPY_TBL1 | P1             | COL1        |               2 |           NULL | 7                     | 7                         |                     1 |
   | SYS   | TEST_COPY_TBL1 | P1             | COL1        |               1 |           NULL | 2                     | 2                         |                     1 |
   | SYS   | TEST_COPY_TBL1 | P1             | COL2        |               2 |           NULL | 8                     | 8                         |                     1 |
   | SYS   | TEST_COPY_TBL1 | P1             | COL2        |               1 |           NULL | 3                     | 3                         |                     1 |
   | SYS   | TEST_COPY_TBL1 | P2             | COL1        |               2 |           NULL | 7                     | 7                         |                     1 |
   | SYS   | TEST_COPY_TBL1 | P2             | COL1        |               1 |           NULL | 2                     | 2                         |                     1 |
   | SYS   | TEST_COPY_TBL1 | P2             | COL2        |               2 |           NULL | 8                     | 8                         |                     1 |
   | SYS   | TEST_COPY_TBL1 | P2             | COL2        |               1 |           NULL | 3                     | 3                         |                     1 |
   +-------+----------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
   8 rows in set
   

   For more information about the all_part_histograms view, see ALL_PART_HISTOGRAMS.