優化擁有謂詞or的子查詢
於節點2上發現一個sql,其結構如下:
select distinct t.attr, t.item
from a t
where attr = :1
and (pro = :2 or exists
(select 1
from b hps
where hps.pro = :3
and t.pro = hps.sub_pro))
鑑於以往的經驗,一開始就覺得子查詢中的連線謂詞or有問題。
注:
表a有50多萬條記錄,attr和pro上分別有索引;表b有10萬條記錄,pro上有單獨索引,同時(pro,sub_pro)組成unique索引;
同時表a上的attr,pro欄位的分佈情況如下,對於表a,pro欄位的選擇性比attr高出了很多
SQL> select count(distinct pro),count(distinct attr) from a;
COUNT(DISTINCT pro) COUNT(DISTINCT attr)
------------------------- ---------------------------
164067 716
先通過set autotrace traceonly檢視一把其執行計劃和consistent gets
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63 | 945 | 83 (2)| 00:00:01 |
| 1 | HASH UNIQUE | | 63 | 945 | 83 (2)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| a | 1268 | 19020 | 82 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | idx_a_attr | 1268 | | 8 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | idx_b_un_pro_sub | 1 | 12 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("pro"=TO_NUMBER(:B) OR EXISTS (SELECT 0 FROM "b" "HPS" WHERE
"HPS"."SUB_pro"=:B1 AND "HPS"."pro"=TO_NUMBER(:C)))
4 - access("attr"=TO_NUMBER(:A))
5 - access("HPS"."pro"=TO_NUMBER(:C) AND "HPS"."SUB_pro"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4690 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
從執行計劃看出,訪問表a的時候選擇了attr上的索引,實際效果不佳,邏輯讀有4690;
通過等價改寫後的sql如下,去除了謂詞or,改用了union
select distinct t.attr, t.item
from a t
where attr = :a
and pro = :b
union
select distinct t.attr, t.item
from a t
where attr = :a
and exists
(select 1
from b hps
where hps.pro = :c
and t.pro = hps.sub_pro)
改寫後的sql的執行計劃如下
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 150 | 16 (75)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 150 | 16 (75)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| a | 1 | 15 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | idx_a_pro | 3 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| a | 1 | 15 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 5 | 135 | 10 (10)| 00:00:01 |
| 7 | SORT UNIQUE | | 5 | 60 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | idx_b_un_pro_sub | 5 | 60 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | idx_a_pro | 3 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("attr"=TO_NUMBER(:A))
4 - access("pro"=TO_NUMBER(:B))
5 - filter("attr"=TO_NUMBER(:A))
8 - access("HPS"."pro"=TO_NUMBER(:C))
9 - access("T"."pro"="HPS"."SUB_pro")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
訪問表a時選擇了pro上的索引,雖然增加了一次訪問次數,但是邏輯讀卻下降到了24.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15480802/viewspace-702062/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- CBO的查詢轉換(謂詞推入與子查詢展開(Subquery Unnesting))
- MySQL子查詢的優化薦MySql優化
- exists與in子查詢優化優化
- 【SQL】Oracle查詢轉換之謂詞推送SQLOracle
- Oracle查詢轉換(四)連線謂詞推入Oracle
- Oracle not exist子查詢全掃的優化Oracle優化
- mysql子查詢的缺陷以及5.6的優化MySql優化
- 涉及子查詢sql的一次優化SQL優化
- Mysql優化系列之——優化器對子查詢的處理MySql優化
- Oracle 12CR2查詢轉換之謂詞推送Oracle
- 一個NOT EXISTS含有OR條件子查詢的優化優化
- 一文終結SQL 子查詢優化SQL優化
- 一次內鏈子查詢優化 2優化
- 一次內鏈子查詢優化 1優化
- 【大資料】SparkSql連線查詢中的謂詞下推處理(一)大資料SparkSQL
- 【大資料】SparkSql 連線查詢中的謂詞下推處理 (二)大資料SparkSQL
- 使用謂詞(NSPredicate)來提高集合遍歷與過濾查詢的效率
- PostgreSQL 原始碼解讀(29)- 查詢語句#14(查詢優化-上拉子查詢)SQL原始碼優化
- 查詢優化優化
- MySQL 的查詢優化MySql優化
- MySQL 優化五(關聯查詢子查詢以及 in 的效率問題)(高階篇)MySql優化
- 基於CBO優化器謂詞選擇率的計算方法優化
- 優化-mysql子查詢索引失效問題解決優化MySql索引
- pgsql查詢優化之模糊查詢SQL優化
- 子查詢-表子查詢
- Oracle in 查詢優化Oracle優化
- MySQL查詢優化MySql優化
- join 查詢優化優化
- HBase查詢優化優化
- 查詢優化器優化
- SQL查詢優化SQL優化
- 全文查詢的效能優化優化
- SQL查詢優化的方法SQL優化
- StoneDB 子查詢最佳化
- 關於樹結構的查詢優化,及許可權樹的查詢優化優化
- SQL查詢的:子查詢和多表查詢SQL
- 分頁查詢優化優化
- MySQL 慢查詢優化MySql優化