高效的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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 分割槽Partition
- 分割槽partition知識點
- 分割槽函式Partition By的基本用法函式
- Spark學習——分割槽Partition數Spark
- Oracle Partition 分割槽詳細總結Oracle
- Kafka分割槽分配策略（Partition Assignment Strategy）Kafka
- 使用parted建立大分割槽時 mkpart Warning: The resulting partition is not properly
- 分割槽函式partition by的基本用法【轉載】函式
- MySQL全面瓦解29：使用Partition功能實現水平分割槽MySql
- Oracle查詢Interval partition分割槽表內資料Oracle
- 在分割槽表上使用正確的索引來提高效能索引
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- Partition|Disk Utility 如何分割磁碟
- Hive的靜態分割槽與動態分割槽Hive
- Linux分割槽方案、分割槽建議Linux
- Spark SQL解析查詢parquet格式Hive表獲取分割槽欄位和查詢條件SparkSQLHive
- WXS與JS使用的部分割槽別JS
- Linux下磁碟分割槽工具cfdisk的使用Linux
- 對一條基於分割槽的簡單SQL的優化SQL優化
- 如何讓阿三 Windows 10、11 的恢復分割槽（Recovery Partition）恢復到 “蓋茨” 模式Windows模式
- PG的非分割槽表線上轉分割槽表
- Laravel Query Builder 複雜查詢案例：子查詢實現分割槽查詢 partition byLaravelUI
- oracle分割槽表和分割槽表exchangeOracle
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- Linux 分割槽擴容（根分割槽擴容，SWAP 分割槽擴容，掛載新分割槽為目錄）Linux
- SQL Server大分割槽表沒有空分割槽的情況下如何擴充套件分割槽的方法SQLServer套件
- HGDB的分割槽表實現SQL Server的分割槽檢視SQLServer
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 移動分割槽表和分割槽索引的表空間索引
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- win10分割槽使用mbr好還是gpt好_win10系統gpt分割槽和mbr分割槽的區別Win10GPT
- MySQL的分割槽（一）MySql
- MySQL的分割槽（二）MySql
- linux的分割槽方法Linux
- Flink的分割槽策略
- oracle分割槽表和非分割槽表exchangeOracle
- 非分割槽錶轉換成分割槽表
- (3) MySQL分割槽表使用方法MySql
- 教程：如何使用DataLakeAnalytics建立分割槽表