​[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，會出現什麼情況，沒有測試。至少一些維護會簡單許多，我估計有點像
--//線上重定義使用物化檢視的方式。