关于查询转换的作用，在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。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,AGRE
EMENT_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')
