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 |

建立複合分割槽表

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/，如需轉載，請註明出處，否則將追究法律責任。

Oracle 分割槽(partition)技術

相關文章