關於查詢轉換的一些簡單分析(二)
關於查詢轉換的作用，在之前的一篇博文裡面也大概說了檢視合併和子查詢巢狀的部分。http://blog.itpub.net/23718752/viewspace-1310272/ 今天來舉一個例項來分析一下。
這條sql語句是在生產環境中執行的，目前執行速度在0.1秒左右。我們來看看從查詢轉換的角度來看，對於同一條sql語句，反覆的變化就能夠折騰出不少的東西來。
sql語句如下：
select soc
from SERVICE_DETAILS ser --大表，資料量過億，做了分割槽
where agreement_no in (select subscriber_no
from subscriber --中級表，資料量百萬
where sub_status = 'A'
and (subscriber_no, prim_resource_tp) in (select agreement_no, resource_type from agreement_resource where resource_value='0910600138') --大表，資料量千萬，做了分割槽
)
and soc_status = 'A'
執行計劃如下：
Plan hash value: 1540053094
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 196 | 7 (15)| 00:00:01 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 7 | 196 | 7 (15)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 1 | 13 | 5 (0)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 1 | 36 | | | | |
| 5 | NESTED LOOPS | | | | | | | |
| 6 | NESTED LOOPS | | 1 | 36 | 5 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE ALL | | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
|* 9 | INDEX RANGE SCAN | AGREEMENT_RESOURCE_1IX | 1 | | 3 (0)| 00:00:01 | 1 | 11 |
|* 10 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 9 | | 1 (0)| 00:00:01 | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 7 | 105 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("RESOURCE_VALUE"='0910600138')
10 - access("SUBSCRIBER_NO"="AGREEMENT_NO")
11 - filter("SUB_STATUS"='A' AND "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
12 - access("AGREEMENT_NO"="SUBSCRIBER_NO")
13 - filter("SOC_STATUS"='A')
對應的索引資訊如下。
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
SERVICE_DETAILS_PK INDXS01 NORMAL UNIQUE NO AGREEMENT_NO,SOC,SOC_SEQ_NO TABLE VALID 117595228 23-OCT-14 N
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
SUBSCRIBER_1IX INDXM01 NORMAL NONUNIQUE NO CH_NODE_ID TABLE VALID 6994331 23-OCT-14 N
SUBSCRIBER_2IX INDXM01 NORMAL NONUNIQUE NO EXTERNAL_ID TABLE VALID 6994329 23-OCT-14 N
SUBSCRIBER_3IX INDXM01 NORMAL NONUNIQUE NO CUSTOMER_ID,SUB_STATUS TABLE VALID 6994331 23-OCT-14 N
SUBSCRIBER_PK INDXM01 NORMAL UNIQUE NO SUBSCRIBER_NO TABLE VALID 6994331 23-OCT-14 N
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
AGREEMENT_RESOURCE_1IX NORMAL NONUNIQUE YES RESOURCE_VALUE,RESOURCE_TYPE TABLE N/A 25762156 22-OCT-14 N
AGREEMENT_RESOURCE_2IX NORMAL NONUNIQUE YES FROM_RESOURCE_VAL,RESOURCE_TYPE TABLE N/A 25762156 22-OCT-14 N
AGREEMENT_RESOURCE_3IX NORMAL NONUNIQUE YES RESOURCE_SCOPE_ID TABLE N/A 0 22-OCT-14 N
AGREEMENT_RESOURCE_PK NORMAL UNIQUE YES AGREEMENT_NO,RESRC_SEQ_NO,AGREEMENT_KEY TABLE N/A 25762156 22-OCT-14 N
這條sql語句是在生產環境中執行的，目前執行速度在0.1秒左右。我們來看看從查詢轉換的角度來看，對於同一條sql語句，反覆的變化就能夠折騰出不少的東西來。
sql語句如下：
select soc
from SERVICE_DETAILS ser --大表，資料量過億，做了分割槽
where agreement_no in (select subscriber_no
from subscriber --中級表，資料量百萬
where sub_status = 'A'
and (subscriber_no, prim_resource_tp) in (select agreement_no, resource_type from agreement_resource where resource_value='0910600138') --大表，資料量千萬，做了分割槽
)
and soc_status = 'A'
執行計劃如下：
Plan hash value: 1540053094
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 196 | 7 (15)| 00:00:01 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 7 | 196 | 7 (15)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 1 | 13 | 5 (0)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 1 | 36 | | | | |
| 5 | NESTED LOOPS | | | | | | | |
| 6 | NESTED LOOPS | | 1 | 36 | 5 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE ALL | | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
|* 9 | INDEX RANGE SCAN | AGREEMENT_RESOURCE_1IX | 1 | | 3 (0)| 00:00:01 | 1 | 11 |
|* 10 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 9 | | 1 (0)| 00:00:01 | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 7 | 105 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("RESOURCE_VALUE"='0910600138')
10 - access("SUBSCRIBER_NO"="AGREEMENT_NO")
11 - filter("SUB_STATUS"='A' AND "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
12 - access("AGREEMENT_NO"="SUBSCRIBER_NO")
13 - filter("SOC_STATUS"='A')
對應的索引資訊如下。
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
SERVICE_DETAILS_PK INDXS01 NORMAL UNIQUE NO AGREEMENT_NO,SOC,SOC_SEQ_NO TABLE VALID 117595228 23-OCT-14 N
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
SUBSCRIBER_1IX INDXM01 NORMAL NONUNIQUE NO CH_NODE_ID TABLE VALID 6994331 23-OCT-14 N
SUBSCRIBER_2IX INDXM01 NORMAL NONUNIQUE NO EXTERNAL_ID TABLE VALID 6994329 23-OCT-14 N
SUBSCRIBER_3IX INDXM01 NORMAL NONUNIQUE NO CUSTOMER_ID,SUB_STATUS TABLE VALID 6994331 23-OCT-14 N
SUBSCRIBER_PK INDXM01 NORMAL UNIQUE NO SUBSCRIBER_NO TABLE VALID 6994331 23-OCT-14 N
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
AGREEMENT_RESOURCE_1IX NORMAL NONUNIQUE YES RESOURCE_VALUE,RESOURCE_TYPE TABLE N/A 25762156 22-OCT-14 N
AGREEMENT_RESOURCE_2IX NORMAL NONUNIQUE YES FROM_RESOURCE_VAL,RESOURCE_TYPE TABLE N/A 25762156 22-OCT-14 N
AGREEMENT_RESOURCE_3IX NORMAL NONUNIQUE YES RESOURCE_SCOPE_ID TABLE N/A 0 22-OCT-14 N
AGREEMENT_RESOURCE_PK NORMAL UNIQUE YES AGREEMENT_NO,RESRC_SEQ_NO,AGREEMENT_KEY TABLE N/A 25762156 22-OCT-14 N
這個查詢能夠從千萬級的記錄中耗時0.1秒就查出資料，確實是比較理想的。我們來試著折騰一下這條sql語句。
-->改動第一步，從in 切換為exists
我們先從語句的等價性來看，in切換為exists，標黃部分為變化的部分，語句的效果是等價的。
select soc
from SERVICE_DETAILS ser
where exists (select subscriber_no
from subscriber
where ser.agreement_no=subscriber.subscriber_no
and sub_status = 'A'
and (subscriber_no, prim_resource_tp) in (select agreement_no, resource_type from agreement_resource where resource_value='0910600138')
)
and soc_status = 'A'
但是執行計劃就讓人大失所望。
Plan hash value: 3038362059
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 105 | 300M (1)|999:59:59 | | |
|* 1 | FILTER | | | | | | | |
|* 2 | TABLE ACCESS FULL | SERVICE_DETAILS | 96M| 1384M| 530K (1)| 01:46:06 | | |
| 3 | NESTED LOOPS | | 1 | 36 | 3 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ALL | | 1 | 25 | 2 (0)| 00:00:01 | 1 | 11 |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE | 1 | 25 | 2 (0)| 00:00:01 | 1 | 11 |
|* 8 | INDEX RANGE SCAN | AGREEMENT_RESOURCE_PK | 4 | | 2 (0)| 00:00:01 | 1 | 11 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "PRDAPPO"."SUBSCRIBER" "SUBSCRIBER","PRDAPPO"."AGREEMENT_RESOURCE"
"AGREEMENT_RESOURCE" WHERE "AGREEMENT_NO"=:B1 AND "RESOURCE_VALUE"='0910600138' AND
"PRIM_RESOURCE_TP"="RESOURCE_TYPE" AND "SUBSCRIBER"."SUBSCRIBER_NO"=:B2 AND "SUB_STATUS"='A'))
2 - filter("SOC_STATUS"='A')
4 - filter("SUB_STATUS"='A')
5 - access("SUBSCRIBER"."SUBSCRIBER_NO"=:B1)
7 - filter("RESOURCE_VALUE"='0910600138' AND "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
8 - access("AGREEMENT_NO"=:B1)
發現了全表掃描，而且資源消耗很高。時間消耗已經得到了理論的最高值。
可見，在某些場景中，exists和in還是有所不同，在最佳化器分析的過程中還是對一些細節做了區別處理。稍後來解釋。
-->改動第二步，去除in,exists
我們嘗試去除語句中的in部分。直接把相關的表放在from之後。查詢條件也等價保持不變。這種方法看似沒有經過任何的調優，但是從查詢轉換的角度來說，可能oracle更能夠合理的做出分析。
select ser.soc
from subscriber sub,agreement_resource agr,SERVICE_DETAILS ser
where sub.sub_status = 'A' and sub.subscriber_no=agr.agreement_no and sub.prim_resource_tp= agr.resource_type and resource_value='0910600138'
and ser.agreement_no =sub.subscriber_no
and ser.soc_status = 'A'
執行計劃如下。
Plan hash value: 3783316108
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 408 | 6 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 8 | 408 | 6 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 36 | 5 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
|* 6 | INDEX RANGE SCAN | AGREEMENT_RESOURCE_1IX | 1 | | 3 (0)| 00:00:01 | 1 | 11 |
|* 7 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 8 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 9 | | 1 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 7 | 105 | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("RESOURCE_VALUE"='0910600138')
7 - filter("SUB"."SUB_STATUS"='A' AND "SUB"."PRIM_RESOURCE_TP"="AGR"."RESOURCE_TYPE")
8 - access("SUB"."SUBSCRIBER_NO"="AGR"."AGREEMENT_NO")
9 - access("SER"."AGREEMENT_NO"="SUB"."SUBSCRIBER_NO")
10 - filter("SER"."SOC_STATUS"='A')
-->改動第三步，從檢視合併的角度調整
從檢視合併的角度來看，可以使用下面的方式進行調整，定義一個子查詢，然後再和service_details關聯。
select ser.soc
from
(select subscriber_no
from subscriber
where sub_status = 'A'
and (subscriber_no, prim_resource_tp) in (select agreement_no, resource_type from agreement_resource where resource_value='0910600138')) temp,
SERVICE_DETAILS ser
where agreement_no =temp.subscriber_no
and soc_status = 'A'
執行計劃如下：
Plan hash value: 3927794511
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 408 | 7 (15)| 00:00:01 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 8 | 408 | 7 (15)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 36 | 6 (17)| 00:00:01 | | |
| 4 | SORT UNIQUE | | 1 | 25 | 4 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL | | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
|* 7 | INDEX RANGE SCAN | AGREEMENT_RESOURCE_1IX | 1 | | 3 (0)| 00:00:01 | 1 | 11 |
|* 8 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 9 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 9 | | 1 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 7 | 105 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("RESOURCE_VALUE"='0910600138')
8 - filter("SUB_STATUS"='A' AND "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
9 - access("SUBSCRIBER_NO"="AGREEMENT_NO")
10 - access("AGREEMENT_NO"="SUBSCRIBER_NO")
11 - filter("SOC_STATUS"='A')
-->改動第4步，從子查詢解巢狀的角度調整
回到exists的那個問題，執行的時候，exists的效能要比in差很遠(在這個例子中,需要視業務場景而定)
我們看看怎麼調整讓它也快起來。我們加入hint，使得子查詢解巢狀被禁用。優先從子查詢中先輸出資料。
select soc
from SERVICE_DETAILS ser
where exists (select /*+ UNNEST */ subscriber_no
from subscriber
where ser.agreement_no=subscriber.subscriber_no
and sub_status = 'A'
and (subscriber_no, prim_resource_tp) in (select agreement_no, resource_type from agreement_resource where resource_value='0910600138')
)
and soc_status = 'A'
執行計劃如下。可以看到又得到了我們預期的效果。
Plan hash value: 2142047497
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 196 | 7 (15)| 00:00:01 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 7 | 196 | 7 (15)| 00:00:01 | | |
| 3 | VIEW | VW_SQ_1 | 1 | 13 | 5 (0)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 1 | 36 | | | | |
| 5 | NESTED LOOPS | | | | | | | |
| 6 | NESTED LOOPS | | 1 | 36 | 5 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE ALL | | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
|* 9 | INDEX RANGE SCAN | AGREEMENT_RESOURCE_1IX | 1 | | 3 (0)| 00:00:01 | 1 | 11 |
|* 10 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 9 | | 1 (0)| 00:00:01 | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 7 | 105 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("RESOURCE_VALUE"='0910600138')
10 - access("SUBSCRIBER_NO"="AGREEMENT_NO")
11 - filter("SUB_STATUS"='A' AND "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
12 - access("SER"."AGREEMENT_NO"="ITEM_0")
13 - filter("SOC_STATUS"='A')
-->改動第一步，從in 切換為exists
我們先從語句的等價性來看，in切換為exists，標黃部分為變化的部分，語句的效果是等價的。
select soc
from SERVICE_DETAILS ser
where exists (select subscriber_no
from subscriber
where ser.agreement_no=subscriber.subscriber_no
and sub_status = 'A'
and (subscriber_no, prim_resource_tp) in (select agreement_no, resource_type from agreement_resource where resource_value='0910600138')
)
and soc_status = 'A'
但是執行計劃就讓人大失所望。
Plan hash value: 3038362059
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 105 | 300M (1)|999:59:59 | | |
|* 1 | FILTER | | | | | | | |
|* 2 | TABLE ACCESS FULL | SERVICE_DETAILS | 96M| 1384M| 530K (1)| 01:46:06 | | |
| 3 | NESTED LOOPS | | 1 | 36 | 3 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ALL | | 1 | 25 | 2 (0)| 00:00:01 | 1 | 11 |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE | 1 | 25 | 2 (0)| 00:00:01 | 1 | 11 |
|* 8 | INDEX RANGE SCAN | AGREEMENT_RESOURCE_PK | 4 | | 2 (0)| 00:00:01 | 1 | 11 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "PRDAPPO"."SUBSCRIBER" "SUBSCRIBER","PRDAPPO"."AGREEMENT_RESOURCE"
"AGREEMENT_RESOURCE" WHERE "AGREEMENT_NO"=:B1 AND "RESOURCE_VALUE"='0910600138' AND
"PRIM_RESOURCE_TP"="RESOURCE_TYPE" AND "SUBSCRIBER"."SUBSCRIBER_NO"=:B2 AND "SUB_STATUS"='A'))
2 - filter("SOC_STATUS"='A')
4 - filter("SUB_STATUS"='A')
5 - access("SUBSCRIBER"."SUBSCRIBER_NO"=:B1)
7 - filter("RESOURCE_VALUE"='0910600138' AND "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
8 - access("AGREEMENT_NO"=:B1)
發現了全表掃描，而且資源消耗很高。時間消耗已經得到了理論的最高值。
可見，在某些場景中，exists和in還是有所不同，在最佳化器分析的過程中還是對一些細節做了區別處理。稍後來解釋。
-->改動第二步，去除in,exists
我們嘗試去除語句中的in部分。直接把相關的表放在from之後。查詢條件也等價保持不變。這種方法看似沒有經過任何的調優，但是從查詢轉換的角度來說，可能oracle更能夠合理的做出分析。
select ser.soc
from subscriber sub,agreement_resource agr,SERVICE_DETAILS ser
where sub.sub_status = 'A' and sub.subscriber_no=agr.agreement_no and sub.prim_resource_tp= agr.resource_type and resource_value='0910600138'
and ser.agreement_no =sub.subscriber_no
and ser.soc_status = 'A'
執行計劃如下。
Plan hash value: 3783316108
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 408 | 6 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 8 | 408 | 6 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 36 | 5 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
|* 6 | INDEX RANGE SCAN | AGREEMENT_RESOURCE_1IX | 1 | | 3 (0)| 00:00:01 | 1 | 11 |
|* 7 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 8 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 9 | | 1 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 7 | 105 | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("RESOURCE_VALUE"='0910600138')
7 - filter("SUB"."SUB_STATUS"='A' AND "SUB"."PRIM_RESOURCE_TP"="AGR"."RESOURCE_TYPE")
8 - access("SUB"."SUBSCRIBER_NO"="AGR"."AGREEMENT_NO")
9 - access("SER"."AGREEMENT_NO"="SUB"."SUBSCRIBER_NO")
10 - filter("SER"."SOC_STATUS"='A')
-->改動第三步，從檢視合併的角度調整
從檢視合併的角度來看，可以使用下面的方式進行調整，定義一個子查詢，然後再和service_details關聯。
select ser.soc
from
(select subscriber_no
from subscriber
where sub_status = 'A'
and (subscriber_no, prim_resource_tp) in (select agreement_no, resource_type from agreement_resource where resource_value='0910600138')) temp,
SERVICE_DETAILS ser
where agreement_no =temp.subscriber_no
and soc_status = 'A'
執行計劃如下：
Plan hash value: 3927794511
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 408 | 7 (15)| 00:00:01 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 8 | 408 | 7 (15)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 36 | 6 (17)| 00:00:01 | | |
| 4 | SORT UNIQUE | | 1 | 25 | 4 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL | | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
|* 7 | INDEX RANGE SCAN | AGREEMENT_RESOURCE_1IX | 1 | | 3 (0)| 00:00:01 | 1 | 11 |
|* 8 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 9 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 9 | | 1 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 7 | 105 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("RESOURCE_VALUE"='0910600138')
8 - filter("SUB_STATUS"='A' AND "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
9 - access("SUBSCRIBER_NO"="AGREEMENT_NO")
10 - access("AGREEMENT_NO"="SUBSCRIBER_NO")
11 - filter("SOC_STATUS"='A')
-->改動第4步，從子查詢解巢狀的角度調整
回到exists的那個問題，執行的時候，exists的效能要比in差很遠(在這個例子中,需要視業務場景而定)
我們看看怎麼調整讓它也快起來。我們加入hint，使得子查詢解巢狀被禁用。優先從子查詢中先輸出資料。
select soc
from SERVICE_DETAILS ser
where exists (select /*+ UNNEST */ subscriber_no
from subscriber
where ser.agreement_no=subscriber.subscriber_no
and sub_status = 'A'
and (subscriber_no, prim_resource_tp) in (select agreement_no, resource_type from agreement_resource where resource_value='0910600138')
)
and soc_status = 'A'
執行計劃如下。可以看到又得到了我們預期的效果。
Plan hash value: 2142047497
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 196 | 7 (15)| 00:00:01 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 7 | 196 | 7 (15)| 00:00:01 | | |
| 3 | VIEW | VW_SQ_1 | 1 | 13 | 5 (0)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 1 | 36 | | | | |
| 5 | NESTED LOOPS | | | | | | | |
| 6 | NESTED LOOPS | | 1 | 36 | 5 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE ALL | | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE | 1 | 25 | 4 (0)| 00:00:01 | 1 | 11 |
|* 9 | INDEX RANGE SCAN | AGREEMENT_RESOURCE_1IX | 1 | | 3 (0)| 00:00:01 | 1 | 11 |
|* 10 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | SUBSCRIBER | 1 | 11 | 1 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 9 | | 1 (0)| 00:00:01 | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 7 | 105 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("RESOURCE_VALUE"='0910600138')
10 - access("SUBSCRIBER_NO"="AGREEMENT_NO")
11 - filter("SUB_STATUS"='A' AND "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
12 - access("SER"."AGREEMENT_NO"="ITEM_0")
13 - filter("SOC_STATUS"='A')
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1352312/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於查詢轉換的一些簡單分析(一)
- 關於查詢轉換的一些簡單分析(三)
- 關於查詢轉換的一些總結
- Oracle查詢轉換（一）簡單檢視合併Oracle
- 二分查詢的簡單理解
- 關聯查詢子查詢效率簡單比照
- 關於二分查詢及其上下界問題的一些思考
- 簡單的查詢
- vim下多行查詢替換簡單命令
- 查詢轉換
- 對 MySQL 慢查詢日誌的簡單分析MySql
- 關於查詢最佳化的一些總結
- 簡單的mysql查詢MySql
- 對於專案中簡單的多條件查詢的一些心得體會
- Oracle 查詢轉換Oracle
- 二、mybatis查詢分析MyBatis
- openGauss核心分析2：簡單查詢的執行
- 關於MongoDB的簡單理解（二）--Java篇MongoDBJava
- MySQL - 資料查詢 - 簡單查詢MySql
- 關於 Elasticsearch nested field /script 的一些複雜查詢Elasticsearch
- sqlalchemy在python中的使用(關於查詢)二SQLPython
- Java中關於二分查詢的問題Java
- 關於MySQL 通用查詢日誌和慢查詢日誌分析MySql
- Mybatis簡單查詢MyBatis
- JavaScript實現簡單二叉查詢樹JavaScript
- 簡單的查詢語法
- ElasticSearch中的簡單查詢Elasticsearch
- 關於兩個簡單問題的分析
- OJB查詢（二） (轉)
- Oracle查詢轉換（二）複雜檢視合併Oracle
- Laravel 關聯查詢 ——一對一 簡單例子Laravel單例
- 關於抽象類和介面一些簡單的概念抽象
- Oracle 查詢轉換初探Oracle
- GP詭異的查詢轉換
- 關於Laravel的二、三事（1）簡單的路由Laravel路由
- Oracle查詢轉換（五）子查詢展開Oracle
- SQL Story摘錄（一）————簡單查詢初探 (轉)SQL
- MySQL的簡單查詢語句MySql