COPY_TABLE_STATS

The COPY_TABLE_STATS procedure copies statistics from a source partition (or subpartition) to a 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

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

• For a partitioned table that uses a HASH partitioning method, the minimum and maximum values of the target partition are the same as those of the source partition.

• If the partitioning type is LIST, behavior depends on the settings for the target partition.

  • If the target partition is the 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 not a DEFAULT partition, then:

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

• For RANGE partitions, you can specify:

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

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

      • The target partition and the source partition are the same. In this case, you do not need to copy or update the partition's min or max values.

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

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

  • If the destination partition is not the first partition:

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

      • The minimum and maximum values of the destination partition are both set to the lower bound of the destination partition if the source partition has only one value, and this value is equal to the lower bound of the source partition. In addition, the upper bound of the destination partition is not MAXVALUE.

      • Otherwise:

        • If the upper boundary of the target partition is MAXVALUE, the maximum and minimum 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, 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 composite partitioned column, then:

      • If the source partition contains only one value, which 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 adopts the previous rule, and for subsequent columns Cx, it would be:

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

Examples

Create a hash-partitioned table named test_copy_tbl1 and perform 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 partition the table 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, where 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 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 that the histogram size for all columns is 32 buckets.

   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 with a scaling factor of 2 to the P2 partition.

   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 obtain the 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 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 obtain the 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 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 and obtain the 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 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.