溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章給大家分享的是有關(guān)數(shù)據(jù)庫中怎么將一個(gè)普通表轉(zhuǎn)換為分區(qū)表的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過來看看吧。
各位技術(shù)愛好者，看完本文后，你可以掌握如下的技能，也可以學(xué)到一些其它你所不知道的知識，~O(∩_∩)O~：
① 將一個(gè)普通表轉(zhuǎn)換為分區(qū)表的常用方法（重點(diǎn)）
② 在線重定義的使用
③ ctas和insert的優(yōu)化
④ DML語句如何開啟并行操作，如何查看DML是否開啟了并行
Tips：
① 若文章代碼格式有錯(cuò)亂，推薦使用QQ、搜狗或360瀏覽器，也可以下載pdf格式的文檔來查看，pdf文檔下載地址：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）
② 本篇BLOG中命令的輸出部分需要特別關(guān)注的地方我都用灰色背景和粉紅色字體來表示，比如下邊的例子中，thread 1的最大歸檔日志號為33，thread 2的最大歸檔日志號為43是需要特別關(guān)注的地方；而命令一般使用黃色背景和紅色字體標(biāo)注；對代碼或代碼輸出部分的注釋一般采用藍(lán)色字體表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有錯(cuò)誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
參考文檔都是MOS上How to Partition a Non-partitioned / Regular / Normal Table (文檔 ID 1070693.6)，已上傳到云盤，大家可自行下載。
本文介紹了4種非分區(qū)表轉(zhuǎn)換為分區(qū)表的幾種方法，參考文檔來自于MOS。
將普通表轉(zhuǎn)換成分區(qū)表有4種方法，這個(gè)在MOS文檔上有說明（How to Partition a Non-partitioned / Regular / Normal Table (文檔 ID 1070693.6)）：
1. Export/import method
2. Insert with a subquery method
3. Partition exchange method
4. DBMS_REDEFINITION
采用邏輯導(dǎo)出導(dǎo)入很簡單，首先在源庫建立分區(qū)表，然后將數(shù)據(jù)導(dǎo)出，然后導(dǎo)入到新建的分區(qū)表即可，
1) 導(dǎo)出表：exp usr/pswd tables=numbers file=exp.dmp
2) 刪除表：drop table numbers;
3) 重建分區(qū)表的定義：
create table numbers (qty number(3), name varchar2(15))
partition by range (qty)
(partition p1 values less than (501),
partition p2 values less than (maxvalue));
4) 利用ignore=y來導(dǎo)入分區(qū)表：imp usr/pswd file=exp.dmp ignore=y
創(chuàng)建普通表并插入測試數(shù)據(jù)
LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
Table created.
LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
87069 rows created.
LHR@dlhr> commit;
Commit complete.
LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1107
采用expdp導(dǎo)出表
[ZFXDESKDB2:oracle]:/tmp>expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp INCLUDE=TABLE:\"IN \(\'T\'\)\" SCHEMAS=LHR LOGFILE=expdp_T.log
Export: Release 11.2.0.4.0 - Production on Fri May 27 11:07:46 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp INCLUDE=TABLE:"IN ('T')" SCHEMAS=LHR LOGFILE=expdp_T.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "LHR"."T" 1.406 MB 87091 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/oracle/app/oracle/admin/dlhr/dpdump/lhr_t.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 27 11:07:57 2016 elapsed 0 00:00:11
刪除原表，創(chuàng)建一個(gè)分區(qū)表結(jié)構(gòu)：
LHR@dlhr> drop table t;
Table dropped.
LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE )
2 PARTITION BY RANGE (TIME)
3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
4 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
5 PARTITION T3 VALUES LESS THAN (MAXVALUE))
6 ;
Table created.
LHR@dlhr>
導(dǎo)入到分區(qū)表
[ZFXDESKDB2:oracle]:/tmp>impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp SCHEMAS=LHR table_exists_action=APPEND LOGFILE=impdp_T.log
Import: Release 11.2.0.4.0 - Production on Fri May 27 11:12:40 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp SCHEMAS=LHR table_exists_action=APPEND LOGFILE=impdp_T.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "LHR"."T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "LHR"."T" 1.406 MB 87091 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri May 27 11:12:46 2016 elapsed 0 00:00:05
[ZFXDESKDB2:oracle]:/tmp>
查詢導(dǎo)入后的情況：
SYS@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1083
SYS@dlhr> SELECT D.TABLE_OWNER,D.TABLE_NAME,D.PARTITION_NAME FROM DBA_TAB_PARTITIONS d WHERE d.table_name='T';
TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
LHR T T1
LHR T T2
LHR T T3
SYS@dlhr>
這種方法的特點(diǎn)是:
優(yōu)點(diǎn)：方法簡單易用，由于采用DDL語句，不會產(chǎn)生UNDO，且只產(chǎn)生少量REDO，效率相對較高，而且建表完成后數(shù)據(jù)已經(jīng)在分布到各個(gè)分區(qū)中了。
不足：對于數(shù)據(jù)的一致性方面還需要額外的考慮。由于幾乎沒有辦法通過手工鎖定T表的方式保證一致性，在執(zhí)行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修改可能會丟失，如果要保證一致性，需要在執(zhí)行完語句后對數(shù)據(jù)進(jìn)行檢查，而這個(gè)代價(jià)是比較大的。另外在執(zhí)行兩個(gè)RENAME語句之間執(zhí)行的對T的訪問會失敗。
適用于修改不頻繁的表，在閑時(shí)進(jìn)行操作，表的數(shù)據(jù)量不宜太大。
主要有2種方式，ctas和insert方式，下邊分別介紹：
利用CTAS語法在創(chuàng)建分區(qū)表的時(shí)候可以一起插入數(shù)據(jù)，也可以創(chuàng)建好表結(jié)構(gòu)再insert 進(jìn)去。 CTAS這種方法采用DDL語句，不產(chǎn)生UNDO，只產(chǎn)生少量REDO，建表完成后數(shù)據(jù)已經(jīng)在分布到各個(gè)分區(qū)中。
創(chuàng)建普通表并插入測試數(shù)據(jù)
LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
Table created.
LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
87069 rows created.
LHR@dlhr> commit;
Commit complete.
LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1085
創(chuàng)建一個(gè)分區(qū)表，注意這里的分區(qū)表的列后邊沒有數(shù)據(jù)類型：
LHR@dlhr> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
2 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
3 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
4 PARTITION T3 VALUES LESS THAN (MAXVALUE))
5 AS SELECT ID, TIME FROM T;
Table created.
LHR@dlhr>
改變表名
Table renamed.
LHR@dlhr> rename t_new to t;
Table renamed.
驗(yàn)證新表數(shù)據(jù)
LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1085
LHR@dlhr>
對于CTAS建表語句性能的提升可以通過如下的方式，① 加nologging ② 并行DDL ③ 查詢并行，需要說明的是建表完成后根據(jù)需要將表修改為logging模式。
CREATE TABLE T_NEW (ID, TIME)
PARTITION BY RANGE (TIME)
(PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
PARTITION T3 VALUES LESS THAN (MAXVALUE))
nologging parallel 4
AS SELECT /*+PARALLEL*/ ID, TIME FROM T;
執(zhí)行計(jì)劃：
SYS@dlhr> explain plan for CREATE TABLE T_NEW (ID, TIME)
2 PARTITION BY RANGE (TIME)
3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
4 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
5 PARTITION T3 VALUES LESS THAN (MAXVALUE))
6 nologging parallel 4
7 AS SELECT /*+PARALLEL*/ ID, TIME FROM T;
Explained.
SYS@dlhr> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4064487821
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 82787 | 1778K| 14 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 82787 | 1778K| 4 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T_NEW | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 82787 | 1778K| 4 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T | 82787 | 1778K| 4 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: skipped because of IO calibrate statistics are missing
17 rows selected.
SYS@dlhr>
可以看到對T表的查詢是并行的，create table也是并行的，這在源表的數(shù)據(jù)量非常大的情況下性能顯著。
這種方法就是先建立表結(jié)構(gòu)然后使用insert 來實(shí)現(xiàn)。
看示例：
創(chuàng)建普通表T_LHR_20160527
LHR@dlhr> CREATE TABLE T_LHR_20160527 (ID NUMBER PRIMARY KEY, TIME DATE);
Table created.
LHR@dlhr> INSERT INTO T_LHR_20160527 SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
87098 rows created.
LHR@dlhr> commit;
Commit complete.
LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from T_LHR_20160527 t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1114
創(chuàng)建一個(gè)分區(qū)表T_LHR_20160527_NEW：
LHR@dlhr> CREATE TABLE T_LHR_20160527_NEW (ID NUMBER, TIME DATE)
2 PARTITION BY RANGE (TIME)
3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
4 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
5 PARTITION T3 VALUES LESS THAN (MAXVALUE));
Table created.
從源表查詢插入到新表中：
LHR@dlhr> alter table T_LHR_20160527_NEW nologging;
Table altered.
LHR@dlhr> alter session enable parallel dml;
Session altered.
LHR@dlhr> insert /*+APPEND PARALLEL*/ into T_LHR_20160527_NEW (ID, TIME) select * from T_LHR_20160527;
87098 rows created.
LHR@dlhr> commit;
Commit complete.
刪除源表，重命名新表
LHR@dlhr> drop table T_LHR_20160527;
Table dropped.
LHR@dlhr> rename T_LHR_20160527_NEW to T_LHR_20160527;
Table renamed.
驗(yàn)證新表數(shù)據(jù)：
LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from T_LHR_20160527 t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1114
LHR@dlhr>
INSERT性能提升的方式，① 表修改為nologging ② 禁用表上的索引，可以將數(shù)據(jù)插入完成后再建索引 ③ 啟用并行DML alter session enable parallel dml; ④ 采用 append方式插入
commit;
alter session enable parallel dml;
alter table T_LHR_20160527_NEW nologging;
insert /*+APPEND PARALLEL*/ into T_LHR_20160527_NEW (ID, TIME) select /*+PARALLEL(t3,4)*/ * from T_LHR_20160527;
采用并行DML必須執(zhí)行alter session enable parallel dml;才可以啟用并行DML,執(zhí)行計(jì)劃:
LHR@dlhr> explain plan for insert /*+APPEND PARALLEL*/ into T_LHR_20160527 (ID, TIME) select /*+PARALLEL(t3,4)*/ * from t3;
Explained.
LHR@dlhr> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 584641640
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 6897K| 144M| 272 (4)| 00:00:04 | | | |
| 1 | LOAD AS SELECT | T_LHR_20160527 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T3 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: skipped because of IO calibrate statistics are missing
17 rows selected.
LHR@dlhr> commit;
Commit complete.
LHR@dlhr> alter session enable parallel dml;
Session altered.
LHR@dlhr> explain plan for insert /*+APPEND PARALLEL*/ into T_LHR_20160527 (ID, TIME) select /*+PARALLEL(t3,4)*/ * from t3;
Explained.
LHR@dlhr> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 576433284
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 6897K| 144M| 272 (4)| 00:00:04 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T_LHR_20160527 | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T3 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: skipped because of IO calibrate statistics are missing
17 rows selected.
LHR@dlhr>
這種方法的特點(diǎn)
優(yōu)點(diǎn)：只是對數(shù)據(jù)字典中分區(qū)和表的定義進(jìn)行了修改，沒有數(shù)據(jù)的修改或復(fù)制，效率最高。如果對數(shù)據(jù)在分區(qū)中的分布沒有進(jìn)一步要求的話，實(shí)現(xiàn)比較簡單。在執(zhí)行完RENAME操作后，可以檢查T_OLD中是否存在數(shù)據(jù)，如果存在的話，直接將這些數(shù)據(jù)插入到T中，可以保證對T插入的操作不會丟失。
不足：仍然存在一致性問題，交換分區(qū)之后RENAME T_NEW TO T之前，查詢、更新和刪除會出現(xiàn)錯(cuò)誤或訪問不到數(shù)據(jù)。如果要求數(shù)據(jù)分布到多個(gè)分區(qū)中，則需要進(jìn)行分區(qū)的SPLIT操作，會增加操作的復(fù)雜度，效率也會降低。
適用于包含大數(shù)據(jù)量的表轉(zhuǎn)到分區(qū)表中的一個(gè)分區(qū)的操作。應(yīng)盡量在閑時(shí)進(jìn)行操作。
舉例來說明
創(chuàng)建普通表并插入測試數(shù)據(jù)
LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
Table created.
LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS where CREATED<=to_date('201311','YYYYMM');
85984 rows created.
LHR@dlhr> COMMIT;
Commit complete.
LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
創(chuàng)建分區(qū)表
LHR@dlhr> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION T1 VALUES LESS THAN (TO_DATE('2013-11-1', 'YYYY-MM-DD')),
3 PARTITION T2 VALUES LESS THAN (MAXVALUE));
Table created.
交換數(shù)據(jù)
LHR@dlhr> ALTER TABLE T_NEW EXCHANGE PARTITION T1 WITH TABLE T;
Table altered.
改變表名
LHR@dlhr> rename t to t_old;
Table renamed.
LHR@dlhr> rename t_new to t;
Table renamed.
查詢數(shù)據(jù)
LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
交換分區(qū)的操作步驟如下：
1. 創(chuàng)建分區(qū)表，假設(shè)有2個(gè)分區(qū)，P1，P2.
2. 創(chuàng)建表A存放P1規(guī)則的數(shù)據(jù)。
3. 創(chuàng)建表B 存放P2規(guī)則的數(shù)據(jù)。
4. 用表A 和P1 分區(qū)交換。 把表A的數(shù)據(jù)放到到P1分區(qū)
5. 用表B 和p2 分區(qū)交換。 把表B的數(shù)據(jù)存放到P2分區(qū)。
This example creates the exchange table with the same structure as the partitions of the partitioned table p_emp.
SQL> CREATE TABLE p_emp
2 (sal NUMBER(7,2))
3 PARTITION BY RANGE(sal)
4 (partition emp_p1 VALUES LESS THAN (2000),
5 partition emp_p2 VALUES LESS THAN (4000));
Table created.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> CREATE TABLE exchtab1 as SELECT sal FROM emp WHERE sal<2000;
Table created.
SQL> CREATE TABLE exchtab2 as SELECT sal FROM emp WHERE sal BETWEEN 2000 AND 3999;
Table created.
SQL> alter table p_emp exchange partition emp_p1 with table exchtab1;
Table altered.
SQL> alter table p_emp exchange partition emp_p2 with table exchtab2;
Table altered.
這種分區(qū)的特點(diǎn)
優(yōu)點(diǎn)：保證數(shù)據(jù)的一致性，在大部分時(shí)間內(nèi)，表T都可以正常進(jìn)行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強(qiáng)的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進(jìn)行相應(yīng)的授權(quán)并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。
不足：實(shí)現(xiàn)上比上面兩種略顯復(fù)雜。
適用于各種情況。
在線重定義的大致操作流程如下：
（1）創(chuàng)建基礎(chǔ)表A，如果存在，就不需要操作。
（2）創(chuàng)建臨時(shí)的分區(qū)表B結(jié)構(gòu)。
（3）開始重定義，將基表A的數(shù)據(jù)導(dǎo)入臨時(shí)分區(qū)表B。
（4）結(jié)束重定義，完成后在DB的 Name Directory里，已經(jīng)將2個(gè)表進(jìn)行了交換。即此時(shí)基表A成了分區(qū)表，我們創(chuàng)建的臨時(shí)分區(qū)表B 成了普通表。 此時(shí)我們可以刪除我們創(chuàng)建的臨時(shí)表B。它已經(jīng)是普通表。
MOS上的文檔：
這個(gè)功能只在9.2.0.4以后的版本才有，在線重定義表具有以下功能：
（1）修改表的存儲參數(shù)；
（2）將表轉(zhuǎn)移到其他表空間；
（3）增加并行查詢選項(xiàng)；
（4）增加或刪除分區(qū)；
（5）重建表以減少碎片；
（6）將堆表改為索引組織表或相反的操作；
（7）增加或刪除一個(gè)列。
在線重定義的原理：物化視圖
在線重定義表的步驟：
1.選擇一種重定義方法：
存在兩種重定義方法，一種是基于主鍵、另一種是基于ROWID。ROWID的方式不能用于索引組織表，而且重定義后會存在隱藏列M_ROW$$。默認(rèn)采用主鍵的方式。
2.調(diào)用DBMS_REDEFINITION.CAN_REDEF_TABLE()過程，如果表不滿足重定義的條件，將會報(bào)錯(cuò)并給出原因。
3.在用一個(gè)方案中建立一個(gè)空的中間表，根據(jù)重定義后你期望得到的結(jié)構(gòu)建立中間表。比如：采用分區(qū)表，增加了COLUMN等。
4.調(diào)用DBMS_REDEFINITION.START_REDEF_TABLE()過程，并提供下列參數(shù)：被重定義的表的名稱、中間表的名稱、列的映射規(guī)則、重定義方法。
如果映射方法沒有提供，則認(rèn)為所有包括在中間表中的列用于表的重定義。如果給出了映射方法，則只考慮映射方法中給出的列。如果沒有給出重定義方法，則認(rèn)為使用主鍵方式。
5.在中間表上建立觸發(fā)器、索引和約束，并進(jìn)行相應(yīng)的授權(quán)。任何包含中間表的完整性約束應(yīng)將狀態(tài)置為disabled。
當(dāng)重定義完成時(shí)，中間表上建立的觸發(fā)器、索引、約束和授權(quán)將替換重定義表上的觸發(fā)器、索引、約束和授權(quán)。中間表上disabled的約束將在重定義表上enable。
6.（可選）如果在執(zhí)行DBMS_REDEFINITION.START_REDEF_TABLE()過程和執(zhí)行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程直接在重定義表上執(zhí)行了大量的DML操作，那么可以選擇執(zhí)行一次或多次的SYNC_INTERIM_TABLE()過程，以減少最后一步執(zhí)行FINISH_REDEF_TABLE()過程時(shí)的鎖定時(shí)間。
7.執(zhí)行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程完成表的重定義。這個(gè)過程中，原始表會被獨(dú)占模式鎖定一小段時(shí)間，具體時(shí)間和表的數(shù)據(jù)量有關(guān)。
執(zhí)行完FINISH_REDEF_TABLE()過程后，原始表重定義后具有了中間表的屬性、索引、約束、授權(quán)和觸發(fā)器。中間表上disabled的約束在原始表上處于enabled狀態(tài)。
8.（可選）可以重命名索引、觸發(fā)器和約束。對于采用了ROWID方式重定義的表，包括了一個(gè)隱含列M_ROW$$。推薦使用下列語句經(jīng)隱含列置為UNUSED狀態(tài)或刪除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);
ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;
使用在線重定義的一些限制條件：
（1） There must be enough space to hold two copies of the table.
（2） Primary key columns cannot be modified.
（3） Tables must have primary keys.
（4） Redefinition must be done within the same schema.
（5） New columns added cannot be made NOT NULL until after the redefinition operation.
（6） Tables cannot contain LONGs, BFILEs or User Defined Types.
（7） Clustered tables cannot be redefined.
（8） Tables in the SYS or SYSTEM schema cannot be redefined.
（9） Tables with materialized view logs or materialized views defined on them cannot be redefined.
（10） Horizontal sub setting of data cannot be performed during the redefinition.
在Oracle 10.2.0.4和11.1.0.7 版本下，在線重定義可能會遇到如下bug：
Bug 7007594 - ORA-600 [12261]
http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx
? 如果使用基于主鍵的方式，則原表后重定義后的表必須有相同的主鍵
? 如果使用基于ROWID的方式，則不能是索引組織表
? 如果原表上有物化視圖或者物化視圖日志，則不能在線重定義
? 物化視圖容器表或者高級隊(duì)列表不能在線重定義
? 索引組織表的溢出表不能在線重定義
? 擁有BFILE，LOGN列的表不能在線重定義
? Cluster中的表不能在線重定義
? sys和system下的表不能在線重定義
? 臨時(shí)表不能在線重定義
? 不支持水平數(shù)據(jù)子集
? 在列映射時(shí)只能使用有確定結(jié)果的表達(dá)式，如子查詢就不行
? 如果中間表有新增列，則不能有NOT NULL約束
? 原表和中間表之間不能有引用完整性
? 在線重定義無法采用nologging
創(chuàng)建普通表T_LHR_20160527_UNPART及其索引：
LHR@dlhr> CREATE TABLE T_LHR_20160527_UNPART (ID NUMBER PRIMARY KEY, TIME DATE);
Table created.
LHR@dlhr> INSERT INTO T_LHR_20160527_UNPART SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
87112 rows created.
LHR@dlhr> commit;
Commit complete.
LHR@dlhr> CREATE INDEX create_date_indx ON T_LHR_20160527_UNPART(TIME);
Index created.
LHR@dlhr> exec dbms_stats.gather_table_stats(user, 'T_LHR_20160527_UNPART', cascade => true);
PL/SQL procedure successfully completed.
LHR@dlhr>
LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from T_LHR_20160527_UNPART t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1128
創(chuàng)建臨時(shí)分區(qū)表T_LHR_20160527_PART,注意這里的time列我換成了CREATED_DATE
LHR@dlhr> CREATE TABLE T_LHR_20160527_PART (ID NUMBER PRIMARY KEY, CREATED_DATE DATE)
2 PARTITION BY RANGE (created_date)
3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
4 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
5 PARTITION T3 VALUES LESS THAN (MAXVALUE));
Table created.
然后執(zhí)行DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T_LHR_20160527_UNPART', DBMS_REDEFINITION.CONS_USE_PK);檢查是否可以執(zhí)行在線重定義，若返回錯(cuò)誤的話說明不能執(zhí)行，LHR@dlhr> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK);
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "LHR"."T" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 143
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1635
ORA-06512: at line 1
LHR@dlhr> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T_LHR_20160527_UNPART', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
沒有錯(cuò)誤，說明我們需要轉(zhuǎn)換的表可以執(zhí)行在線重定義，下邊開始執(zhí)行在線重定義，這個(gè)過程可能要等一會，根據(jù)表的大小不同而不同：
LHR@dlhr> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART',DBMS_REDEFINITION.CONS_USE_PK);
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART',DBMS_REDEFINITION.CONS_USE_PK); END;
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1498
ORA-06512: at line 1
LHR@dlhr> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART', 'ID ID, TIME created_date ', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
LHR@dlhr>
LHR@dlhr> select count(1) from T_LHR_20160527_UNPART;
COUNT(1)
----------
87112
LHR@dlhr> select count(1) from T_LHR_20160527_PART;
COUNT(1)
----------
87112
LHR@dlhr> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART');
這一步操作結(jié)束后，數(shù)據(jù)就已經(jīng)同步到這個(gè)臨時(shí)的分區(qū)表里來了。需要注意的是如果分區(qū)表和原表列名相同，則可以不用加列的轉(zhuǎn)換，如果不同的話需要加上轉(zhuǎn)換，即重新指定映射關(guān)系。另外EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART');是同步新表作用是可選的。如果在執(zhí)行DBMS_REDEFINITION.START_REDEF_TABLE()過程和執(zhí)行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程直接在重定義表上執(zhí)行了大量的DML操作，那么可以選擇執(zhí)行一次或多次的SYNC_INTERIM_TABLE()過程，以減少最后一步執(zhí)行FINISH_REDEF_TABLE()過程時(shí)的鎖定時(shí)間。
下邊我們在新表上創(chuàng)建索引，在線重定義只重定義數(shù)據(jù)，索引還需要單獨(dú)建立。
LHR@dlhr> CREATE INDEX create_date_indx2 ON T_LHR_20160527_PART(created_date);
Index created.
LHR@dlhr> exec dbms_stats.gather_table_stats(user, 'T_LHR_20160527_PART', cascade => true);
PL/SQL procedure successfully completed.
LHR@dlhr>
接下來就是結(jié)束重定義了：
LHR@dlhr> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T_LHR_20160527_UNPART', 'T_LHR_20160527_PART');
PL/SQL procedure successfully completed.
LHR@dlhr>
LHR@dlhr> select D.TABLE_NAME, partitioned from user_tables D where table_name like '%T_LHR_20160527%' ;
TABLE_NAME PAR
------------------------------ ---
T_LHR_20160527_PART NO
T_LHR_20160527_UNPART YES
LHR@dlhr> SELECT D.TABLE_NAME, partition_name
2 FROM user_tab_partitions D
3 WHERE table_name = 'T_LHR_20160527_UNPART';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_LHR_20160527_UNPART T1
T_LHR_20160527_UNPART T2
T_LHR_20160527_UNPART T3
LHR@dlhr>
結(jié)束重定義DBMS_REDEFINITION.FINISH_REDEF_TABLE的意義：
基表 T_LHR_20160527_UNPART和臨時(shí)分區(qū)表T_LHR_20160527_PART進(jìn)行了交換。 此時(shí)臨時(shí)分區(qū)表T_LHR_20160527_PART成了普通表，我們的基表T_LHR_20160527_UNPART成了分區(qū)表。
我們在重定義的時(shí)候，基表T_LHR_20160527_UNPART是可以進(jìn)行DML操作的。 只有在2個(gè)表進(jìn)行切換的時(shí)候會有短暫的鎖表。
在線重定義能保證數(shù)據(jù)的一致性，在大部分時(shí)間內(nèi)，表都可以正常進(jìn)行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強(qiáng)的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進(jìn)行相應(yīng)的授權(quán)并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。
還有最后一個(gè)步驟，刪除臨時(shí)表并索引重命名，驗(yàn)證數(shù)據(jù)即可：
LHR@dlhr> drop table T_LHR_20160527_PART;
Table dropped.
LHR@dlhr> alter index create_date_indx2 rename to create_date_indx;
Index altered.
LHR@dlhr> select to_char(t.created_date, 'YYYYMM'), COUNT(1)
2 from T_LHR_20160527_UNPART t
3 group by to_char(t.created_date, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1128
--------------------------------------------------------------------------------------------------------------
感謝各位的閱讀！關(guān)于“數(shù)據(jù)庫中怎么將一個(gè)普通表轉(zhuǎn)換為分區(qū)表”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼
