PPT普通显示,怎么增加备注文档(如何在ppt中增加备注提示)
837
2022-05-30
**转载自 MOS 文档1070693.6 :How to Partition a Non-partitioned / Regular / Normal Table (Doc ID 1070693.6) **
Partitioning a regular/non-partitioned table can be done in five ways:
A. Export/import method B. Insert with a subquery method C. Partition Exchange method D. DBMS_REDEFINITION E. MODIFY clause added to the ALTER TABLE SQL Statement. (From 12.2)
A. Export/import method
This method involves exporting the non partitioned table, creating a partitioned table, and then importing data into the new partitioned table.
1) Export your table:
$ exp
2) Drop the table:
SQL> drop table TEST_TABLE1;
3) Recreate the table with partitions:
SQL> create table TEST_TABLE1 (qty number(3), name varchar2(15)) partition by range (qty)(partition p1 values less than (501),
partition p2 values less than (maxvalue));
4) Import the table with ignore=y:
$ imp
The ignore=y causes the import to skip the table creation and continues to load all rows.
With Data Pump export/import (expdp/impdp) you can use the table_exists_action option of impdp e.g. table_exists_action = APPEND or table_exists_action = REPLACE.
Also review Note 552424.1 Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Data ?
B. Insert with a subquery method
1) Create a partitioned table:
SQL> create table partbl (qty number(3), name varchar2(15)) partition by range (qty) (partition p1 values less than (501),partition p2 values less than (maxvalue));
2) Insert into the partitioned table with a subquery from the non-partitioned table:
SQL> insert into partbl (qty, name) select * from origtbl;
3) If you want the partitioned table to have the same name as the original table, then drop the original table and rename the new table:
SQL> drop table origtbl;
SQL> alter table partbl rename to origtbl;
You may improve the insert performance with direct path insert and utilize parallelism. Examples below show how this can be done and how it can be recognized in the execution plan.
Conventional insert
SQL> insert into partbl (qty, name) select * from origtbl;
--------------------------------------------
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
| 1 | LOAD TABLE CONVENTIONAL | |
| 2 | TABLE ACCESS FULL | ORIGTBL |
Direct load insert
SQL> insert /*+APPEND*/ into partbl (qty, name) select * from origtbl;
--------------------------------------
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
| 1 | LOAD AS SELECT | |
| 2 | TABLE ACCESS FULL| ORIGTBL |
Direct load insert with parallel query part
SQL> insert /*+APPEND PARALLEL*/ into partbl (qty, name) select * from origtbl;
------------------------------------------
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
| 1 | LOAD AS SELECT | |
| 2 | PX COORDINATOR | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 |
| 4 | PX BLOCK ITERATOR | |
|* 5 | TABLE ACCESS FULL| ORIGTBL |
Note LOAD AS SELECT is above PX COORDINATOR in the execution plan.
Direct load insert with parallel query and insert parts
SQL>alter session enable parallel dml;
SQL> insert /*+APPEND PARALLEL*/ into partbl (qty, name) select * from origtbl;
------------------------------------------
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 |
| 3 | LOAD AS SELECT | |
| 4 | PX BLOCK ITERATOR | |
|* 5 | TABLE ACCESS FULL| ORIGTBL |
Note LOAD AS SELECT is below PX COORDINATOR in the execution plan.
An alternative to insert … select is to use create table as select: creating the partitioned table and loading data into it in one go.
The execution plan shows direct path load with both dml and select parts parallel.
SQL>alter session enable parallel dml;
SQL> create table partbl (qty, name) partition by range (qty) (partition p1 values less than (501),partition p2 values less than (maxvalue))
2 as select /*+PARALLEL*/ * from origtbl;
-------------------------------------------
| Id | Operation | Name |
| 0 | CREATE TABLE STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 |
| 3 | LOAD AS SELECT | |
| 4 | PX BLOCK ITERATOR | |
|* 5 | TABLE ACCESS FULL | ORIGTBL |
C. Partition Exchange method
ALTER TABLE EXCHANGE PARTITION can be used to convert a partition (or subpartition) into a non-partitioned table and a non-partitioned table into a partition (or subpartition) of a partitioned table by exchanging their data and index segments. Unless update indexes close the ALTER TABLE … EXCHANGE PARTITION command is a dictionary operation with no data movement. Further information about this method can be found in the oracle documentation (e.g. 11.2) and in Note 198120.1.
The steps involved briefly are the following:
1) Create the partitioned table with the required partitions
2) Have the exchange table with the same structure as the partitions of the partitioned table, and the exchange table having the content that you want to exchange with a partition of the partitioned table
3) Alter table exchange partition partition_name with table exchange table
Note that during the exchange all rows of the exchange table must qualify for the partition to be exchanged, otherwise the following error is thrown ORA-14099: all rows in table do not qualify for specified partition.
This is because by default the exchange is done with validation.
Example (based on SCOTT sample schema)
---------
This example creates the exchange table with the same structure as the partitions of the partitioned table p_test.
SQL> CREATE TABLE p_test
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_test exchange partition emp_p1 with table exchtab1;
Table altered.
SQL> alter table p_test exchange partition emp_p2 with table exchtab2;
Table altered.
D. DBMS_REDEFINITION
For details see
Note 472449.1 How To Partition Existing Table Using DBMS_Redefinition
Note 1481558.1 DBMS_REDEFINITION: Case Study for a Large Non-Partition Table to a Partition Table with Online Transactions occuring
Note 177407.1 How to Re-Organize a Table Online
E. MODIFY clause added to the ALTER TABLE SQL Statement. (From 12.2)
From 12.2, Alter Table
For More details, Please see https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/evolve-nopartition-table.html#GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5
Oracle
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。