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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-775371/