本文探讨了在数据库中高效使用分区技术与SQL查询优化的方法，通过实例展示了如何利用分区条件提升查询性能，并对比了不同索引类型在分区表上的表现。
高效的partition（使用分区条件）
Partition技术中：
高效的SQL应尽量使用分区条件
range分区表
create table range_tab(id int,col2 int,col3int)
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 selectrownum,rownum+1,rownum+2 from dual connect by rownum<=10000;
没有索引的情况下
SQL> select * from range_tab wherecol2=800; <=未使用分区条件
ID COL2 COL3
---------- ---------- ----------
799 800 801
Execution Plan
----------------------------------------------------------
Plan hash value: 2142701667
-------------------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 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 byoperation 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 toclient
415 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rowsprocessed
SQL> select * from range_tab wherecol2=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 | SELECTSTATEMENT | | 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 byoperation 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 toclient
415 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts(disk)
=>没有索引的情况下，正确的使用分区条件减少了访问数据的范围，从原来需要访问所有的分区，到正确使用分区条件后的一个分区
local索引
SQL> create index idx_col2_local onrange_tab(col2) local;
Index created.
SQL> select * from range_tabwhere 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 | SELECTSTATEMENT | | 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 byoperation 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 toclient
415 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rowsprocessed
SQL> select * from range_tab wherecol2=800 andid=799; <=使用了分区条件
ID COL2 COL3
---------- ---------- ----------
799 800 801
Execution Plan
----------------------------------------------------------
Plan hash value: 1302426126
---------------------------------------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 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 byoperation 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 toclient
415 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
=>local索引也是有多少个区就有多少个索引段，所以正确的使用了分区条件就将需要访问的索引块减少到了最低，索引使用分区条件也可以提升效率
Global索引
SQL> select * from range_tab wherecol2=800;
ID COL2 COL3
---------- ---------- ----------
799 800 801
Execution Plan
----------------------------------------------------------
Plan hash value: 312410708
------------------------------------------------------------------------------------------------------------------------
| Id |Operation |Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 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 byoperation 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 toclient
415 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rowsprocessed
SQL> select * from range_tab wherecol2=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 byoperation 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 toclient
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts(disk)
1 rowsprocessed
=> global索引是把分区表看成一个段进行建立索引的，索引分区条件对global索引影响不大，但是如果你使用分区条件，经过CBO的计算还是会优先分区条件使用权的
总结：
使用partition技术时，高效的SQL请使用分区条件；
反问，如果不使用分区条件你为什么要使用partition技术呢？
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
