高效的partition（使用分割槽條件）

Partition技術中：

高效的SQL應儘量使用分割槽條件

range分割槽表

create table range_tab(id int,col2 int,col3 int)

partition by range(id)

(

partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than (3000),

partition p4 values less than (4000),

partition p5 values less than (5000),

partition p6 values less than (6000),

partition p7 values less than (7000),

partition p8 values less than (8000),

partition p9 values less than (9000),

partition p_max values less than (maxvalue)

);

插入資料

insert into range_tab select rownum,rownum+1,rownum+2 from dual connect by rownum<=10000;

沒有索引的情況下

SQL> select * from range_tab where col2=800; <=未使用分割槽條件

ID COL2 COL3

---------- ---------- ----------

799 800 801

Execution Plan

----------------------------------------------------------

Plan hash value: 2142701667

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 39 | 15 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE ALL| | 1 | 39 | 15 (0)| 00:00:01 | 1 | 10 |

|* 2 | TABLE ACCESS FULL | RANGE_TAB | 1 | 39 | 15 (0)| 00:00:01 | 1 | 10 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("COL2"=800)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

71 consistent gets

0 physical reads

0 redo size

540 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select * from range_tab where col2=800 and id=799; <=使用了分割槽條件

ID COL2 COL3

---------- ---------- ----------

799 800 801

Execution Plan

----------------------------------------------------------

Plan hash value: 2649581176

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE| | 1 | 39 | 3 (0)| 00:00:01 | 1 | 1 |

|* 2 | TABLE ACCESS FULL | RANGE_TAB | 1 | 39 | 3 (0)| 00:00:01 | 1 | 1 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("COL2"=800 AND "ID"=799)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

540 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

=>沒有索引的情況下，正確的使用分割槽條件減少了訪問資料的範圍，從原來需要訪問所有的分割槽，到正確使用分割槽條件後的一個分割槽

local索引

SQL> create index idx_col2_local on range_tab(col2) local;

Index created.

SQL> select * from range_tab where col2=800; <=未使用分割槽條件

ID COL2 COL3

---------- ---------- ----------

799 800 801

Execution Plan

----------------------------------------------------------

Plan hash value: 3282018838

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 39 | 12 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE ALL | | 1 | 39 | 12 (0)| 00:00:01 | 1 | 10 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| RANGE_TAB | 1 | 39 | 12 (0)| 00:00:01 | 1 | 10 |

|* 3 | INDEX RANGE SCAN | IDX_COL2_LOCAL | 1 | | 11 (0)| 00:00:01 | 1 | 10 |

---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("COL2"=800)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

22 consistent gets

0 physical reads

0 redo size

540 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select * from range_tab where col2=800 and id=799; <=使用了分割槽條件

ID COL2 COL3

---------- ---------- ----------

799 800 801

Execution Plan

----------------------------------------------------------

Plan hash value: 1302426126

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE | | 1 | 39 | 2 (0)| 00:00:01 | 1 | 1 |

|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| RANGE_TAB | 1 | 39 | 2 (0)| 00:00:01 | 1 | 1 |

|* 3 | INDEX RANGE SCAN | IDX_COL2_LOCAL | 1 | | 1 (0)| 00:00:01 | 1 | 1 |

---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"=799)

3 - access("COL2"=800)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

540 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

=>local索引也是有多少個區就有多少個索引段，所以正確的使用了分割槽條件就將需要訪問的索引塊減少到了最低，索引使用分割槽條件也可以提升效率

Global索引

SQL> select * from range_tab where col2=800;

ID COL2 COL3

---------- ---------- ----------

799 800 801

Execution Plan

----------------------------------------------------------

Plan hash value: 312410708

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 | | |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| RANGE_TAB | 1 | 39 | 2 (0)| 00:00:01 | ROWID | ROWID |

|* 2 | INDEX RANGE SCAN | RANGE_COL2_GLOBAL | 1 | | 1 (0)| 00:00:01 | | |

------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("COL2"=800)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

544 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select * from range_tab where col2=800 and id=799;

ID COL2 COL3

---------- ---------- ----------

799 800 801

Execution Plan

----------------------------------------------------------

Plan hash value: 227308907

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 | | |

|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| RANGE_TAB | 1 | 39 | 2 (0)| 00:00:01 | 1 | 1 |

|* 2 | INDEX RANGE SCAN | RANGE_COL2_GLOBAL | 1 | | 1 (0)| 00:00:01 | | |

------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=799)

2 - access("COL2"=800)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

540 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

=> global索引是把分割槽表看成一個段進行建立索引的，索引分割槽條件對global索引影響不大，但是如果你使用分割槽條件，經過CBO的計算還是會優先分割槽條件使用權的

總結：

使用partition技術時，高效的SQL請使用分割槽條件；

反問，如果不使用分割槽條件你為什麼要使用partition技術呢？

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

高效的partition（使用分割槽條件）

相關文章