Oracle 分割槽(partition)技術

Oracle 分割槽(partition)表
分割槽表的好處:
一：提高資料的可用性，分割槽表邏輯上是一個表，實際各分割槽的資料是獨立存放的，一個分割槽可以離線的
        同時，其他分割槽可以正常操作.
二：減輕管理負擔，對一個20g的物件多備份，移動，收縮等操作，顯然要比在20個1g的物件上執行同
        樣的操作要更有挑戰性，分割槽採用分而治之的方法，而且分割槽實際上獨立存放，從而可以用這些小對
        象上的操作來代替大表上操作
三：提高查詢效率，在olap系統中，存在諸多非常大的物件，可能存放5年，10年的歷史資料，決策報表
        需要資料量也非常多，分割槽技術在此環境下，充分利用分割槽消除，可以大幅度的提高查詢效率，但對
       於oltp系統，應用的不同將會導致幾乎感受不到這種好處。

各種型別分割槽使用注意點:

範圍(range)分割槽:
一::對於分割槽表,如果where條件種沒有分割槽列,那麼oracle會掃描所有的分割槽,然後做PARTITION RANGE
      ALL 操作,這樣成本將比未分割槽的全表掃描稍微高點,因為需要合併各個分割槽.
二:範圍分割槽可以用values less than (maxvalue)增加一個預設分割槽,maxvalue 常量表示該分割槽用來存放所有其
     他分割槽無法存放的記錄,
三:範圍分割槽可以對各種謂詞做分割槽消除,包括=,>,等比hash,和list分割槽要靈活

雜湊(hash)分割槽
一:oracle根據分割槽列的hash函式計算值, hash分割槽數來自動決定某一條記錄放在哪一個分割槽(你無法決定).
二:分割槽數應為2的一個冪,如2,4,8,16……如若不然,記錄的雜湊將會不均勻.
三:分割槽列應該有很好的選擇性,如果在10000條記錄中,分割槽列只有5個不同的值,那麼很可能所有的記錄都集中在
   少數幾個分割槽中.無法把10000條記錄均勻的分散到這5個分割槽中.
四:hash分割槽對於非嚴格=的謂詞,很難做分割槽消除,沒有range分割槽靈活.
五:如果hash分割槽的分割槽數有增加或減少,資料會在所有分割槽中重新再分佈

列值(list)分割槽
一:對於既無法使用範圍分割槽,同時若列的選擇不很好,又無法使用hash分割槽的時候,可以採用list分割槽,如區域
     代號,部門代號等欄位.
二:分割槽對於非嚴格=的謂詞,很難做分割槽消除,沒有range分割槽靈活.
三:oracle9i 以後才支援list分割槽.

複合分割槽
一:主分割槽必須是範圍分割槽,子分割槽可以是hash分割槽或者列表分割槽
二:如果where條件中有主分割槽的分割槽列,則支援範圍分割槽消除,如果where條件中再加上子分割槽的分割槽列,則
     會在前面分割槽消除結果集中再次做分割槽消除,如果where條件中只有子分割槽的分割槽列,則會掃描每一個主
    分割槽.在每一個主分割槽中做子分割槽列的分割槽消除.這種情況下,成本可能會比未分割槽的成本還要高一些.

下面是一些試驗例子:

建立範圍分割槽表
SQL> Create Table t(owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
  2                 last_ddl_time, timestamp, status, temporary, generated, secondary)
  3    Partition By Range(object_id)
  4    (Partition p_3000 Values Less Than(3000) Tablespace users,
  5     Partition p_6000 Values Less than(6000) Tablespace users,
  6     Partition p_9000 Values Less Than(9000) Tablespace users,
  7     Partition p_12000 Values Less Than(12000) Tablespace users,
  8     Partition p_15000 Values Less Than(15000) Tablespace users,
  9     Partition p_18000 Values Less Than(18000) Tablespace users,
 10     Partition p_21000 Values Less Than(21000) Tablespace users,
 11     Partition p_24000 Values Less Than(24000) Tablespace users,
 12     Partition p_27000 Values Less Than(27000) Tablespace users,
 13     Partition p_others Values Less Than(Maxvalue) Tablespace users
 14     )
 15  As
 16  Select owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
 17                 last_ddl_time, timestamp, status, temporary, generated, secondary
18 From dba_objects;
再建立一個非分割槽表,後面用來做對比
SQL> Create Table t1(owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
  2                 last_ddl_time, timestamp, status, temporary, generated, secondary)
  3  As
  4  Select owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
  5                 last_ddl_time, timestamp, status, temporary, generated, secondary
6 From dba_objects;

SQL>  explain plan for select count(*) from t where object_id>4000 and object_id<5000;
已解釋。
SQL>  select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
| Id  | Operation                |  Name       | Rows  | Bytes | Cost  | Pstart|   Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |     1 |     3 |     6 |       |       |
|   1 |  SORT AGGREGATE          |             |     1 |     3 |       |       |       |
|*  2 |   TABLE ACCESS FULL      | T           |   985 |  2955 |     6 |     2 |     2 |

pstart,pstop 表示開始分割槽和結束分割槽,本例中只對第二個分割槽做全表掃描

SQL> explain plan for select count(*) from t1  where object_id>4000 and object_id<5000;
已解釋。
SQL>  select * from table(dbms_xplan.display);
--------------------------------------------------------------------
| Id  | Operation                  |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |     4 |    41 |
|   1 |  SORT AGGREGATE            |             |     1 |     4 |       |
|*  2 |   TABLE ACCESS FULL        | T1          |   962 |  3848 |    41 |
非分割槽表無法做分割槽消除,對整個表做全表掃描,成本比分割槽表要高很多

建立hash 分割槽表
oracle根據hash分割槽數,以及分割槽列的hash函式計算值,來自動決定某一條記錄放在拿一個分割槽(你無法決定),
這樣可以很均勻的把資料分散到每一個分割槽中;
 SQL> Create Table t(owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
  2                 last_ddl_time, timestamp, status, temporary, generated, secondary)
  3    Partition By Hash(object_id)
  4    (Partition p_1 Tablespace users,
  5     Partition p_2 Tablespace users,
  6     Partition p_3 Tablespace users,
  7     Partition p_4 Tablespace users,
  8     Partition p_5 Tablespace users,
  9     Partition p_6 Tablespace users,
 10     Partition p_7 Tablespace users,
 11     Partition p_8 Tablespace users
 12     )
 13  As
 14  Select owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
 15                 last_ddl_time, timestamp, status, temporary, generated, secondary
16 From dba_objects;

SQL> explain plan for select * from t where object_id=1000;
已解釋。
SQL>  select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
| Id  | Operation                |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |    46 |  8142 |     7 |     |       |
|*  1 |  TABLE ACCESS FULL       | T           |    46 |  8142 |     7 |   1 |     1 |

對於非=謂詞,hash分割槽很難做分割槽消除
SQL> explain plan for select * from t where object_id<=1000 and object_id>=999;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
| Id  | Operation                |  Name       | Rows  | Bytes | Cost  | Pstart  | Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    93 | 16461 |     46 |       |       |
|   1 |  PARTITION HASH ALL  |             |       |       |        |     1 |     8 |
|*  2 |   TABLE ACCESS FULL  | T           |     93| 16461 |     46 |     1 |     8 |
上面語句掃描了所有8個分割槽.

建立list 分割槽表
SQL> Create Table t(owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
  2                 last_ddl_time, timestamp, status, temporary, generated, secondary)
  3    Partition By list(OWNER)
  4    (Partition p_1  Values ('HR','SCOTT') Tablespace users,
  5     Partition p_2 Values ('MDSYS') Tablespace users,
  6     Partition p_3 Values ('SH','SYS') Tablespace users,
  7     Partition p_4 Values ('OE','OLAPSYS','SYSTEM') Tablespace users,
  8     Partition p_5 Values ('ODM','ODM_MTR') Tablespace users,
  9     Partition p_6 Values ('QS','QS_CS','QS_ES','QS_OS','QS_WS','WKSYS','WMSYS') Tablespace users,
 10     Partition p_7 Values ('PM','PUBLIC') Tablespace users,
 11     Partition p_8 Values (DEFAULT) Tablespace users
 12     )
 13  As
 14  Select owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
 15                 last_ddl_time, timestamp, status, temporary, generated, secondary
16 From dba_objects;

SQL> explain plan for select * from t where WNER='SYS';
已解釋。
SQL>  select * from table(dbms_xplan.display);
| Id  | Operation                 |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   162 | 28674 |    21 |      |       |
|*  1 |  TABLE ACCESS FULL   | T           |   162 | 28674 |    21 |    3 |     3 |

再來看看,雖然sys和sh在同一個分割槽,但對於 in 的謂詞,如果有多個值,oracle無法很好的去做分割槽消除
SQL> explain plan for select * from t where OWNER IN ('SYS','SH');
explain plan for select * from t where OWNER IN ('SYS') OR OWNER IN ('SH');
explain plan for select * from t where WNER ='SYS' OR  WNER ='SH';
 PARTITION LIST INLIST|             |       |       |       |KEY(I) |KEY(I) |
 TABLE ACCESS FULL    | T           |   368 | 65136 |    45 |KEY(I) |KEY(I) |

建立複合分割槽表

SQL> Create Table t(owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
  2                 last_ddl_time, timestamp, status, temporary, generated, secondary)
  3    Partition By Range(object_id)
  4    Subpartition By list(owner)
  5    (Partition p_6000 Values Less Than(6000) Tablespace users
  6     (subpartition p_6_1 values   ('HR','SCOTT', 'SH','SYS'),
  7  subPartition p_6_2 values  ('OE','OLAPSYS','SYSTEM'),
  8  subPartition p_6_3 values  (default)
  9  ),
 10     Partition p_12000 Values Less than(12000) Tablespace users
 11  (subpartition p_12_1 values   ('HR','SCOTT', 'SH','SYS'),
 12  subPartition p_12_2 values  ('OE','OLAPSYS','SYSTEM'),
 13  subPartition p_12_3 values  (default)
 14  ),
 15     Partition p_18000 Values Less Than(18000) Tablespace users
 16  (subpartition p_18_1 values   ('HR','SCOTT', 'SH','SYS'),
 17  subPartition p_18_2 values  ('OE','OLAPSYS','SYSTEM'),
 18  subPartition p_18_3 values  (default)
 19  ),
 20     Partition p_24000 Values Less Than(24000) Tablespace users
 21  (subpartition p_24_1 values   ('HR','SCOTT', 'SH','SYS'),
 22  subPartition p_24_2 values  ('OE','OLAPSYS','SYSTEM'),
 23  subPartition p_24_3 values  (default)
 24  ),
 25     Partition p_others Values Less Than(Maxvalue) Tablespace users
 26  (subpartition p_oth_1 values   ('HR','SCOTT', 'SH','SYS'),
 27  subPartition p_oth_2 values  ('OE','OLAPSYS','SYSTEM'),
 28  subPartition p_oth_3 values  (default)
 29  )
 30     )
 31  As
 32  Select owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
 33                 last_ddl_time, timestamp, status, temporary, generated, secondary
 34    From dba_objects
 35  ;
Table created
SQL> explain plan for select * from t where OBJECT_ID>=5000 AND OBJECT_ID<16000;
已解釋。
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost  | Pstart  | Pstop |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             | 10561 |   917K|    29 |       |       |
|   1 |  PARTITION RANGE ITERATOR|                  |       |       |       |     1 |     3 |
|   2 |   PARTITION LIST ALL          |             |       |       |       |     1 |     3 |
|*  3 |    TABLE ACCESS FULL          | T           | 10561 |   917K|    29 |     1 |     9 |

首先做範圍分割槽消除,oracle確定要掃描5000-16000之間的三個分割槽,對於每個範圍分割槽下面的子分割槽,全部掃描,
然後做PARTITION LIST ALL 合併各個範圍分割槽的子分割槽.如果where條件中有自分割槽列,oracle也會對自分割槽做分
區消除,如下面,pstart 和pend 為key
SQL> explain plan for select * from t where OBJECT_ID>=5000 AND OBJECT_ID<16000 and wner='SH';

已解釋。

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------
| Id  | Operation                                   |  Name       | Rows  | Bytes | Cost  | Pstart  | Pstop |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   556 | 49484 |    29 |       |       |
|   1 |  PARTITION RANGE ITERATOR     |             |       |       |       |     1 |     3 |
|*  2 |   TABLE ACCESS FULL           | T           |   556 | 49484 |    29 |   KEY |   KEY |

但如果where條件中只有子分割槽列,那麼成本會比未分割槽表的掃描還要高,因為oracle需要對各個分割槽及子分割槽做合併動作,如下
SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
| Id  | Operation                 |  Name       | Rows  | Bytes | Cost  | Pstart  | Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  1564 |   135K|    47 |       |       |
|   1 |  PARTITION RANGE ALL |             |       |       |       |     1 |     5 |
|*  2 |   TABLE ACCESS FULL  | T           |  1564 |   135K|    47 |   KEY |   KEY |

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10159839/viewspace-254449/，如需轉載，請註明出處，否則將追究法律責任。