In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-14 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
Today, I will talk to you about the example analysis of Exchange Partition. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.
The conclusion of Exchange Partition experiment results:
Intermediate table (non-partitioned, empty table)
1. All the partition data of the source partition table has been transferred to the intermediate table, and the partition data of the source partition cannot be found.
2. Because both the source partition table and the target partition table generate partition names automatically by INTERVAL, there will be no partition names when there is no new data in the target partition table, so insert a piece of data into the target partition table and add a new partition.
3. After the new partition Exchange of the intermediate table and the target partition table, the new partition data of the intermediate table and the target partition table are exchanged.
4. When both the source partition table and the destination partition table are INTERVAL, there is no guarantee that the partition names exported by the original table are the same as those imported by the target table.
T3: source partition table
CREATE TABLE T3 (hid number,hdate date)
PARTITION BY RANGE (hid) INTERVAL (5)
(PARTITION P01 VALUES LESS THAN (10)
PARTITION p0q2 VALUES LESS THAN (14))
T2: intermediate table
CREATE TABLE T2 (hid number,hdate date)
T4: target partition table
CREATE TABLE T4 (hid number,hdate date)
PARTITION BY RANGE (hid) INTERVAL (5)
(PARTITION P01 VALUES LESS THAN (10)
PARTITION p0q2 VALUES LESS THAN (14))
Make T3 table have data
Insert into T3 values (1 SysdateMur1)
Insert into T3 values (10 ~ (th) SysdateMur1)
Insert into T3 values (20th SysdateMurray 1)
Insert into T3 values (30th SysdateMurray 1)
Commit
Select partition_name, partition_position from user_tab_partitions where table_name='T3'
PARTITION_NAME PARTITION_POSITION
P01 1
P0Q2 2
SYS_P66 3
SYS_P67 4
SQL > select * from T3
HID HDATE
--
1 04-AUG-17
10 04-AUG-17
20 04-AUG-17
30 04-AUG-17
The first step
Transfer partition 3 SYS_P66 of T3 to T2
ALTER TABLE T3 EXCHANGE PARTITION SYS_P66 WITH TABLE T2
Step two
To view the results, the SYS_P66 structure of partition 3 of T3 is still there, but the partition data is sent to the T2 table.
SQL > select partition_name, partition_position from user_tab_partitions where table_name='T3'
PARTITION_NAME PARTITION_POSITION
P01 1
P0Q2 2
SYS_P66 3
SYS_P67 4
SQL > select * from T3
HID HDATE
--
1 04-AUG-17
10 04-AUG-17
30 04-AUG-17
SQL > select * from T2
HID HDATE
--
20 04-AUG-17
Step three
Then transfer T2 to T4.
Because T4 is INTERVAL, if there is no new data, there will be no new partition, but Exchange can only use the existing partition name and the partition can insert this value, so we must first create a partition for the T4 table that can be used for Exchange.
SQL > ALTER TABLE T4 EXCHANGE PARTITION WITH TABLE T2;-T4 without any partition name, error reported
ALTER TABLE T4 EXCHANGE PARTITION WITH TABLE T2
*
ERROR at line 1:
ORA-14006: invalid partition name
SQL > ALTER TABLE T4 EXCHANGE PARTITION SYS_P66 WITH TABLE T2;-- T4 with a non-existent partition name, reported an error
ALTER TABLE T4 EXCHANGE PARTITION SYS_P66 WITH TABLE T2
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
SQL > ALTER TABLE T4 EXCHANGE PARTITION p01 WITH TABLE T2;-- T4 has a partition name, but this partition must be less than 10, an error was reported
ALTER TABLE T4 EXCHANGE PARTITION p01 WITH TABLE T2
*
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition
Create a partition for the T4 table that can be used for Exchange, and SYS_P68 is automatically generated in the case of INTERVAL.
SQL > insert into T4 values (1951 sysdatemur1)
1 row created.
SQL > commit
SQL > select partition_name, partition_position from user_tab_partitions where table_name='T4'
PARTITION_NAME PARTITION_POSITION
P01 1
P0Q2 2
SYS_P68 3
Continue Exchange and find that the data of T2 and T4 have been exchanged.
SQL > ALTER TABLE T4 EXCHANGE PARTITION SYS_P68 WITH TABLE T2
Table altered.
SQL > select * from T2
HID HDATE
--
19 04-AUG-17
SQL > select * from T4
HID HDATE
--
20 04-AUG-17
After reading the above, do you have any further understanding of the example analysis of Exchange Partition? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.