Oracle中利用函式索引處理資料傾斜案例
http://blog.itpub.net/17203031/viewspace-681311/
關於B-Tree、Bitmap、函式索引的相關內容請參考另一篇博文:
Oracle中B-Tree、Bitmap和函式索引使用案例總結
通常來說,索引選取的資料列最好為分散度高、選擇性好。從索引樹結構的角度看,列值都是分佈在葉節點位置。這樣,透過樹結構搜尋得到的葉節點數量效率比較高。
實際中,我們常常遇到資料列值傾斜的情況。就是說,整個列資料取值有限。但是大部分資料值都集中在少數一兩個取值裡,其他取值比例極少。比如:一個資料列值有“N”、“B”、“M”、“P”、“Q”幾個取值,其中55%資料行取值為“N”,40%資料行取值為“B”,剩下的取值分佈在5%的資料行中。對於這種結構的資料列加索引,是存在一些問題的。
首先,預設資料庫是會為所有的列值(非空)建立索引結構。也就意味著無論是高頻度取值,還是低頻度取值,都會在索引結構的葉節點上出現。當然,這樣的大部分葉節點都是這些重複值。
其次,在CBO(基於成本最佳化器)的作用下,對高頻度取值的搜尋一般都不會選擇索引作為搜尋路徑,因為進行全表掃描可能效率更高。我們為資料列建立了索引,但高頻詞的查詢永遠不會走到索引路徑(下面的實驗會證明這一點)。
最後,建立的索引空間和時間消耗比較大。建立的索引涵蓋所有取值,對海量資料表而言,佔有的空間勢必較大。同時,在進行小頻度資料查詢的時候,雖然會去走索引路徑,但是引起的邏輯物理讀也是有一些損耗。
下面引入一個解決方法
思路:既然高頻度值在查詢的時候不會走到索引路徑,可以考慮將其剔出構建索引的過程,只為那些低頻度資料值建立索引結構。這樣,建立的索引樹結構相對較小,而且索引查詢的效率也能提升。
具體的方法是使用decode函式。decode(a,b,c,d,e…f)含義:如果a=b,則返回c,等於d,返回e,最後沒有匹配的情況下,返回f。針對上面的例子,可以使用decode(列名,‘N’, null,‘B’,null,列名),含義是,如果該列取值為N或者B,直接設定為null,否則才返回列值,並且以此建立函式索引。
這樣做藉助了Oracle兩個功能:1、對null值不生成索引;2、函式索引;
下面透過實驗來證明該方法:
1、構建實驗測試環境
--建立測試資料表tb_wjq
SEIANG@seiang11g>create table tb_wjq as select * from dba_objects where owner
in ('SEIANG','PUBLIC','HR','SYSMAN','XDB','BI','SYS');
Table created.
--使用指令碼插入大量資料
begin
for i in 1..8 loop
insert /*+ append */ into tb_wjq select * from tb_wjq;
commit;
end loop;
end;
/
SEIANG@seiang11g>select count(*) from tb_wjq;
COUNT(*)
----------
9804160
Elapsed: 00:00:01.54
--用於實驗的資料量分佈情況
SEIANG@seiang11g>select owner,count(*) from tb_wjq group by owner;
OWNER COUNT(*)
------------------------------ ----------
SEIANG 3072
PUBLIC 4352256
HR 4352
SYSMAN 454912
XDB 149760
BI 1024
SYS 4838784
7 rows selected.
Elapsed: 00:00:02.44
可以看到,九萬多條資料,絕大部分資料集中到了PUBLIC、SYSMAN、SYS上,其他資料取值頻數較小。資料傾斜趨勢明顯。
2、建索引
--分別對owner列建立常規、函式索引。
SEIANG@seiang11g>create index idx_tb_wjq_owner_normal on tb_wjq(owner);
Index created.
Elapsed: 00:00:24.72
SEIANG@seiang11g>create index func_idx_tb_wjq_owner on tb_wjq(decode
(owner,'PUBLIC',null,'SYSMAN',null,'SYS',null,owner));
Index created.
Elapsed: 00:00:12.34
索引func_idx_tb_wjq_owner將PUBLIC、SYSMAN、SYS值轉化為null,剔出了建立索引的過程。不僅可以從上面建立索引所用的時間可以看出,而且從下面的索引段資訊看,兩個索引所佔的空間差異比較大,也證明了這點。
SEIANG@seiang11g>select
owner,segment_name,segment_type,bytes/1024/1024,blocks,extents from
dba_segments where segment_name='IDX_TB_WJQ_OWNER_NORMAL';
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 BLOCKS
EXTENTS
--------------- ------------------------------ ------------------
--------------- ---------- ----------
SEIANG IDX_TB_WJQ_OWNER_NORMAL INDEX 184 23552 94
Elapsed: 00:00:00.01
SEIANG@seiang11g>select
owner,segment_name,segment_type,bytes/1024/1024,blocks,extents from
dba_segments where segment_name='FUNC_IDX_TB_WJQ_OWNER';
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 BLOCKS
EXTENTS
--------------- ------------------------------ ------------------
--------------- ---------- ----------
SEIANG
FUNC_IDX_TB_WJQ_OWNER INDEX 3 384 18
由上可以看出,同樣是對一個資料列加索引。普通索引型別IDX_TB_WJQ_OWNER_NORMAL佔據94個區,23552個資料塊,空間約佔184M。而函式索引FUNC_IDX_TB_WJQ_OWNER的空間只用了初始分配的18個區,384個資料塊,空間約佔3M。由此,空間優勢直觀體現!
--收集統計資料,由於是實驗性質,而且資料量大,採用高取樣率收集統計資訊。
SEIANG@seiang11g>exec dbms_stats.gather_table_stats('SEIANG', 'TB_WJQ',
cascade => true, estimate_percent => 100,method_opt => 'for all
indexed columns');
PL/SQL procedure successfully completed.
Elapsed: 00:00:49.67
3、檢索效率分析
針對owner資料量149760的XDB取值進行分析。
--直接索引搜尋:
SEIANG@seiang11g>select * from tb_wjq where owner='XDB';
149760 rows selected.
Elapsed: 00:00:01.89
Execution Plan
----------------------------------------------------------
Plan hash value: 3735191644
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 149K|
13M| 4708 (1)| 00:00:57 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TB_WJQ | 149K|
13M| 4708 (1)| 00:00:57 |
|* 2 | INDEX RANGE SCAN | IDX_TB_WJQ_OWNER_NORMAL | 149K| |
348 (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='XDB')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
26000
consistent gets
6349 physical reads
0 redo size
16482673 bytes sent via SQL*Net to client
110336 bytes received via SQL*Net from client
9985 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
149760 rows processed
發現採用BI作為搜尋值時,是進行了索引搜尋。下面是用函式索引搜尋進行對比。
SEIANG@seiang11g>select * from tb_wjq where decode
(owner,'PUBLIC',null,'SYSMAN',null,'SYS',null,owner)='XDB';
149760 rows selected.
Elapsed: 00:00:01.54
Execution Plan
----------------------------------------------------------
Plan hash value: 3652333940
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 149K|
13M| 7091 (1)| 00:01:26 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TB_WJQ | 149K|
13M| 7091 (1)| 00:01:26 |
|* 2 | INDEX
RANGE SCAN |
FUNC_IDX_TB_WJQ_OWNER |
149K| | 316
(1)| 00:00:04 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access(DECODE("OWNER",'PUBLIC',NULL,'SYSMAN',NULL,'SYS',NULL,"OWNER")='XDB')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25998
consistent gets
0 physical reads
0 redo size
9017261 bytes sent via SQL*Net to client
110336 bytes received via SQL*Net from client
9985 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
149760 rows processed
對比後,我們可以發現,使用函式索引的方法,在執行時間、物理邏輯讀、CPU使用上有一定差異。
|
普通索引 |
函式索引 |
執行時間 |
00: 00: 01.89 |
00: 00: 01.54 |
CPU使用 |
4708 |
7091 |
consistent gets |
26000 |
25998 |
physical reads |
6349 |
0 |
結論:使用函式索引處理偏值方法,在一定長度上最佳化查詢效率和索引結構。上表的資料表明,會使邏輯物理讀的消耗很大程度的減少(索引結構簡化),同時連帶影響執行時間的縮小。因為使用函式要進行計算,CPU使用率相對較高,在可以接受的範圍內。
但是,這種方法是存在一些限制的,應用前一定要仔細規劃。
首先,資料表資料要保證較大。因為畢竟函式索引的建立和搜尋較普通索引消耗大,如果資料表小,帶來的最佳化程度不能彌補消耗的成本,結果可能得不償失。筆者進行的一系列實驗中,也發現在資料量中等偏小時,這種效能優勢不能凸顯。
其次,列值傾斜趨勢明顯。透過開篇的討論我們不難發現,列值傾斜的程度越高,使用函式索引剔出的資料量也就越大,生成的索引樹結構也就越小越最佳化。這一點是本方法的核心!
最後,使用函式索引搜尋時,搜尋的取值頻數越高,最佳化效果越好。在本例中,取值XDB的列有149760行,可以看出明顯的效能最佳化。但是當我們選擇值有1024條資料的BI值時,這種最佳化趨勢可以看到,但是明顯程度降低(實驗結果如下所示)。這裡的原因可能是資料量小時,兩種方法邏輯物理讀的差異度縮小。
--直接索引
SEIANG@seiang11g>select * from tb_wjq where owner='BI';
1024 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3735191644
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1024 |
98K| 35 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_WJQ | 1024 |
98K| 35 (0)| 00:00:01 |
|* 2 | INDEX
RANGE SCAN |
IDX_TB_WJQ_OWNER_NORMAL |
1024 | | 5
(0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='BI')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
280 consistent gets
151 physical reads
0 redo size
98579 bytes sent via SQL*Net to client
1271 bytes received via SQL*Net from client
70 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1024 rows processed
--函式索引
SEIANG@seiang11g>select * from tb_wjq where decode
(owner,'PUBLIC',null,'SYSMAN',null,'SYS',null,owner)='BI';
1024 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3652333940
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1024 |
98K| 50 (0)| 00:00:01 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TB_WJQ | 1024 |
98K| 50 (0)| 00:00:01 |
|* 2 | INDEX
RANGE SCAN |
FUNC_IDX_TB_WJQ_OWNER | 1024
| | 3
(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access(DECODE("OWNER",'PUBLIC',NULL,'SYSMAN',NULL,'SYS',NULL,"OWNER")='BI')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
279
consistent gets
3 physical reads
0 redo size
33969 bytes sent via SQL*Net to client
1271 bytes received via SQL*Net from client
70 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1024 rows processed
但是如果是對SYS、PUBLIC或SYSMAN進行查詢時,將會跳過所有的索引,直徑進行全表掃描。
SEIANG@seiang11g>select * from tb_wjq where owner='SYS';
4838784 rows selected.
Elapsed: 00:00:45.85
Execution Plan
----------------------------------------------------------
Plan hash value: 1501781665
----------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 4838K| 452M| 39893
(1)| 00:07:59 |
|* 1 | TABLE
ACCESS FULL| TB_WJQ |
4838K| 452M| 39893 (1)| 00:07:59 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
457638 consistent gets
139684 physical reads
0 redo size
255169095 bytes sent via SQL*Net to client
3548958 bytes received via SQL*Net from client
322587 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4838784 rows processed
作者:SEian.G(苦練七十二變,笑對八十一難)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31015730/viewspace-2147583/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 巧用函式索引解決資料傾斜列查詢函式索引
- Hive資料傾斜Hive
- 編號函式 自定義函式 集合型別 表的優化 資料傾斜函式型別優化
- 大資料SQL優化之資料傾斜解決案例全集大資料SQL優化
- Spark學習——資料傾斜Spark
- 資料傾斜解決辦法
- IoT資料傾斜如何解決
- 利用索引提高SQL Server資料處理的效率索引SQLServer
- Spark 資料傾斜及其解決方案Spark
- hive優化-資料傾斜優化Hive優化
- 淺析 Hadoop 中的資料傾斜(R0.1)Hadoop
- oracle函式大全-字串處理函式Oracle函式字串
- Oracle函式-->字元處理Oracle函式字元
- 大資料常見問題之資料傾斜大資料
- Oracle優化案例-自定義函式索引(五)Oracle優化函式索引
- Hive千億級資料傾斜解決方案Hive
- 【Hive】資料傾斜優化 shuffle, join, group byHive優化
- Redis 切片叢集的資料傾斜分析Redis
- Oracle中B-Tree、Bitmap和函式索引使用案例總結Oracle函式索引
- 資料型別與函式索引-Oracle篇資料型別函式索引Oracle
- [MYSQL -11]使用函式處理資料MySql函式
- CLUSTERING_FACTOR、回表、資料分佈傾斜走全表還是索引索引
- Oracle資料傾斜導致的問題-有繫結變數Oracle變數
- Oracle資料傾斜導致的問題-無繫結變數Oracle變數
- hadoop 透過cachefile來避免資料傾斜Hadoop
- PostgreSQL DBA(193) - 資料傾斜下的HashJoinSQL
- 【Spark篇】---Spark解決資料傾斜問題Spark
- oracle函式索引Oracle函式索引
- PIoU Loss:傾斜目標檢測專用損失函式,公開超難傾斜目標資料集Retail50K | ECCV 2020 Spotlight函式AI
- MySQL-日期和資料處理函式MySql函式
- Oracle分散式事務典型案例處理Oracle分散式
- Oracle 中異常處理函式 - SQLCODE和SQLERRMOracle函式SQL
- 如何解決Hive中經常出現的資料傾斜問題Hive
- mongoDB中聚合函式java處理MongoDB函式Java
- Oracle之函式索引Oracle函式索引
- Oracle 中的 TO_DATE 和 TO_CHAR 函式 日期處理Oracle函式
- Spark效能最佳化篇三:資料傾斜調優Spark
- 如何解決 Redis 資料傾斜、熱點等問題Redis