​[20200423]12c删除不需要的记录.txt

[20200423]12c删除不需要的记录.txt --//12CR2支持改动表仅仅包括需要的特定记录，删除不需要的记录。语法如下： --// alter table table_name move including rows where <query_where >. 1.环境： SCOTT@book> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试： SCOTT@test01p> create table tx as select * from all_objects ; Table created. SCOTT@test01p> create unique index i_tx_object_id on tx(object_id); Index created. SCOTT@test01p> select count(*) from tx ;   COUNT(*) ----------      68135 SCOTT@test01p> select count(*) from tx where owner='SYS';   COUNT(*) ----------      47452 --//仅仅需要保留owner='SYS'记录，理论删除其它redo消耗很大，以前常规方法是通过ctas或者物化视图方式建立新表替换旧表。 --//现在可以执行如下： SCOTT@test01p> alter table tx move online including rows where owner='SYS' UPDATE INDEXES; Table altered. SCOTT@test01p> select count(*) from tx ;   COUNT(*) ----------      47452 SCOTT@test01p> select object_name from tx where object_id=2; OBJECT_NAME -------------------- C_OBJ# SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  8tw1qfk3y5ru2, child number 0 ------------------------------------- select object_name from tx where object_id=2 Plan hash value: 3474389346 ----------------------------------------------------------------------------------------------- | Id  | Operation                   | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ----------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                |        |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| TX             |      1 |    41 |     2   (0)| 00:00:01 | |*  2 |   INDEX UNIQUE SCAN         | I_TX_OBJECT_ID |      1 |       |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / TX@SEL$1    2 - SEL$1 / TX@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("OBJECT_ID"=2)     --//索引依旧有效。 This only moves the rows matching the where clause. Provided you want to remove a large chunk of data, this can be much faster than delete. And it has an online clause. So unlike the CTAS methods, you can do this while the application is still running. --//这种方式更加简单，不知道如果在原表上有DML，会出现什么情况，没有测试。至少一些维护会简单许多，我估计有点像 --//在线重定义使用物化视图的方式。