Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is Exchange Partition". In daily operation, I believe many people have doubts about what Exchange Partition is. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what is Exchange Partition?" Next, please follow the editor to study!

Pruning

Partition clipping is to execute the things in the plan, without setting anything by yourself (when select table does not specify the partition name), the optimizer can automatically extract the partitions that need to be scanned according to the partition key from FROM and WHERE words, thus avoiding full table scanning, reducing the number of scanned data blocks, and improving performance. Related fields dba_tab_partitions.partition_name, dba_tab_partitions.partition_position.

Partition clipping is divided into static and dynamic, static partition clipping occurs in the compilation phase, and dynamic partition clipping occurs in the execution phase.

Static tailoring: oracle knows which partitions to go. For example, after where conditional filtering, the pstart and pstop of the execution plan show the specific partition number.

Dynamic tailoring: if you cannot know how many partitions need to be scanned during the parsing phase, you can only determine at run time that the pstart and pstop of the execution plan display KEY (SQ) or: BF0000

PARTITION RANGE ALL: scan all partitions (specific partitions can be found in the fields Pstart and Pstop in the execution plan)

PARTITION RANGE SINGLE: scan a single partition (specific partitions can be found in the Pstart and Pstop fields in the execution plan)

PARTITION RANGE ITERATOR: scan multiple partitions before merging them (for more information on which partitions, please refer to the fields Pstart and Pstop in the execution plan)

Reference Partition

The business scenario targeted by Reference Partition is the primary foreign key association. After the primary table is partitioned, automatic child table partitioning can be achieved with the help of Reference Partition (regardless of whether there is a partitioning key on the child table or not). After the Reference Partition partition, the data records in the same primary table partition and the corresponding child table records are all on the same child table partition.

Exchange Partition

Exchange Partition refers to the exchange of data between a partition and a physical table. The data of the partition goes to the physical table, and the data of the physical table goes to the partition.

You can convert a partition (or subpartition) into a nonpartitioned table, and a nonpartitioned table into a partition (or subpartition) of a partitioned table by exchanging their data segments

In fact, it is similar to changing the data dictionary, changing the segment headers of partitions and entity tables, and the block of data storage is not moved.

In real life, there are common scenarios when using Exchange Partition, such as: there are two partition tables, one is the history table and the other is the current table. How to import the data from the current table 6 months ago into the history table on a regular basis?

Because the partition table cannot exchange data with the partition table directly, we can use the function of Exchange Partition to transfer the empty entity table, import the partitions of the current table 6 months ago into each entity table, and then import the data of each entity table into the empty new partitions of the history table (the reason is that when the current table and entity table do Exchange, no data will be written to the current table. When the entity table and the empty new partition Exchange of the history table after Exchange, the entity table becomes empty again)

Partition table A needs to migrate some data to partition table B.

1. The table structure, field order and field names of tables An and B should be the same.

2. If both partitions are interval, there is no guarantee that the partition names of the two partitions are the same.

The reason is that interval is automatically named by the system, and when the intermediate table is transferred to the target table, the target table must have a partition name before it can be converted, but how can the partition name of the target table be the same as the partition name of the source table?

At this point, the study of "what is Exchange Partition" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.