优化笔记: 此两个产品每天8点30分开始，要跑一个小时，看看是否有提升空间
性能瓶颈应该是在1014表的扫描上面。后面的代码是jxjjFXRXX_S.gz产品优化过的。替换后应该会有不小提升。
----------------------------------想进一步研究的，继续往下看-------------------------------------------------------------------------------------
1.with里面 对1429进行了两次全表扫描，可以通过用unpivot合并成一次。（最爱啊，11g终于支持了）
2.对1734表扫了多次，还好都走了索引。（但是扫多次索引也让人看着有点不爽）。可以尝试下把1734提取出来，然后join到from里面，这样就可以只扫1次。
with pj as
(
Select a.f2_1734,Max(F5_1734), max(f4_1735)
From wind.tb_object_1734 a
Where --F2_1734 = f34_1018 and
a.f4_1734 in ('AAA',
'AAA-',
'AA+',
'AA',
'AA-',
'A+',
'A',
'A-',
'BBB+',
'BBB',
'BBB-',
'BB+',
'BB',
'BB-',
'B+',
'B',
'B-',
'CCC+',
'CCC',
'CCC-',
'CC',
'C')
And a.F5_1734 = (Select Max(b.f5_1734)
From wind.tb_object_1734 b
Where b.f2_1734 = a.f2_1734 --10g的问题！
and b.f4_1734 = a.f4_1734)
group by a.f2_1734
)
这个方案有两个比较担心的地方：
记得在哪里看到过，oracle 10g的优化器对带有group的子查询优化的不是很好，会把连接条件放到group by后面，这样的话，就会对1734来次全扫描。这就要衡量下1734全扫描效率高还是多次扫索引效率高了。而sqlserver2005和mysql5.1版本之后，会把连接条件放到group by之前，这样的话，整个过程就只需要扫一次索引。
10g还有个问题，select (select oo from table1 b where b.id=a.id and b.zz=(select zz from table1 c where c.id=b.id)) xx from table1 a 这样的话，优化器是优化不了的。要写成这样子select (select oo from table1 b where b.id=a.id and b.zz=(select zz from table1 c where c.id=a.id)) xx from table1 a。而10g的这个问题，担心会影响到上面的那种方案。
3.绝大多数的情形下，join的效率比子查询高。所以sqlserver的优化器，碰到子查询就会尝试优化成join（oracle应该也差不多）。但是少数时候，子查询效率确实比join高，所以我们要人为把优化器搞蒙。目前在sqlserver2008上试验过，在子查询里面加 distinct top都可以把优化器搞蒙（没有任何官方依据，纯粹自己手动测试的，不保证其他版本也是这样）。
而我们的这个查询，在扫1014表的外面又套了个substr，很有可能也会把优化器搞蒙，这样就会出现最遭的情况，扫7000*16万的数据。经过修改的代码，也会扫7000次索引。
如果再进一步，把这个子查询手动改成join的形式，就可以只扫1次索引了。
4.等哪天有兴致，再去搭个11g环境把各种猜想都测试一下吧，期待11g能修复上面两个问题。
with T as
(select ob_revisions_1090 comid
from wind.tb_object_1090
where f4_1090 In ('A', 'B')
union
/* SELECT f47_1429 comid
From wind.tb_object_1429
union SELECT f40_1429 comid
From wind.tb_object_1429*/
select comid
from wind.tb_object_1429 unpivot(comid for col in(f40_1429, f47_1429)) --11g新功能，把两个1429扫描合并成一个。
minus
Select f12_1099 comid From wind.tb_object_1099)
Select Distinct f34_1018 FXRH,
Case
When ob_object_name_1018 Like '%银行%' or
f41_1018 in (11, 19, 29, 46) Then
'2'
when f41_1018 = 1 then
'7'
when f41_1018 in (4, 5, 6, 7, 8, 42, 43, 44, 45) then
'8'
When ob_object_name_1018 Like '%公司%' then
'1'
Else
'4'
End FXFL,
substrb(nvl(f50_1018, ob_object_name_1018), 1, 32) FXMC,
ob_object_name_1018 FXQC,
substr((Select ob_object_name_1014 --这个地方只需要7000左右数据，却全表扫描了（至少扫了16万，最坏的情况会扫7000*16万的数据。看不到主库执行计划，暂时无法判断到底扫了多少）
From wind.tb_object_1014 a
Where --f29_1018 = '1014:' || a.ob_object_id -- a.ob_object_id本来有唯一索引，这样写，反而不会走索引
f29_1018 like '1014:%'
and a.ob_object_id = substr(f29_1018, 6)), --这样可以走索引（原则：不要在有索引的字段上用函数，拼接啊之类的操作。一旦用了，优化器就不认识了）
1,
16) FXLXR,
substrb(f36_1018, 1, 32) FXRLXDH,
substrb(f37_1018, 1, 32) FXRCZ,
'' JBRXM,
'' JBRZJLX,
'' JBRZJHM,
'' JBRLXDH,
'' JBRCZ,
'' JBRQZWJM,
'' YLQZWJM,
(Select Round(f140_1853, 2)
From wind.tb_object_1853 a
Where f1_1853 = f34_1018
And f4_1853 = '合并报表'
And f2_1853 =
(Select Max(b.f2_1853)
From wind.tb_object_1853 b
Where b.f1_1853 = f34_1018
And b.f4_1853 = '合并报表'
And substr(b.f2_1853, 5, 4) = '1231')) FXZC,
'99990101' JBYX,
decode((Select Max(f4_1734)
From wind.tb_object_1734 a
Where f34_1018 = f2_1734
And f5_1734 =
(Select Max(f5_1734)
From wind.tb_object_1734 b
Where b.f2_1734 = f34_1018)),
'AAA',
'1',
'AAA-',
'2',
'AA+',
'3',
'AA',
'4',
'AA-',
'5',
'A+',
'6',
'A',
'7',
'A-',
'8',
'BBB+',
'9',
'BBB',
'A',
'BBB-',
'B',
'BB+',
'C',
'BB',
'D',
'BB-',
'E',
'B+',
'F',
'B',
'G',
'B-',
'H',
'CCC+',
'I',
'CCC',
'J',
'CCC-',
'K',
'CC',
'L',
'C',
'M') WBPJ,
'' NBPJ,
'' ZHPJ,
'' SXED,
'CN' FXGJ,
'CNY' ZCBZ,
DECODE((Select Max(f4_1734)
From wind.tb_object_1734 a
Where f34_1018 = f2_1734
And f5_1734 =
(Select Max(f5_1734)
From wind.tb_object_1734 b
Where b.f2_1734 = f34_1018)),
'AAA',
'1',
'AAA-',
'2',
'AA+',
'3',
'AA',
'4',
'AA-',
'5',
'A',
'6',
'BBB',
'7',
'BB',
'8',
'B',
'9') DBPJ,
(Select Max(F5_1734)
From wind.tb_object_1734 a
Where F2_1734 = f34_1018
and f4_1734 in ('AAA',
'AAA-',
'AA+',
'AA',
'AA-',
'A+',
'A',
'A-',
'BBB+',
'BBB',
'BBB-',
'BB+',
'BB',
'BB-',
'B+',
'B',
'B-',
'CCC+',
'CCC',
'CCC-',
'CC',
'C')
And F5_1734 = (Select Max(f5_1734)
From wind.tb_object_1734 b
Where b.f2_1734 = f34_1018
and b.f4_1734 = a.f4_1734)) WBPJRQ
From wind.tb_object_1018 a
inner join T
on T.comid = F34_1018
Union ALL
Select f34_1018,
case
when f41_1018 = 18 THEN
'3'
when f41_1018 = 1 then
'7'
ELSE
'1'
END,
case
when f41_1018 = 18 THEN
substr(ob_object_name_1018,
1,
instr(ob_object_name_1018, '基金') + 1)
ELSE
ob_object_name_1018
END,
ob_object_name_1018,
substr((Select ob_object_name_1014
From wind.tb_object_1014 a
Where --f29_1018 = '1014:' || a.ob_object_id --同上
f29_1018 like '1014:%'
and a.ob_object_id = substr(f29_1018, 6)),
1,
16),
substrb(f36_1018, 1, 32),
substrb(f37_1018, 1, 32),
'',
'',
'',
'',
'',
'',
'',
to_number(''),
'99990101' JBYX,
'' WBPJ,
'' NBPJ,
'' ZHPJ,
'' SXED,
'CN' FXGJ,
'CNY' ZCBZ,
NULL DBPJ,
null WBPJRQ
From wind.tb_object_1018 a
Where f34_1018 In (Select f12_1099 From wind.tb_object_1099)
发件人: Dai zhenyu
发送时间: 2014年9月24日 8:56
收件人: Zhang XuTong; Wang GuoDong
抄送: Op.Alert.Datafeed2; Liu XiJun; OP.Alert.DataFeed3
主题: 答复: 此两个产品每天8点30分开始，要跑一个小时，看看是否有提升空间
重要性: 高
请wang guodong和zhang xutong配合优化脚本。
发件人: Liu XiJun
发送时间: 2014年9月24日 8:52
收件人: OP.Alert.DataFeed3; Zhang XuTong
抄送: Op.Alert.Datafeed2
主题: 此两个产品每天8点30分开始，要跑一个小时，看看是否有提升空间
08:30:00 65分 0 jxjjFXRXX_S.gz
08:30:00 62分 0 zmddhfxrxx_S.gz
产品及时服务：请在Wind资讯终端上按“F1”键或致电客服专线400-820-Wind(9463)
---------------------------------------------------------------------------------------------
刘 洗 俊 BUR产品部
上海万得信息技术股份有限公司（Wind资讯）
Shanghai Wind Information Co., Ltd.
|
|
上海市浦东新区福山路33号建工大厦9楼 200120
|
