系统设计时应尽量避免出现left outer join, right outer join
来源:互联网 发布:带-域名 编辑:程序博客网 时间:2024/06/05 06:45
今天盖尔找我优化一条SQL,SQL如下:
SELECT DISTINCT b.organ_id, c.company_name as organ_name, a.distri_date, a.distri_type, d.TYPE_NAME Capital_name, b.policy_code, b.apply_code send_code, i.ATTRIBUTE10 total_code, f.pay_mode, j.type_name as policy_type_name, e.Internal_Id AS product_code, round(a.distri_amount, 2) AS fee_amount, decode(a.posted, 'Y', to_char(i.transaction_date, 'yyyy-mm-dd'), to_char(a.distri_date, 'yyyy-mm-dd')) As finish_time, F.DR_SEG1, F.DR_SEG2, F.DR_SEG3, F.DR_SEG4, F.DR_SEG5, F.DR_SEG6, f.dr_seg7, f.dr_seg8, f.dr_seg9, f.dr_seg10, f.cr_seg1, f.cr_seg2, f.cr_seg3, f.cr_seg4, f.cr_seg5, f.cr_seg6, f.cr_seg7, f.cr_seg8, f.cr_seg9, f.cr_seg10, f.je_posting_id as cred_id FROM T_CAPITAL_DISTRIBUTE a, t_contract_master b, t_channel_type j, t_company_organ c, t_capital_distri_type d, t_product_life e, t_contract_product f, (select * from T_BIZ_ACCOUNTING_INFO where DATA_TABLE = '7') F, T_GL_BIZ_INTERFACE i, (select organ_id from t_company_organ start with organ_id = '101' connect by parent_id = prior organ_id) o WHERE a.policy_id = b.policy_id and a.item_id = f.item_id(+) AND b.organ_id = c.Organ_Id AND a.distri_type = d.distri_type AND a.product_id = e.product_id and b.policy_type = j.INDIVIDUAL_GROUP AND A.capital_id = F.FEE_ID(+) AND A.cred_id = i.posting_id(+) and a.organ_id = i.segment1(+) and nvl(a.posted, 'N') = 'Y' and a.cred_id = 493997 and i.transaction_date >= to_date('2011-11-01', 'yyyy-MM-dd') and i.transaction_date < to_date('2011-11-30', 'yyyy-MM-dd') + 1 and a.distri_type = i.reference3(+) and i.segment1 = o.organ_id(+);
盖尔说这个SQL逻辑读有2千万,跑300s,返回9000条数据,SQL 执行计划如下:
SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|-------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 356 | 27 (0)|| 1 | SORT UNIQUE | | 1 | 356 | 27 (0)||* 2 | HASH JOIN OUTER | | 1 | 356 | 12 (9)|| 3 | NESTED LOOPS | | 1 | 350 | 10 (10)|| 4 | NESTED LOOPS | | 1 | 338 | 9 (12)|| 5 | NESTED LOOPS OUTER | | 1 | 302 | 8 (13)|| 6 | NESTED LOOPS | | 1 | 171 | 7 (15)|| 7 | NESTED LOOPS | | 1 | 125 | 6 (17)|| 8 | NESTED LOOPS | | 1 | 100 | 5 (20)|| 9 | NESTED LOOPS OUTER | | 1 | 86 | 4 (25)|| 10 | NESTED LOOPS | | 1 | 76 | 3 (34)|| 11 | TABLE ACCESS BY INDEX ROWID| T_GL_BIZ_INTERFACE | 1 | 24 | 2 (50)||* 12 | INDEX SKIP SCAN | IDX10 | 1 | | 3 (0)||* 13 | TABLE ACCESS BY INDEX ROWID| T_CAPITAL_DISTRIBUTE | 1 | 52 | 2 (50)||* 14 | INDEX RANGE SCAN | IDX_CAPITAL_DISTR__CRED_ORGAN | 15 | | 2 (0)|| 15 | TABLE ACCESS BY INDEX ROWID | T_CONTRACT_PRODUCT | 1 | 10 | 2 (50)||* 16 | INDEX UNIQUE SCAN | PK_T_CONTRACT_PRODUCT | 1 | | 1 (0)|| 17 | TABLE ACCESS BY INDEX ROWID | T_PRODUCT_LIFE | 1 | 14 | 2 (50)||* 18 | INDEX UNIQUE SCAN | PK_T_PRODUCT_LIFE | 1 | | || 19 | TABLE ACCESS BY INDEX ROWID | T_CAPITAL_DISTRI_TYPE | 1 | 25 | 2 (50)||* 20 | INDEX UNIQUE SCAN | PK_T_CAPITAL_DISTRI_TYPE | 1 | | || 21 | TABLE ACCESS BY INDEX ROWID | T_CONTRACT_MASTER | 1 | 46 | 2 (50)||* 22 | INDEX UNIQUE SCAN | PK_T_CONTRACT_MASTER | 1 | | 1 (0)|| 23 | TABLE ACCESS BY INDEX ROWID | T_BIZ_ACCOUNTING_INFO | 1 | 131 | 2 (50)||* 24 | INDEX RANGE SCAN | IDX_BIZ_ACCOUNTING_INFO__FEE_ | 1 | | 2 (0)|| 25 | TABLE ACCESS BY INDEX ROWID | T_COMPANY_ORGAN | 1 | 36 | 2 (50)||* 26 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN | 1 | | || 27 | TABLE ACCESS BY INDEX ROWID | T_CHANNEL_TYPE | 1 | 12 | 2 (50)||* 28 | INDEX UNIQUE SCAN | PK_T_CHANNEL_TYPE | 1 | | || 29 | VIEW | | 7 | 42 | ||* 30 | CONNECT BY WITH FILTERING | | | | || 31 | NESTED LOOPS | | | | ||* 32 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN | 1 | 6 | || 33 | TABLE ACCESS BY USER ROWID | T_COMPANY_ORGAN | | | || 34 | NESTED LOOPS | | | | || 35 | BUFFER SORT | | 7 | 70 | || 36 | CONNECT BY PUMP | | | | ||* 37 | INDEX RANGE SCAN | T_COMPANY_ORGAN_IDX_002 | 7 | 70 | 1 (0)|------------------------------------------------------------------------------------------------------------- 2 - access("I"."SEGMENT1"="O"."ORGAN_ID"(+)) 12 - access("I"."TRANSACTION_DATE">=TO_DATE('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "I"."POSTING_ID"=493997 AND "I"."TRANSACTION_DATE"<TO_DATE('2011-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter("I"."POSTING_ID"=493997) 13 - filter("A"."DISTRI_TYPE"="I"."REFERENCE3") 14 - access("A"."CRED_ID"=493997 AND "A"."ORGAN_ID"="I"."SEGMENT1") filter(NVL("A"."POSTED",'N')='Y') 16 - access("A"."ITEM_ID"="F"."ITEM_ID"(+)) 18 - access("A"."PRODUCT_ID"="E"."PRODUCT_ID") 20 - access("A"."DISTRI_TYPE"="D"."DISTRI_TYPE") 22 - access("A"."POLICY_ID"="B"."POLICY_ID") 24 - access("A"."CAPITAL_ID"="T_BIZ_ACCOUNTING_INFO"."FEE_ID"(+) AND "T_BIZ_ACCOUNTING_INFO"."DATA_TABLE"(+)=7) 26 - access("B"."ORGAN_ID"="C"."ORGAN_ID") 28 - access("B"."POLICY_TYPE"="J"."INDIVIDUAL_GROUP") 30 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101') 32 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101') 37 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)65 rows selected.
从执行计划上去看,这个SQL基本上没有技术上可以进一步优化的地方了,你可能会说这里不应该走INDEX SKIP SCAN ,应该走INDEX RANGE SCAN
但是这个都是小问题,它不是决定性因素,SQL 优化从技术上 不可行之后,就应该立马分析业务,请仔细观察这个SQL
它有很多的外连接,外连接很特殊,因为外连接的驱动表的顺序是固定的 比如
a left join b 那么 a就只能做驱动表(不管是走nested loops outer 或者hash join outer) 你没办法更改驱动表的顺序,哪怕你用leading ,order hint都不行
正是因为这个SQL里面有很多外连接,SQL的访问顺序给固定死了,所以没办法从技术上调优SQL了
我让盖尔把外连接的(+) 去掉,跑一下SQL ,SQL只需要30秒就能跑完 ,执行计划如下
----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 354 | 27 (0)|| 1 | SORT UNIQUE | | 1 | 354 | 27 (0)|| 2 | TABLE ACCESS BY INDEX ROWID | T_BIZ_ACCOUNTING_INFO | 1 | 131 | 2 (50)|| 3 | NESTED LOOPS | | 1 | 354 | 12 (9)|| 4 | NESTED LOOPS | | 1 | 223 | 11 (10)|| 5 | NESTED LOOPS | | 1 | 209 | 10 (10)|| 6 | NESTED LOOPS | | 1 | 199 | 9 (12)|| 7 | NESTED LOOPS | | 1 | 174 | 8 (13)|| 8 | NESTED LOOPS | | 1 | 138 | 7 (15)|| 9 | NESTED LOOPS | | 1 | 126 | 6 (17)||* 10 | HASH JOIN | | 1 | 80 | 5 (20)|| 11 | TABLE ACCESS BY INDEX ROWID | T_CAPITAL_DISTRIBUTE | 1 | 50 | 2 (50)|| 12 | NESTED LOOPS | | 1 | 74 | 3 (34)|| 13 | TABLE ACCESS BY INDEX ROWID | T_GL_BIZ_INTERFACE | 1 | 24 | 2 (50)||* 14 | INDEX SKIP SCAN | IDX10 | 1 | | 3 (0)||* 15 | INDEX RANGE SCAN | IDX14 | 1 | | 2 (0)|| 16 | VIEW | | 7 | 42 | ||* 17 | FILTER | | | | ||* 18 | CONNECT BY WITH FILTERING | | | | || 19 | NESTED LOOPS | | | | ||* 20 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN | 1 | 6 | || 21 | TABLE ACCESS BY USER ROWID| T_COMPANY_ORGAN | | | || 22 | NESTED LOOPS | | | | || 23 | BUFFER SORT | | 7 | 70 | || 24 | CONNECT BY PUMP | | | | ||* 25 | INDEX RANGE SCAN | T_COMPANY_ORGAN_IDX_002 | 7 | 70 | 1 (0)|| 26 | TABLE ACCESS BY INDEX ROWID | T_CONTRACT_MASTER | 1 | 46 | 2 (50)||* 27 | INDEX UNIQUE SCAN | PK_T_CONTRACT_MASTER | 1 | | 1 (0)|| 28 | TABLE ACCESS BY INDEX ROWID | T_CHANNEL_TYPE | 1 | 12 | 2 (50)||* 29 | INDEX UNIQUE SCAN | PK_T_CHANNEL_TYPE | 1 | | || 30 | TABLE ACCESS BY INDEX ROWID | T_COMPANY_ORGAN | 1 | 36 | 2 (50)||* 31 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN | 1 | | || 32 | TABLE ACCESS BY INDEX ROWID | T_CAPITAL_DISTRI_TYPE | 1 | 25 | 2 (50)||* 33 | INDEX UNIQUE SCAN | PK_T_CAPITAL_DISTRI_TYPE | 1 | | || 34 | TABLE ACCESS BY INDEX ROWID | T_CONTRACT_PRODUCT | 1 | 10 | 2 (50)||* 35 | INDEX UNIQUE SCAN | PK_T_CONTRACT_PRODUCT | 1 | | 1 (0)|| 36 | TABLE ACCESS BY INDEX ROWID | T_PRODUCT_LIFE | 1 | 14 | 2 (50)||* 37 | INDEX UNIQUE SCAN | PK_T_PRODUCT_LIFE | 1 | | ||* 38 | INDEX RANGE SCAN | IDX_BIZ_ACCOUNTING_INFO__FEE_ | 1 | | 2 (0)|----------------------------------------------------------------------------------------------------------------10 - access("I"."SEGMENT1"="O"."ORGAN_ID") 14 - access("I"."TRANSACTION_DATE">=TO_DATE('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "I"."POSTING_ID"=493997 AND "I"."TRANSACTION_DATE"<TO_DATE('2011-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter("I"."POSTING_ID"=493997) 15 - access("A"."CRED_ID"=493997 AND "A"."ORGAN_ID"="I"."SEGMENT1" AND "A"."DISTRI_TYPE"="I"."REFERENCE3") filter(NVL("A"."POSTED",'N')='Y' AND "A"."DISTRI_TYPE"="I"."REFERENCE3") 17 - filter(TO_DATE('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<TO_DATE('2011-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2011-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')>TO_DATE('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 18 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101') 20 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101') 25 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL) 27 - access("A"."POLICY_ID"="B"."POLICY_ID") 29 - access("B"."POLICY_TYPE"="J"."INDIVIDUAL_GROUP") 31 - access("B"."ORGAN_ID"="C"."ORGAN_ID") 33 - access("A"."DISTRI_TYPE"="D"."DISTRI_TYPE") 35 - access("A"."ITEM_ID"="F"."ITEM_ID") 37 - access("A"."PRODUCT_ID"="E"."PRODUCT_ID") 38 - access("A"."CAPITAL_ID"="T_BIZ_ACCOUNTING_INFO"."FEE_ID" AND "T_BIZ_ACCOUNTING_INFO"."DATA_TABLE"=7)
所以系统设计的时候,应该尽量避免出现 left outer join, right outer join ,尤其是大表,大表更应该尽量避它作为外连接的驱动表
- 系统设计时应尽量避免出现left outer join, right outer join
- left outer join right outer join
- inner join&left outer join&right outer join
- inner join&left outer join&right outer join 区别
- left outer join,inner join,right outer join的区别
- inner join&left outer join&right outer join
- inner join, left outer join, right outer join
- cross join & natural join & inner join & left outer join & right outer join & full outer join
- JOIN用法 INNER JOIN/LEFT JOIN/RIGHT JOIN/OUTER JOIN
- Linq语句实现(INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、CROSS JOIN)
- left outer join or right outer join次数过多出现的后果
- SQL中 inner join、 left outer join 、right outer join、 full outer join之间的区别
- inner join,outer join,left join,right join的区别
- inner join,outer join,left join,right join的区别
- inner join,outer join,left join,right join的区别
- join left join right join outer join 以及笛卡儿积
- left join , right join , full outer join , inner join
- left join,right join,inner join,outer join的用法
- 盘点2011年度四大艳照门事件
- 双缓冲为什么应该这样用?
- 一维最接近点对的分治解法
- 利用ZooKeeper的Java API来创建一个znode
- php文件上传类
- 系统设计时应尽量避免出现left outer join, right outer join
- C# 上传附件 删除附件
- CButtonST的应用
- php生成缩微图类
- WWW的工作原理
- android repo 切换分支
- 12306自动售票助手(管用)
- 杂谈
- php缓存类