[Oracle] Partition table exchange Heap table
Heap Table --(exchange)--> Partiton table
SQL> create table part_tab1(id int,owner varchar2(30),name varchar2(30),type varchar2(30),created date)
2 partition by range(id)
3 (partition p1 values less than(10000) tablespace users,
4 partition p2 values less than(50000) tablespace users,
5 partition p3 values less than(100000) tablespace users
6 );
Table created. #建立測試的表,range partition
SQL> insert into part_tab1 select object_id,owner,object_name,object_type,created from all_objects;
56331 rows created.
Elapsed: 00:00:00.91 #寫入一部分資料
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> select count(*) from part_tab1;
COUNT(*)
----------
56331
SQL> create index part_tab1_index on part_tab1(id) local; #建立local indexes
Index created.
SQL> create table temp_part_tab1 as select * from part_tab1; #建立臨時table
Table created.
Elapsed: 00:00:00.06
SQL> create index temp_part_tab1_index on temp_part_tab1(id); #建立臨時table的indexes
Index created.
Elapsed: 00:00:00.11
SQL> update temp_part_tab1 set id=id+100000;
56331 rows updated.
Elapsed: 00:00:01.55
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> alter table part_tab1 add partition p4 values less than(200000) tablespace users; #建立需要使用的partition
Table altered.
ERROR at line 1:
ORA-02149: Specified partition does not exist #如果再exchange前沒有建立相應的parition會報這個錯誤.
SQL> alter table part_tab1 exchange partition p4 with table temp_part_tab1 including indexes without validation;
Table altered.
Elapsed: 00:00:00.10
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition #沒有新增 without validation選項,則exchange時會檢查id是否符合parition的規則.
SQL> select count(*) from part_tab1;
COUNT(*)
----------
112662
Elapsed: 00:00:00.01
SQL> select count(*) from part_tab1 partition (p4); #資料已經全部exchange到partition p4中
COUNT(*)
----------
56331
SQL> select count(*) from temp_part_tab1; #exchange後原有的臨時表中沒有資料了.
COUNT(*)
----------
0
==========================================================================
PartitonTable --(exchange)--> Heap table
SQL> alter table part_tab1 exchange partition p1 with table temp_part_tab1 including indexes;
Table altered.
Elapsed: 00:00:00.02
SQL>
SQL> select count(*) from temp_part_tab1;
COUNT(*)
----------
4490
Elapsed: 00:00:00.01
SQL> select * from part_tab1 partition(p1);
no rows selected
Elapsed: 00:00:00.01
SQL> select segment_name,partition_name from user_segments where segment_name like '%PART%';
SEGMENT_NAME PARTITION_NAME
---------------------------------------- ------------------------------------------------------------------------------------------
TEMP_PART_TAB1
PART_TAB1 P2
PART_TAB1 P3
PART_TAB1 P4
PART_TAB1 P5
TEMP_PART_TAB1_INDEX
PART_TAB1_INDEX P2
PART_TAB1_INDEX P3
PART_TAB1_INDEX P4
PART_TAB1_INDEX P5
10 rows selected.
Elapsed: 00:00:00.07
SQL> list
1* select index_name,partition_name,status from user_ind_partitions
SQL> /
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------------------------------------------------------------------ ------------------------
PART_TAB1_INDEX P5 USABLE
PART_TAB1_INDEX P2 USABLE
PART_TAB1_INDEX P3 USABLE
PART_TAB1_INDEX P4 USABLE
PART_TAB1_INDEX P1 USABLE
Elapsed: 00:00:00.01
SQL> select index_name,table_name,status from user_indexes where table_name like '%PART%';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------------------------------------------------------------------ ------------------------
TEMP_PART_TAB1_INDEX TEMP_PART_TAB1 VALID
PART_TAB1_INDEX PART_TAB1 N/A
Elapsed: 00:00:00.27
SQL> create table part_tab1(id int,owner varchar2(30),name varchar2(30),type varchar2(30),created date)
2 partition by range(id)
3 (partition p1 values less than(10000) tablespace users,
4 partition p2 values less than(50000) tablespace users,
5 partition p3 values less than(100000) tablespace users
6 );
Table created. #建立測試的表,range partition
SQL> insert into part_tab1 select object_id,owner,object_name,object_type,created from all_objects;
56331 rows created.
Elapsed: 00:00:00.91 #寫入一部分資料
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> select count(*) from part_tab1;
COUNT(*)
----------
56331
SQL> create index part_tab1_index on part_tab1(id) local; #建立local indexes
Index created.
SQL> create table temp_part_tab1 as select * from part_tab1; #建立臨時table
Table created.
Elapsed: 00:00:00.06
SQL> create index temp_part_tab1_index on temp_part_tab1(id); #建立臨時table的indexes
Index created.
Elapsed: 00:00:00.11
SQL> update temp_part_tab1 set id=id+100000;
56331 rows updated.
Elapsed: 00:00:01.55
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> alter table part_tab1 add partition p4 values less than(200000) tablespace users; #建立需要使用的partition
Table altered.
ERROR at line 1:
ORA-02149: Specified partition does not exist #如果再exchange前沒有建立相應的parition會報這個錯誤.
SQL> alter table part_tab1 exchange partition p4 with table temp_part_tab1 including indexes without validation;
Table altered.
Elapsed: 00:00:00.10
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition #沒有新增 without validation選項,則exchange時會檢查id是否符合parition的規則.
SQL> select count(*) from part_tab1;
COUNT(*)
----------
112662
Elapsed: 00:00:00.01
SQL> select count(*) from part_tab1 partition (p4); #資料已經全部exchange到partition p4中
COUNT(*)
----------
56331
SQL> select count(*) from temp_part_tab1; #exchange後原有的臨時表中沒有資料了.
COUNT(*)
----------
0
==========================================================================
PartitonTable --(exchange)--> Heap table
SQL> alter table part_tab1 exchange partition p1 with table temp_part_tab1 including indexes;
Table altered.
Elapsed: 00:00:00.02
SQL>
SQL> select count(*) from temp_part_tab1;
COUNT(*)
----------
4490
Elapsed: 00:00:00.01
SQL> select * from part_tab1 partition(p1);
no rows selected
Elapsed: 00:00:00.01
SQL> select segment_name,partition_name from user_segments where segment_name like '%PART%';
SEGMENT_NAME PARTITION_NAME
---------------------------------------- ------------------------------------------------------------------------------------------
TEMP_PART_TAB1
PART_TAB1 P2
PART_TAB1 P3
PART_TAB1 P4
PART_TAB1 P5
TEMP_PART_TAB1_INDEX
PART_TAB1_INDEX P2
PART_TAB1_INDEX P3
PART_TAB1_INDEX P4
PART_TAB1_INDEX P5
10 rows selected.
Elapsed: 00:00:00.07
SQL> list
1* select index_name,partition_name,status from user_ind_partitions
SQL> /
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------------------------------------------------------------------ ------------------------
PART_TAB1_INDEX P5 USABLE
PART_TAB1_INDEX P2 USABLE
PART_TAB1_INDEX P3 USABLE
PART_TAB1_INDEX P4 USABLE
PART_TAB1_INDEX P1 USABLE
Elapsed: 00:00:00.01
SQL> select index_name,table_name,status from user_indexes where table_name like '%PART%';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------------------------------------------------------------------ ------------------------
TEMP_PART_TAB1_INDEX TEMP_PART_TAB1 VALID
PART_TAB1_INDEX PART_TAB1 N/A
Elapsed: 00:00:00.27
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-2134644/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 普通table 轉換為partition tableOracle
- 拆分Table 為Partition Table
- Oracle Partitioned Table:exchange的利用Oracle
- partition table and partition indexIndex
- Oracle分割槽表(Partition Table)Oracle
- partition table test
- partition table(1)
- partition table(2)
- When to Partition a Table
- create a partition table using a exsit table
- 分割槽表PARTITION table
- Applying “Incremental Statistic” for Oracle Big Partition TableAPPREMOracle
- 資料庫表--heap organized table資料庫Zed
- db2 partition table testDB2
- Interval Partition table 11G
- How to partition a non-partitioned table
- 分割槽表PARTITION table(轉)
- partition table中truncate應用
- partition table update partition-key result in changing tablespace
- exchange partition
- mysql partition table use to_days bugMySql
- 關於partition table import的問題Import
- doesn't contain a valid partition tableAI
- add hash partition , default tablespace for patitioned table
- exchange partition(轉)
- Pruning、Reference Partition、Exchange Partition
- oracle temporary tableOracle
- oracle shrink tableOracle
- Oracle Table LocksOracle
- Alter table for ORACLEOracle
- Oracle Table FunctionOracleFunction
- 【DB】Direct Path EXP Corrupts The Dump If An Empty Table Partition Exists
- exchange partition原理探究
- exchange partition 實驗
- exchange partition 的用法
- Oracle -- 深入體會PLAN_TABLE、PLAN_TABLE$Oracle
- Oracle --- PLAN_TABLE$和PLAN_TABLE區別Oracle
- oracle cache table(轉)Oracle