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

This procedure updates the minimum and maximum values in the destination partition as follows:

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

• If the partition type is LIST, the behavior depends on the destination partition's settings:

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

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

  • If the destination partition is a NOT DEFAULT partition, then:

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

• If the partition type is RANGE, then:

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

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

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

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

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

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

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

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

      • Otherwise:

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

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

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

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

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

To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the owner of the table or have the SYSDBA privilege.

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 IF EXISTS 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: p0, p1, p2, and p3.

   CREATE TABLE test_copy_tbl1(col1 INT, col2 INT) PARTITION BY HASH(col1) PARTITIONS 4;
   

3. Execute the following statement to insert data into the test_copy_tbl1 table.

   INSERT INTO test_copy_tbl1 VALUES (1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(7,8),(8,9),(9,10),(10,11);
   

4. Execute the following statement to call the gather_table_stats procedure in the DBMS_STATS package to collect statistics for the p0 partition of the test_copy_tbl1 table.

   CALL dbms_stats.gather_table_stats(NULL, 'test_copy_tbl1', 'p0', 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 statistics for the p0 partition of the test_copy_tbl1 table to the p1 partition with a scale factor of 2.

   CALL dbms_stats.copy_table_stats(NULL, 'test_copy_tbl1', 'p0', 'p1', '2');
   

   Alternatively, use named parameters:

   CALL dbms_stats.copy_table_stats(NULL, 'test_copy_tbl1', 'p0', 'p1', scale_factor=>'2');