ITPub博客

首页 > 数据库 > Oracle > 高效的partition(使用分区条件)

高效的partition(使用分区条件)

原创 Oracle 作者:lovehewenyu 时间:2013-10-30 16:38:58 0 删除 编辑

高效的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

 

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

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

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

|   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

 

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

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

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

|   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

 

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

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

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

|   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

 

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

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

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

|   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

 

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

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

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

|   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

 

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

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

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

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

下一篇: ORA-214 8I环境
请登录后发表评论 登录
全部评论
10年老鸟,Oracle,Mysql,EMC 存储,NBU备份

注册时间:2012-02-03

  • 博文量
    268
  • 访问量
    1313836