分区表与堆表执行计划的不同

分区表与堆表执行计划的不同

 

Execution Plan

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

Plan hash value: 84294021

 

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

| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   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

 

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

| Id  | Operation                      | Name         | Rows  | Cost (%CPU)| Time     |

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

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

|   1 |  LOAD TABLE CONVENTIONAL       | DOU_RANG_TAB |       |            |          |

|   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

 

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

| Id  | Operation                      | Name    | Rows  | Cost (%CPU)| Time     |

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

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

|   1 |  LOAD TABLE CONVENTIONAL       | DOU_TAB |       |            |          |

|   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

 

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

| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   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

 

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

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-774860/