COPY_TABLE_STATS

The COPY_TABLE_STATS procedure copies statistics from a source partition (or subpartition) to a target 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 in the target partition as follows:

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

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

  • If the target partition is a DEFAULT partition, then:

    • 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, then:

    • The minimum value of the target partition is set to the minimum value in the list of values that describe the target partition.
    • The maximum value of the target partition is set to the maximum value in the list of values that describe the target partition.

• If the partition type is RANGE, then:

  • If the target partition is the first partition, then:

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

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

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

      • The minimum and maximum values of the target partition are set to the upper bound of the target partition.

  • If the target partition is not the first partition, then:

    • If the target partition is a single-column partition key, then:

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

      • Otherwise:

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

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

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

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

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

Examples

Create a hash-partitioned table named test_copy_tbl1 and perform related statistics 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 partition it by the col1 column using the hash function 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 of data into the test_copy_tbl1 table. The col1 column contains values from 1 to 10, and the col2 column contains values from 2 to 11.

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

   The returned 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 statistics for the P1 partition of the test_copy_tbl1 table. 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 in the DBMS_STATS package to copy the statistics for 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 and retrieve statistics for all partitions of the TEST_COPY_TBL1 table owned by SYS, including the table name, partition name, number of rows, number of blocks, average row length, whether the statistics type is locked, whether the statistics are outdated, and the 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 returned 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 and retrieve partition column statistics for the TEST_COPY_TBL1 table owned by SYS, sorted 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 returned 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
   

8. Execute the following statement to query the all_part_histograms view and retrieve partition column histogram information for the TEST_COPY_TBL1 table owned by SYS, sorted 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 returned 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