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

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

 

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

 

Tablecreated.

 

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

 

40rows created.

 

 

Execution Plan

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

Plan hash value: 1731520519

 

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

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

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

|   0 | INSERTSTATEMENT               |              |     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 |

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

 

PredicateInformation (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 fromdual connect by rownum<=40;

 

40rows created.

 

 

Execution Plan

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

Plan hash value: 1731520519

 

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

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

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

|   0 | INSERTSTATEMENT               |         |    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 |

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

 

PredicateInformation (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 |  PARTITIONRANGE 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

 

 

ExecutionPlan

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

Planhash 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 |

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

 

PredicateInformation (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

 

 

附表：

         实验脚本：

分区表创建

createtable dou_rang_tab(x int)

partitionby range(x)

(

partitionp1_10 values less than(10),

partitionp2_20 values less than(20),

partitionp3_30 values less than(30),

partitionp4_max values less than(maxvalue)

);

堆表创建

createtable dou_tab(x int);

分别向分区表和堆表插入数据

insertinto dou_rang_tab  select rownum fromdual connect by rownum<=40;

insertinto dou_tab  select rownum from dualconnect 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》