分割槽表與堆表執行計劃的不同

Execution Plan

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

Plan hash value: 84294021

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

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

| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE| | 4 | 52 | 3 (0)| 00:00:01 | 2 | 2 |

|* 2 | TABLE ACCESS FULL | DOU_RANG_TAB | 4 | 52 | 3 (0)| 00:00:01 | 2 | 2 |

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

紅色部分是分割槽表特有的執行計劃部分。表示讀取分割槽的區域（開始與結束）

實驗如下：

SQL> create table dou_rang_tab(x int)

2 partition by range(x)

3 (

4 partition p1_10 values less than(10),

5 partition p2_20 values less than(20),

6 partition p3_30 values less than(30),

7 partition p4_max values less than(maxvalue)

8 );

SQL> create table dou_tab(x int);

Table created.

SQL> insert into dou_rang_tab select rownum from dual connect by rownum<=40;

40 rows created.

Execution Plan

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

Plan hash value: 1731520519

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

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

| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 2 | COUNT | | | | |

|* 3 | CONNECT BY WITHOUT FILTERING| | | | |

| 4 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - filter(ROWNUM<=40)

Statistics

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

26 recursive calls

113 db block gets

20 consistent gets

0 physical reads

6712 redo size

676 bytes sent via SQL*Net to client

639 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

40 rows processed

SQL> insert into dou_tab select rownum from dual connect by rownum<=40;

40 rows created.

Execution Plan

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

Plan hash value: 1731520519

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

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

| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 2 | COUNT | | | | |

|* 3 | CONNECT BY WITHOUT FILTERING| | | | |

| 4 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - filter(ROWNUM<=40)

Statistics

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

159 recursive calls

59 db block gets

33 consistent gets

0 physical reads

6640 redo size

676 bytes sent via SQL*Net to client

634 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

SQL> select * from dou_rang_tab where x<19 and x>14;

X

----------

15

16

17

18

Execution Plan

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

Plan hash value: 84294021

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

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

| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE| | 4 | 52 | 3 (0)| 00:00:01 | 2 | 2 |

|* 2 | TABLE ACCESS FULL | DOU_RANG_TAB | 4 | 52 | 3 (0)| 00:00:01 | 2 | 2 |

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

Predicate Information (identified by operation id):

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

2 - filter("X"<19 AND "X">14)

Note

-----

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

Statistics

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

4 recursive calls

0 db block gets

16 consistent gets

0 physical reads

0 redo size

469 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)

4 rows processed

SQL> select * from dou_tab where x<19 and x>14;

X

----------

15

16

17

18

Execution Plan

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

Plan hash value: 3810283012

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

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

| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| DOU_TAB | 4 | 52 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("X"<19 AND "X">14)

Note

-----

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

Statistics

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

5 recursive calls

0 db block gets

16 consistent gets

0 physical reads

0 redo size

469 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)

4 rows processed

附表：

實驗指令碼：

分割槽表建立

create table dou_rang_tab(x int)

partition by range(x)

(

partition p1_10 values less than(10),

partition p2_20 values less than(20),

partition p3_30 values less than(30),

partition p4_max values less than(maxvalue)

);

堆表建立

create table dou_tab(x int);

分別向分割槽表和堆表插入資料

insert into dou_rang_tab select rownum from dual connect by rownum<=40;

insert into dou_tab select rownum from dual connect by rownum<=40;

對比查詢效能

select * from dou_rang_tab where x<19 and x>14;

select * from dou_tab where x<19 and x>14;

參考：

《收穫，不止ORACLE》

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

分割槽表與堆表執行計劃的不同

相關文章