[20190805]Oracle 12c New Feature – Online Partitioning.txt

[20190805]Oracle 12c New Feature – Online Partitioning.txt

--//oracle 12c 支援建立分割槽表新特性，就是線上修改普通表為分割槽表。

1.環境：
SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0

SCOTT@test01p> create table tx as select rownum id ,'test' name from dual connect by level<=2e4;
Table created.

SCOTT@test01p> create unique index i_t_id on tx (id);
Index created.

SCOTT@test01p> create index i_t_name on tx (name);
Index created.

SCOTT@test01p> create index i_t_id_name on tx (id,name);
Index created.

--//分析略。

2.測試：
--//session 1,插入不提交。
SCOTT@test01p> insert into tx values (2e4+1,'aaaa');
1 row created.

--//session 2,執行如下：
alter table tx modify partition by range (id)
(PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (20000),
PARTITION px VALUES LESS THAN (MAXVALUE)) ONLINE ;
--//掛起!!

--//session 1:
SCOTT@test01p> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
       251      41873 7720:4852                DEDICATED 6316                      27          4 alter system kill session '251,41873' immediate;

SCOTT@test01p> @ viewlock
   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
    24      45764 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TM DML(TM)      Row-X (SX) None       27765      0          SCOTT  TABLE      TX                   No    000007FF091426B8
    24      45764 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TM DML(TM)      Exclusive  None       27775      0          SCOTT  TABLE      SYS_RMTAB$$_H27765   No    000007FF091426B8
    24      45764 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TX Transaction  None       Share      589839     1387                                              No    000007FF091426B8
    24      45764 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TM DML(TM)      Exclusive  None       27773      0          SCOTT  TABLE      SYS_JOURNAL_27765    No    000007FF091426B8
    24      45764 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TX Transaction  Exclusive  None       262173     1470                                              No    000007FF091426B8
   251      41873 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TX Transaction  Exclusive  None       589839     1387                                              Yes
   251      41873 SCOTT      ZWS\Admini WORKGROUP\ SQL*Plus     TM DML(TM)      Row-X (SX) None       27765      0          SCOTT  TABLE      TX                   No
7 rows selected.       
--//因為session 有事務沒有提交。

SCOTT@test01p> commit ;
Commit complete.

--//session 2,很快建立完成。

SCOTT@test01p> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TX';
PARTITION_NAME
--------------------
P1
P2
PX

SCOTT@test01p> select * from tx where id=1;
        ID NAME
---------- --------------------
         1 test

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6sj645u5hrrbv, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 419152296

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |        |       |     2 (100)|          |       |       |
|   1 |  PARTITION RANGE SINGLE|             |      1 |    10 |     2   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN     | I_T_ID_NAME |      1 |    10 |     2   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / TX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=1)
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--//可以發現索引重新建立，變成了local分割槽索引。
--//你可以發現i_t_id_name也是local分割槽索引。i_t_name是global索引，大家可以自行驗證結果不再貼出。
--//也就是如果建立分割槽表，其原來索引包含分割槽的鍵值並且在前面，建立的索引是字首分割槽索引。而原來索引沒有包含分割槽的鍵值，
--//建立的就是global索引。
--//我的理解這個相當於以前的線上重定義，只不過oracle封裝為1個命令實現了整個過程罷了。