社群
學習
工具庫
AI工具
休閒
搜尋
繁体中文
首頁 資料庫 mysql教程 巧用函数索引性能调优

巧用函数索引性能调优

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 07, 2016 pm 03:28 PM
sql 函數 效能 執行 索引 調優

今天调了一条SQL，执行计划中出现BITMAP CONVERSION，检查了下索引，没有位图索引

今天调了一条SQL，执行计划中出现BITMAP CONVERSION，检查了下索引，没有位图索引啊。原来是Oracle有时候会选择将B-Tree索引进行BITMAP转换来进行SQL执行，从而导致极其恶劣的执行计划。在下列计划中BITMAP CONVERSION FROM / TO ROWIDS 就是进行了位图转换后的执行计划：

SQL> SELECT *

2 FROM (SELECT A.ASSET_ID,
3 C.CLASSIFY_CODE,
4 C.CLASSIFY_NAME,
5 V.NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(A.G3E_GEOMETRY)) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 WHERE A.CLASSIFY_ID = C.CLASSIFY_ID
9 AND A.BASE_VOLTAGE_ID = V.BASE_VOLTAGE_ID(+)
10 AND C.CLASSIFY_ID = '7002321'
11 AND SDO_RELATE(A.G3E_GEOMETRY,
12 SDO_GEOMETRY(3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY(1, 1003, 1),
16 SDO_ORDINATE_ARRAY(113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0)),
31 'MASK=ANYINTERACT') = 'TRUE')
32 WHERE ROWNUM 已选择500行。
已用时间: 00: 00: 06.71
执行计划
----------------------------------------------------------
Plan hash value: 1161815771
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 130K| 263 (1)| 00:00:04 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 501 | 130K| 263 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | DM_BASE_VOLTAGE | 81 | 729 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 501 | 64629 | 259 (1)| 00:00:04 |
| 5 | TABLE ACCESS BY INDEX ROWID | DM_CLASSIFY | 1 | 23 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_DM_CLASSIFY_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | DM_ASSET | 501 | 53106 | 259 (1)| 00:00:04 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 9 | BITMAP AND | | | | | |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 11 | SORT ORDER BY | | | | | |
|* 12 | DOMAIN INDEX | IDX_DM_ASSET_SPL | 50100 | | 0 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 14 | INDEX RANGE SCAN | IDX_ASSET_CLASSIFY_ID | 50100 | | 135 (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM 2 - access("A"."BASE_VOLTAGE_ID"="V"."BASE_VOLTAGE_ID"(+))
6 - access("C"."CLASSIFY_ID"='7002321')
12 - access("MDSYS"."SDO_RELATE"("A"."G3E_GEOMETRY","MDSYS"."SDO_GEOMETRY"(3003,4326,NULL,"SDO_ELEM
_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"(113.93897922622,22.806658666304,0,114.38475977774,22.8066
58666304,0,114.38475977774,22.42623522295,0,113.93897922622,22.42623522295,0,113.93897922622,22.80665
8666304,0)),'MASK=ANYINTERACT')='TRUE')
14 - access("A"."CLASSIFY_ID"='7002321')
统计信息
----------------------------------------------------------
76096 recursive calls
6004 db block gets
78944 consistent gets
0 physical reads
0 redo size
100625 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
500 rows processed

到网上找了下，有两种解决方法，一种是将_b_tree_bitmap_plans设置为false，另一种是将选择性差的索引去掉。

尝试用第一种方法：

SQL> alter session set "_b_tree_bitmap_plans"=false;
SQL> SELECT *
2 FROM (SELECT A.ASSET_ID,
3 C.CLASSIFY_CODE,
4 C.CLASSIFY_NAME,
5 V.NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(A.G3E_GEOMETRY)) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 WHERE A.CLASSIFY_ID = C.CLASSIFY_ID
9 AND A.BASE_VOLTAGE_ID = V.BASE_VOLTAGE_ID(+)
10 AND C.CLASSIFY_ID = '7002321'
11 AND SDO_RELATE(A.G3E_GEOMETRY,
12 SDO_GEOMETRY(3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY(1, 1003, 1),
16 SDO_ORDINATE_ARRAY(113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0)),
31 'MASK=ANYINTERACT') = 'TRUE')
32 WHERE ROWNUM 已选择500行。
已用时间: 00: 00: 06.51
执行计划
----------------------------------------------------------
Plan hash value: 1161815771
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 130K| 263 (1)| 00:00:04 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 501 | 130K| 263 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | DM_BASE_VOLTAGE | 81 | 729 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 501 | 64629 | 259 (1)| 00:00:04 |
| 5 | TABLE ACCESS BY INDEX ROWID | DM_CLASSIFY | 1 | 23 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_DM_CLASSIFY_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | DM_ASSET | 501 | 53106 | 259 (1)| 00:00:04 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 9 | BITMAP AND | | | | | |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 11 | SORT ORDER BY | | | | | |
|* 12 | DOMAIN INDEX | IDX_DM_ASSET_SPL | 50100 | | 0 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 14 | INDEX RANGE SCAN | IDX_ASSET_CLASSIFY_ID | 50100 | | 135 (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM 2 - access("A"."BASE_VOLTAGE_ID"="V"."BASE_VOLTAGE_ID"(+))
6 - access("C"."CLASSIFY_ID"='7002321')
12 - access("MDSYS"."SDO_RELATE"("A"."G3E_GEOMETRY","MDSYS"."SDO_GEOMETRY"(3003,4326,NULL,"SDO_ELEM
_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"(113.93897922622,22.806658666304,0,114.38475977774,22.8066
58666304,0,114.38475977774,22.42623522295,0,113.93897922622,22.42623522295,0,113.93897922622,22.80665
8666304,0)),'MASK=ANYINTERACT')='TRUE')
14 - access("A"."CLASSIFY_ID"='7002321')
统计信息
----------------------------------------------------------
76096 recursive calls
6002 db block gets
78944 consistent gets
0 physical reads
0 redo size
100625 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
500 rows processed

DM_ASSET有7百多万的数据，而CLASSIFY_ID的值只有39个，其中38个值可以走索引，一个值的数量非常多，典型的数据不均匀。
select CLASSIFY_ID,count(1) from DM_ASSET group by CLASSIFY_ID order by 2;
7002386 1
7002369 3
7002381 13
7002513 16
7002349 18
7002333 36
7002474 166
7002502 276
7002345 1131
7002344 1423
7002382 1709
7002359 1791
7002510 6340
7002374 6684
7002358 7286
7002361 8750
7002379 11189
7002340 12622
7002473 12717
7002348 14901
7002360 15722
7002335 17939
7002336 21492
7002500 28304
7002363 42883
7002343 49865
7002472 51327
7002321 76098
7002373 89485
7002515 110765
7002378 128512
7002380 147776
7002499 235166
7002370 271190
7002501 370439
7002398 456259
7002496 830986
7002401 4361079

尝试用第二种方法：删除CLASSIFY_ID上的索引。
SQL> SELECT *
2 FROM (SELECT A.ASSET_ID,
3 C.CLASSIFY_CODE,
4 C.CLASSIFY_NAME,
5 V.NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(A.G3E_GEOMETRY)) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 WHERE A.CLASSIFY_ID = C.CLASSIFY_ID
9 AND A.BASE_VOLTAGE_ID = V.BASE_VOLTAGE_ID(+)
10 AND C.CLASSIFY_ID = '7002321'
11 AND SDO_RELATE(A.G3E_GEOMETRY,
12 SDO_GEOMETRY(3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY(1, 1003, 1),
16 SDO_ORDINATE_ARRAY(113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0)),
31 'MASK=ANYINTERACT') = 'TRUE')
32 WHERE ROWNUM 已选择500行。
已用时间: 00: 00: 00.50
执行计划
----------------------------------------------------------
Plan hash value: 4025821404
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 130K| 10563 (1)| 00:02:07 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 501 | 130K| 10563 (1)| 00:02:07 |
| 3 | TABLE ACCESS FULL | DM_BASE_VOLTAGE | 81 | 729 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 501 | 64629 | 10560 (1)| 00:02:07 |
| 5 | TABLE ACCESS BY INDEX ROWID| DM_CLASSIFY | 1 | 23 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_DM_CLASSIFY_ID | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| DM_ASSET | 501 | 53106 | 10560 (1)| 00:02:07 |
|* 8 | DOMAIN INDEX | IDX_DM_ASSET_SPL | | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM 2 - access("A"."BASE_VOLTAGE_ID"="V"."BASE_VOLTAGE_ID"(+))
6 - access("C"."CLASSIFY_ID"='7002321')
7 - filter("A"."CLASSIFY_ID"='7002321')
8 - access("MDSYS"."SDO_RELATE"("A"."G3E_GEOMETRY","MDSYS"."SDO_GEOMETRY"(3003,4326,NULL,"
SDO_ELEM_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"(113.93897922622,22.806658666304,0,114.384
75977774,22.806658666304,0,114.38475977774,22.42623522295,0,113.93897922622,22.42623522295,0,
113.93897922622,22.806658666304,0)),'MASK=ANYINTERACT')='TRUE')
统计信息
----------------------------------------------------------
3965 recursive calls
6006 db block gets
30388 consistent gets
3 physical reads
340 redo size
94355 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
500 rows processed


问题又来了，删除索引后，这个SQL是快了，但原本根据CLASSIFY_ID走索引的语句性能出了问题。曾经在itpub上看到newid这么用，建一个函数索引：
CREATE INDEX IDX_ASSET_CLASSIFY_ID ON DM_ASSET(CASE WHEN CLASSIFY_ID !='7002401' THEN CLASSIFY_ID ELSE NULL END) nologging;
1. 上面的SQL执行也是非常快。
2. 根据CLASSIFY_ID的代码需要改一下
if(CLASSIFY_ID = '7002401') {--如果是这个值，则走全部扫描
select * from DM_ASSET where CLASSIFY_ID = '7002321';
} else {--如果是这个值，则走索引
select * from DM_ASSET where (CASE WHEN CLASSIFY_ID !='7002401'
THEN CLASSIFY_ID ELSE NULL END)='7002401'
}
本網站聲明
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序，用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！

顯示更多

熱門文章

刺客信條陰影：貝殼謎語解決方案
1 個月前 By DDD
Windows 11 KB5054979中的新功能以及如何解決更新問題
3 週前 By DDD
在哪裡可以找到原子中的起重機控制鑰匙卡
1 個月前 By DDD
如何修復KB5055523無法在Windows 11中安裝？
2 週前 By DDD
Inzoi：如何申請學校和大學
3 週前 By DDD
顯示更多

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版，非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

顯示更多

熱門話題

gmail信箱登陸入口在哪裡
7752
15
Java教學
1643
14
CakePHP 教程
1398
52
Laravel 教程
1293
25
PHP教程
1234
29
顯示更多
Related knowledge
不同Java框架的效能對比 不同Java框架的效能對比 Jun 05, 2024 pm 07:14 PM

不同Java框架的效能比較：RESTAPI請求處理：Vert.x最佳，請求速率達SpringBoot2倍，Dropwizard3倍。資料庫查詢：SpringBoot的HibernateORM優於Vert.x及Dropwizard的ORM。快取操作：Vert.x的Hazelcast客戶端優於SpringBoot及Dropwizard的快取機制。合適框架：根據應用需求選擇，Vert.x適用於高效能Web服務，SpringBoot適用於資料密集型應用，Dropwizard適用於微服務架構。

PHP 陣列鍵值翻轉：不同方法的效能比較分析 PHP 陣列鍵值翻轉：不同方法的效能比較分析 May 03, 2024 pm 09:03 PM

PHP數組鍵值翻轉方法效能比較顯示：array_flip()函數在大型數組（超過100萬個元素）下比for迴圈效能更優，耗時更短。手動翻轉鍵值的for迴圈方法耗時相對較長。

excel函數公式大全 excel函數公式大全 May 07, 2024 pm 12:04 PM

1. SUM函數，用於對一列或一組單元格中的數字進行求和，例如：=SUM(A1:J10)。 2、AVERAGE函數，用於計算一列或一組儲存格中的數字的平均值，例如：=AVERAGE(A1:A10)。 3.COUNT函數，用於計算一列或一組單元格中的數字或文字的數量，例如：=COUNT(A1:A10)4、IF函數，用於根據指定的條件進行邏輯判斷，並返回相應的結果。

C++中如何優化多執行緒程式的效能？ C++中如何優化多執行緒程式的效能？ Jun 05, 2024 pm 02:04 PM

優化C++多執行緒效能的有效技術包括：限制執行緒數量，避免爭用資源。使用輕量級互斥鎖，減少爭用。優化鎖的範圍，最小化等待時間。採用無鎖定資料結構，提高並發性。避免忙等，透過事件通知執行緒資源可用性。

Golang 函數接收 map 參數時的注意事項 Golang 函數接收 map 參數時的注意事項 Jun 04, 2024 am 10:31 AM

在Go中傳遞map給函數時，預設會建立副本，對副本的修改不影響原map。如果需要修改原始map，可透過指標傳遞。空map需小心處理，因為技術上是nil指針，傳遞空map給期望非空map的函數會發生錯誤。

Java框架的效能比較 Java框架的效能比較 Jun 04, 2024 pm 03:56 PM

根據基準測試，對於小型、高效能應用程序，Quarkus（快速啟動、低記憶體）或Micronaut（TechEmpower優異）是理想選擇。 SpringBoot適用於大型、全端應用程序，但啟動時間和記憶體佔用稍慢。

Golang 中隨機數產生器的效能如何？ Golang 中隨機數產生器的效能如何？ Jun 01, 2024 pm 09:15 PM

在Go中產生隨機數的最佳方法取決於應用程式所需的安全性等級。低安全性：使用math/rand套件產生偽隨機數字，適合大多數應用程式。高安全性：使用crypto/rand套件產生加密安全的隨機字節，適用於需要更強隨機性的應用程式。

C++與其他語言的效能比較 C++與其他語言的效能比較 Jun 01, 2024 pm 10:04 PM

在開發高效能應用程式時，C++的效能優於其他語言，尤其在微基準測試中。在宏基準測試中，其他語言如Java和C#的便利性和最佳化機制可能表現較好。在實戰案例中，C++在影像處理、數值計算和遊戲開發中表現出色，其對記憶體管理和硬體存取的直接控制帶來明顯的效能優勢。

See all articles