COPY_TABLE_STATS

The COPY_TABLE_STATS procedure copies the statistics from the source partition (or sub-partition) to the target partition (or sub-partition).

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

The procedure updates the minimum and maximum values in the target partition as follows:

• If the partition type is HASH, the minimum and maximum values in the target partition are the same as those in the source partition.

• If the partition type is LIST, the behavior depends on the settings of the target partition:

  • If the target partition is the DEFAULT partition:

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

  • If the target partition is a NOT DEFAULT partition:

    • The minimum value of the target partition is set to the minimum value in the value list describing the target partition.
    • The maximum value of the target partition is set to the maximum value in the value list describing the target partition.

• If the partition type is RANGE:

  • If the target partition is the first partition:

    • If the upper boundary of the target partition is MAXVALUE, then:

      • The target partition and the source partition are the same, and there is no need to copy or update the minimum and maximum values. Therefore, they do not need to be set.

    • If the upper boundary of the target partition is not MAXVALUE, then:

      • The minimum and maximum values of the target partition will both be set to the upper boundary of the target partition.

  • If the target partition is not the first partition:

    • If the target partition is a single column partition key:

      • If the source partition has only one value that is equal to the lower boundary of the source partition and the upper boundary of the target partition is not MAXVALUE, then the minimum and maximum values of the target partition are both set to the lower boundary of the target partition.

      • Otherwise:

        • If the upper boundary of the target partition is MAXVALUE, then both the maximum and minimum values are set to the lower boundary of the target partition (that is, the upper boundary of the previous partition).
        • If the upper boundary of the target partition is not MAXVALUE, then the maximum value is set to the upper boundary of the target partition, and the minimum value is set to the lower boundary of the target partition.

    • If the target partition is a multi-column partition key:

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

      • Otherwise, for the current column C, it follows the rules mentioned earlier, and for the subsequent columns Cx:

        • If Cx-1 is the same as MAXVALUE before the target partition, then the maximum value of the target partition is set to the upper boundary of the target partition.
        • Otherwise, the maximum value of the target partition is set to the greater value of the upper limit of the target partition and the maximum value of the source partition column.

Examples

The following example demonstrates how to create a HASH partitioned table test_copy_tbl1 and the related statistical operations.

1. (Optional) If a table named test_copy_tbl1 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 with two integer columns col1 and col2, and to partition the table using the HASH function based on col1, with 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 of data into the test_copy_tbl1 table, with the col1 column ranging from 1 to 10, and the col2 column ranging from 2 to 11.

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

   The 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 from the DBMS_STATS package to collect statistics for the P1 partition of the table named test_copy_tbl1. The method_opt parameter specifies a histogram size of 32 buckets for all columns.

   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 from the DBMS_STATS package to copy the statistics of the P1 partition of the test_copy_tbl1 table to the P2 partition with a scale factor of 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 to retrieve the relevant statistics for all partitions of the table named TEST_COPY_TBL1 owned by SYS, including table name, partition name, number of rows, number of blocks, average row length, whether statistics are locked, whether statistics are stale, and the last analyzed 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 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 to retrieve the partition column statistics for the table named TEST_COPY_TBL1 owned by SYS, and sort them 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 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 to retrieve the partition column histogram information for the table named TEST_COPY_TBL1 owned by SYS, and sort them 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 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.