The COPY_TABLE_STATS procedure is used to copy 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
Parameter |
Description |
|---|---|
| ownname | Specify the schema name of the table containing the source and target partitions (or subpartitions). |
| tabname | Specifies the name of the table corresponding to the source and target partitions (or subpartitions). |
| srcpartname | Specifies the source partition (or subpartition) from which statistics are to be copied. |
| dstpartname | Specify the target partition (or subpartition) to receive statistics. |
| scale_factor | When data is replicated to a target partition (or subpartition), this parameter specifies how to proportionally adjust statistical attributes such as nblks (number of blocks) and nrows (number of rows). |
| flags | The default value is NULL. This parameter is generally not required for users. |
| force | Default: FALSE. If this parameter value is set to TRUE, the replication of statistics will continue even if the target partition (or subpartition) is currently locked. |
Exceptions
Error Code |
Description |
|---|---|
| HY000 | Indicates that the partition name is invalid. |
| HY000 | Indicates that the source partition type is different from the target partition type. |
Usage instructions
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 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 a non-DEFAULT partition, then:
- The minimum value of the target partition is set to the minimum value in the list of values describing the target partition.
- The maximum value of the target partition is set to the maximum value in the list of values describing 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 the source partition are the same. There is no need to copy or update the minimum and maximum values, meaning they do not need to be set.
If the upper bound of the target partition is not
MAXVALUE, then:- Both the maximum and minimum 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 has a single-column partitioning key, then:
If the source partition has only one value equal to its lower bound, and the upper bound of the target partition is not
MAXVALUE, then both the maximum and minimum values of the target partition are set to its lower bound.Otherwise:
- If the upper bound of the target partition is
MAXVALUE, set both the maximum and minimum values to the lower bound of the target partition (which is the upper bound of the previous partition). - If the upper bound of the target partition is not
MAXVALUE, set the maximum value to the upper boundary of the target partition and the minimum value to the lower boundary of the target partition.
- If the upper bound of the target partition is
If the target partition has a multi-column partitioning key, then:
If the source partition has only one value that equals its lower bound, both the maximum and minimum values of the target partition are set to the lower bound of the target partition.
Otherwise, the current column C is processed according to the preceding rule, and for subsequent columns Cx, the following applies:
- Cx-1: If the
MAXVALUEof the target partition is the same as that of the partition before it, the maximum value of the target partition is set to its upper boundary. - Otherwise, the maximum value of the target partition column is set to the larger of the target partition upper limit and the maximum value of the source partition column.
- Cx-1: If the
To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the table owner or have the SYSDBA privilege to call this procedure.
Examples
Create a HASH-partitioned table named test_copy_tbl1 and perform related statistics operations.
(Optional) If a table named
test_copy_tbl1exists, execute the following statement to drop it.DROP TABLE IF EXISTS test_copy_tbl1;Execute the following statement to create a table named
test_copy_tbl1, which contains two integer columnscol1andcol2. Partitioncol1using the HASH function into four partitions:p0,p1,p2, andp3.CREATE TABLE test_copy_tbl1(col1 INT, col2 INT) PARTITION BY HASH(col1) PARTITIONS 4;Execute the following statement to insert data into the
test_copy_tbl1table.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);Execute the following statement to call the
gather_table_statsprocedure in theDBMS_STATSpackage to collect statistics for partitionp0of tabletest_copy_tbl1.CALL dbms_stats.gather_table_stats(NULL, 'test_copy_tbl1', 'p0', method_opt=>'for all columns size 32');Execute the following statement to call the
copy_table_statsprocedure in theDBMS_STATSpackage, and copy the statistics of partitionp0from tabletest_copy_tbl1to partitionp1with a scale factor of 2.CALL dbms_stats.copy_table_stats(NULL, 'test_copy_tbl1', 'p0', 'p1', '2');or use named parameters:
CALL dbms_stats.copy_table_stats(NULL, 'test_copy_tbl1', 'p0', 'p1', scale_factor=>'2');