cc的sql优化实战

coffeesweet

浏览: 414634 次
性别:
来自: 北京

最近访客更多访客>>

康敏栋

沉默的水

ptkr

xubukang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

sql
oracle
mysql

sql oracle 优化分析

最近帮同事优化了一个SQL，优化前66秒，优化后0.8秒，优化的过程中总结了一些常用的需要注意的事项，

没有时间整理格式，自己留个记录,附件是原始SQL和优化后SQL脚本。

1:拿到原SQL，第一是先运行一下，看看运行时间状况和执行计划，是否有全表扫描的情况。

发现没有全表的情况，首先大致排除索引使用的问题（索引情况很多，这里只是大致排除，因为

前提是知道查询源表数据量很大，而查询结果很小，理论上应该必须使用索引才能提高效率）。

2:开始考虑源数据量的问题。

先分析原有SQL的逻辑目的，即它究竟是想干什么，从哪些表里查询，查询什么条件的什么数据？

查看逻辑的经验是从SQL里层往外剥，一层一层执行（SQL执行的大致原理也是先内后外），

注意这里可能需要调整原有SQL的条件限制，目的只是查看它的查询逻辑，而不是优化调整。

那么看到里层第一个能执行的SQL如下：

①

select (select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_YDJHCJ') C_YDJHCJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_JHCJHJ') C_JHCJHJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ') C_XYWCJHJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ') C_XYWSYZWCJHJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_KZCZCJ') C_KZCZCJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ') C_SF_XYDJCJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = '_C_CJZE') C_CJZE,

d.region_code

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33)

这里有人可能要说了，不对啊，最里层第一个SQL语句类似的是：

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

适当调整一下也能执行啊，是，调整一下的确能执行，分析一下也行，该SQL大致是

查询channel.c_award表中符合and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

的数据，至于where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

这两个条件就需要看外层的SQL了，那么来解释一下外层的SQL①，首先是确定数据源

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

然后是过滤数据

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33)

最后是解析表达式

select (select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_YDJHCJ') C_YDJHCJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_JHCJHJ') C_JHCJHJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ') C_XYWCJHJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ') C_XYWSYZWCJHJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_KZCZCJ') C_KZCZCJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ') C_SF_XYDJCJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = '_C_CJZE') C_CJZE,

d.region_code

说解析表达式可能不太明白，那就说最后是执行SELECT（注：如果有ORDER BY的话，会在SELECT后执行）。

①这条SQL在pl/sql developer里执行显示结果很快，但要全部查询显示结果需要27秒左右，说明该查询

结果条目数大（9012条）。

但是条目数大也不应该这么慢，SELECT * FROM channel_dept;查询出13518条数据也就需要11秒左右，

这说明肯定还有其它原因，那么仔细看①SQL语句的SELECT的内容，发现，每一个表达式都是一个SELECT语句，一共有

7个SELECT作为最终的查询表达式来一个一个解析。

具体到这个SQL，也就是说在

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33)

数据源中每查询一条数据出来都需要和①SQL里SELECT里的每一个表达式字段关联再执行该字段对应的自己的

SQL语句（拿出7个中的一个做例子）

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_YDJHCJ') C_YDJHCJ,

最后才能跳出符合条件的表达式结果（即查询结果字段），也就是说每一条符合条件的数据源都要在经过7次

表达式的SELECT查询逻辑，但是这个7个SELECT查询逻辑虽然是严格的内连接过滤查询，只查询出一条符合的记录（速度应该会很快），

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1--这里的1也可以用c.state代替

and ca.element_code = 'C_YDJHCJ'

【因为分析channel.c_award表字段可知：字段channel_id，state，cycle_id，element_code联合唯一（具体业务

是不是先不管了，那是题外话）】

而且最后查询出一个字段作为①SQL的一个查询结果表达式字段，理论上和查询单个表达式字段一样，没有很大区别啊。

确实，单独执行以下SQL速度很快：0.047秒（因为太符合采用索引的条件了，大数据源，小结果，channel.c_award有168万数据，只查询出一条记录）

⑨select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ';

即使是7个这样的SQL叠加也不到半秒啊，那么性能问题是不是不在这里，哪里出了问题呢？？

先看看下面这些例子：

②SELECT (select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33) ;

这条SQL大概要5-7秒，

③SELECT (select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33) ;

这条SQL需要1.7秒左右，

④SELECT (select 1 from dual ) C_YDJHCJ

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33) ;

这条SQL也是1.7秒左右

⑤SELECT 1 C_YDJHCJ

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33) ;

这条SQL也是1.7秒左右

注意看②-⑤条SQL的区别，

第②条SQL和外层表进行了关联，③和④都没有关联，④把表达式SELECT的表变成了dual伪表，

⑤直接去掉了表达式SELECT，用字面量1代替

但③④⑤的效率一样，

【注：③之所以能和④，⑤效率差不多完全是因为③查询条件的每个字段都采用了index，从大数量中

搂出小数据，由此可见INDEX的强大。】

由此可见表达式的SELECT和外层数据源之间的关联才是性能消耗的原因，这就解释了之前分析⑨SQL时的疑惑了，

性能不是出在单表SQL上，而是内外层关联上，再加上7个字段都是这种内外层关联，一个5-7秒，加起来大概35秒，接近27秒的原因了，

那么到底是内外层关联影响大，还是字段子查询个数的影响大呢？？

看看这个例子：

SELECT (

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ,

(

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ2

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33) ;

需要2.1秒左右，比一个字段的SELECT多了0.4秒，

SELECT (

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ,

(

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ2,

(

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ3

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33) ;

需要2.3秒，比两个字段的SQL又多了0.2秒，

可以发现增加字段的SELECT确实影响了效率，但由于都没有和外层关联，所以影响微乎其微。

再看下面几个：

SELECT (select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ,

(

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ2,

(

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ3

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33) ;

花了5.8秒，

SELECT (select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ,

(

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ2,

(

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ3

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33) ;

这个花了10.9秒（如果是3个都和外层关联是14.2秒），由此可见和外层关联是影响效率的主要因素，如有有N个字段，只有其中

一个字段和外层关联，那么其它N-1个字段对效率的影响可以忽略，如果再加一个字段也和外层关联，

那么这个效率影响就是一个字段和外层关联的两倍，以此类推。

那么这里的7个SELECT字段难道就不能只和外层数据源关联一次吗？？这样不就节省掉了6/7的时间？？？

再仔细观察②-⑤条SQL查询的结果都是9012条数据，都和⑤的结果条目数一致，也就是说和外层数据源结果

的条目数一致，和每个表达式SELECT的条目数无关。

以下是证明：

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ';

结果是1条，

而

SELECT (

select ca.award_amount

from channel.c_award ca

where ca.cycle_id = 201109

and ca.channel_id = 10045

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

) C_YDJHCJ

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33) ;

仍然是9012条，和外层的条目数一致。

那这是在干什么，有关联关系，想想？？想想外连接，对啊，这不就和外连接类似吗？？

左+号已右表数据条数为准，右+号已左表条目数为准，而SELECT不是也能当做FROM 的数据源吗？？

想到这里就可以进行修改了，将①SQL修改为

⑥SELECT C_DMLX.channel_id,C_DMLX.region_code,/*C_DMLX.AWARD_AMOUNT,*/

C_YDJHCJ.AWARD_AMOUNT C_YDJHCJ,

C_JHCJHJ.AWARD_AMOUNT C_JHCJHJ,

C_XYWCJHJ.AWARD_AMOUNT C_XYWCJHJ,

C_XYWSYZWCJHJ.AWARD_AMOUNT C_XYWSYZWCJHJ,

C_KZCZCJ.AWARD_AMOUNT C_KZCZCJ,

C_SF_XYDJCJ.AWARD_AMOUNT C_SF_XYDJCJ,

C_CJZE.AWARD_AMOUNT C_CJZE

FROM

(

SELECT d.channel_id,d.region_code,c.cycle_id,c.award_amount

FROM CHANNEL.C_AWARD C,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D

WHERE C.CYCLE_ID = A.BILL_CYCLE

AND C.STATE = 1

AND C.CHANNEL_ID = D.CHANNEL_ID

AND D.STATE = 1

AND C.ELEMENT_CODE = 'C_DMLX'

/*AND C.AMOUNT IN (311, 312, 313, 32, 33)*/

) C_DMLX,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

AND ca.award_amount<>0

) C_YDJHCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_JHCJHJ'

AND ca.award_amount<>0

) C_JHCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ'

AND ca.award_amount<>0

) C_XYWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ'

AND ca.award_amount<>0

) C_XYWSYZWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_KZCZCJ'

AND ca.award_amount<>0

) C_KZCZCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ'

AND ca.award_amount<>0

) C_SF_XYDJCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = '_C_CJZE'

AND ca.award_amount<>0

) C_CJZE/*,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D*/

WHERE

C_DMLX.CHANNEL_ID=C_YDJHCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_JHCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWSYZWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_KZCZCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_SF_XYDJCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_CJZE.Channel_Id(+)

执行时间为4.7秒左右，乘以7=32秒左右和原始①SQL执行时间27秒接近，而结果一样，达到目的。

那么这4.7秒也很慢啊，这是什么原因啊？

那么现在就来分析⑥SQL，这里是以C_DMLX为主表，其它表没有数据就补空，C_DMLX主表有9012条数据。

整个执行时间4.7秒，如果确定主表只查询出一条数据呢？？稍微修改一下⑥SQL变为下面

⑦SELECT C_DMLX.channel_id,C_DMLX.region_code,/*C_DMLX.AWARD_AMOUNT,*/

C_YDJHCJ.AWARD_AMOUNT C_YDJHCJ,

C_JHCJHJ.AWARD_AMOUNT C_JHCJHJ,

C_XYWCJHJ.AWARD_AMOUNT C_XYWCJHJ,

C_XYWSYZWCJHJ.AWARD_AMOUNT C_XYWSYZWCJHJ,

C_KZCZCJ.AWARD_AMOUNT C_KZCZCJ,

C_SF_XYDJCJ.AWARD_AMOUNT C_SF_XYDJCJ,

C_CJZE.AWARD_AMOUNT C_CJZE

FROM

(

SELECT d.channel_id,d.region_code,c.cycle_id,c.award_amount

FROM CHANNEL.C_AWARD C,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D

WHERE C.CYCLE_ID = A.BILL_CYCLE

AND C.STATE = 1

AND C.CHANNEL_ID = D.CHANNEL_ID

AND D.STATE = 1

AND C.ELEMENT_CODE = 'C_DMLX'

/*AND C.AMOUNT IN (311, 312, 313, 32, 33)*/

) C_DMLX,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

AND ca.award_amount<>0

) C_YDJHCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_JHCJHJ'

AND ca.award_amount<>0

) C_JHCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ'

AND ca.award_amount<>0

) C_XYWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ'

AND ca.award_amount<>0

) C_XYWSYZWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_KZCZCJ'

AND ca.award_amount<>0

) C_KZCZCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ'

AND ca.award_amount<>0

) C_SF_XYDJCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = '_C_CJZE'

AND ca.award_amount<>0

) C_CJZE/*,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D*/

WHERE

C_DMLX.CHANNEL_ID=C_YDJHCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_JHCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWSYZWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_KZCZCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_SF_XYDJCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_CJZE.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=102096;//添加的条件

执行一下，天啊，只需要0.172秒，再多执行几次也一样，不敢相信自己的眼睛吧，

⑦SQL就添加了AND C_DMLX.CHANNEL_ID=102096用于过滤出一条数据，c_award表有168万数据，

从中查询9012条数据需要4.7秒，查询一条数据需要0.172秒，都用了索引，而索引的使用原则是

大数据量中查询小结果。【这页正验证了这个原理，不信的话你可以把条件都去掉，直接查询168万数据

出来，估计你等不到结果，这样查询大数据量的话不用索引反而比用索引快，你可以线下自己试试，

至于为什么，自己去查索引的相关知识吧。】

那么就来考虑这9012条数据是否都有用，其中每一条数据对最终的结果都会产生影响？？？

是否可以把对结果不会产生影响的数据在where里给过滤掉，这样查询结果集就会变小，速度也会提升。

【用土话说就是PLSQL/DEVELOPER在查询结果大数据量时列出默认的数据和列出所有结果数据时间肯定不一样，

时间就浪费在列出所有数据上，就是点击那个两个向下箭头带一个横线的图标。（其实这是PLSQL/DEVELOPER的

优化，先缓存一屏幕数据出来，你要看全部了点击按钮它再取出所有需要的数据。那么真正该SQL执行所需要的

时间就是列出所有数据所需要的时间）】

要想知道⑥SQL查询出的9012条数据是否全部有用，就需要往外层看，看外层SQL拿这些数据干了些什么：

select sum(nvl(C_YDJHCJ, 0)) C_YDJHCJ,

sum(nvl(C_JHCJHJ, 0)) C_JHCJHJ,

sum(nvl(C_XYWCJHJ, 0)) C_XYWCJHJ,

sum(nvl(C_XYWSYZWCJHJ, 0)) C_XYWSYZWCJHJ,

sum(nvl(C_KZCZCJ, 0)) C_KZCZCJ,

sum(nvl(C_SF_XYDJCJ, 0)) C_SF_XYDJCJ,

sum(nvl(C_CJZE, 0)) C_CJZE,

region_code

from (select (select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_YDJHCJ') C_YDJHCJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_JHCJHJ') C_JHCJHJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ') C_XYWCJHJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ') C_XYWSYZWCJHJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_KZCZCJ') C_KZCZCJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ') C_SF_XYDJCJ,

(select ca.award_amount

from channel.c_award ca

where ca.cycle_id = c.cycle_id

and ca.channel_id = c.channel_id

and ca.state = 1

and ca.element_code = '_C_CJZE') C_CJZE,

d.region_code

from channel.c_award c,

channel.C_AWARD_BILL_CYCLE a,

channel.channel_dept d

where c.cycle_id = a.bill_cycle

and c.state = 1

and c.channel_id = d.channel_id

and d.state = 1

and c.element_code = 'C_DMLX'

and c.amount in (311, 312, 313, 32, 33))

group by region_code

这个SQL是我们以上分析完毕的①SQL的外层SQL，我们看它的查询结果字段表达式都是SUM处理，还有空值转换处理，

也就是说是想把内层①SQL查询出来的9012条数据按照region_code字段分组，其它7个字段各自在行分组内按行求和，

注意是行求和，不是列求和。

那么除了region_code的其它需要求和的7个字段都是（0或空）的数据就对最后SUM的结果没有任何影响了。

那这些数据就可以先过滤掉了，使⑥SQL（⑥SQL是我们用于替换①SQL的优化后的SQL，可以看做等价，但效率比①快6/7）

查询结果减少，又不影响最终外层的结果。

符合7个字段都是（0或空）的数据有多少呢？？

⑧SELECT C_DMLX.channel_id

FROM

(

SELECT d.channel_id,d.region_code,c.cycle_id,c.award_amount

FROM CHANNEL.C_AWARD C,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D

WHERE C.CYCLE_ID = A.BILL_CYCLE

AND C.STATE = 1

AND C.CHANNEL_ID = D.CHANNEL_ID

AND D.STATE = 1

AND C.ELEMENT_CODE = 'C_DMLX'

/*AND C.AMOUNT IN (311, 312, 313, 32, 33)*/

) C_DMLX,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

AND ca.award_amount<>0

) C_YDJHCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_JHCJHJ'

AND ca.award_amount<>0

) C_JHCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ'

AND ca.award_amount<>0

) C_XYWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ'

AND ca.award_amount<>0

) C_XYWSYZWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_KZCZCJ'

AND ca.award_amount<>0

) C_KZCZCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ'

AND ca.award_amount<>0

) C_SF_XYDJCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = '_C_CJZE'

AND ca.award_amount<>0

) C_CJZE/*,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D*/

WHERE

C_DMLX.CHANNEL_ID=C_YDJHCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_JHCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWSYZWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_KZCZCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_SF_XYDJCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_CJZE.Channel_Id(+)

AND C_YDJHCJ.award_amount IS NULL

AND C_JHCJHJ.award_amount IS NULL

AND C_XYWCJHJ.award_amount IS NULL

AND C_XYWSYZWCJHJ.award_amount IS NULL

AND C_KZCZCJ.award_amount IS NULL

AND C_SF_XYDJCJ.award_amount IS NULL

AND C_CJZE.award_amount IS NULL

查询结果为8982条，耗时2.094秒，仔细观察上面的SQL，为什么在7个FROM的数据源中SELECT时就加上

AND ca.award_amount<>0的条件，而不是再最外面添加不为0的判断呢？？

由于这7个ca.award_amount的值很不统一，有的为0值，有的为空值，假如直接在最后的条件中添加过滤

的话，需要添加成类似AND （C_YDJHCJ.award_amount=0 OR C_YDJHCJ.award_amount IS NULL），这样明显

就复杂了，效率上会不会有影响我没试，首先感觉就不清爽，肯定也快不了。

因为反正是外连接，在最后查询结果中显示0和空值对结果的影响又都一样，索性就先在每个子数据源里将为0的数据过滤掉，

这样每个子数据源的结果数据量肯定有所减小，和主表关联也会少，速度会提升，而最后有外连接做保证，没有关联数据的拿空行补齐，

相当于字面量操作，不需从关联表中查询结果填充，又节省了时间（我很抠门儿啊。。。）

这样就相当于把所有0的数据都变成了Null的数据，外面过滤直接统一用null判断就行了，不用再关注0的情况了，

数据正确性也能保证。何乐而不为呢？？嘿嘿！

那有人又要问了，为什么只在子数据源中添加AND ca.award_amount<>0，如果把AND ca.award_amount IS NOT NULL

添加上不是更好？因为会过滤掉更多的数据。变成类似下面的写法

select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

AND ca.award_amount<>0

AND ca.award_amount IS NOT NULL

是，其实理论上这样会过滤掉更多无效数据，但假如某个子数据源中的数据是三种情况：

1：ca.award_amount字段0和空值都用空值来表现

2：ca.award_amount字段0和空值都用0来表现

3：ca.award_amount字段0代表该条数据在该字段上有意义，只是结果为0，空值代表该条数据在该字段上没有意义

【举例：某家店面有A项酬金，但酬金值为0，和某家店面直接没有参与过A酬金是不一样的意义的。】

那么就需要看每个具体子数据源的该字段当初的定义了，如果确定

为2情况，那么只需要添加AND ca.award_amount<>0就行了，不需要额外添加没有意义的AND ca.award_amount IS NOT NULL

。如果确定是1的情况，那么只需要添加AND ca.award_amount IS NOT NULL，而不需要额外添加没有意义的AND ca.award_amount<>0，

如果该字段是3这种情况的话，只能两个条件都加上了。

那具体到现在的场景到底这里该这么添加条件呢？因为开发人员对于数据操作很不规范，

这7个子数据源的字段没法确定到底是哪种情况，所以只能用最保险的第三种情况的条件了，就是两个都加上，

但我为什么只加了个过滤0的条件呢？其实这里就看实际的测试了，我把两个条件都加上如下：

SELECT C_DMLX.channel_id

FROM

(

SELECT d.channel_id,d.region_code,c.cycle_id,c.award_amount

FROM CHANNEL.C_AWARD C,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D

WHERE C.CYCLE_ID = A.BILL_CYCLE

AND C.STATE = 1

AND C.CHANNEL_ID = D.CHANNEL_ID

AND D.STATE = 1

AND C.ELEMENT_CODE = 'C_DMLX'

/*AND C.AMOUNT IN (311, 312, 313, 32, 33)*/

) C_DMLX,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

AND ca.award_amount<>0

AND ca.award_amount IS NOT NULL

) C_YDJHCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_JHCJHJ'

AND ca.award_amount<>0

AND ca.award_amount IS NOT NULL

) C_JHCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ'

AND ca.award_amount<>0

AND ca.award_amount IS NOT NULL

) C_XYWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ'

AND ca.award_amount<>0

AND ca.award_amount IS NOT NULL

) C_XYWSYZWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_KZCZCJ'

AND ca.award_amount<>0

AND ca.award_amount IS NOT NULL

) C_KZCZCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ'

AND ca.award_amount<>0

AND ca.award_amount IS NOT NULL

) C_SF_XYDJCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = '_C_CJZE'

AND ca.award_amount<>0

AND ca.award_amount IS NOT NULL

) C_CJZE/*,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D*/

WHERE

C_DMLX.CHANNEL_ID=C_YDJHCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_JHCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWSYZWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_KZCZCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_SF_XYDJCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_CJZE.Channel_Id(+)

AND C_YDJHCJ.award_amount IS NULL

AND C_JHCJHJ.award_amount IS NULL

AND C_XYWCJHJ.award_amount IS NULL

AND C_XYWSYZWCJHJ.award_amount IS NULL

AND C_KZCZCJ.award_amount IS NULL

AND C_SF_XYDJCJ.award_amount IS NULL

AND C_CJZE.award_amount IS NULL

查询时间是2.094秒左右浮动，比只加AND ca.award_amount<>0的2.094秒一样，多执行几次也都几乎一样，

为什么会这样，其实好好想想，外关联从子数据源关联出一个空值字段，和自己补充一个空值字段能差多少呢？

关键是只有一个字段，又不是很多，而且关联查询出的值和关联不上自己需要补充的值一样，都是空值，

ORACLE对于这种情况肯定有所优化，如果这个字段不空，又或者需要关联出子数据源的很多个字段出来，肯定

会有影响，这里只有空值，还就一个字段，所以不用考虑影响了。

那么说明没有说明区别，所以这里分析到就行了，如果两种执行结果差距很大，再来具体分析有说明问题。

由于两种写法的执行时间一样，不管里面添不添加空判断，外层条件的写法都一样，都需要添加空判断，时间还一样，那还不如里面

不添加AND ca.award_amount IS NOT NULL条件了，还能减小掉SQL得字符数量，让ORACLE解析SQL语句快一点了。

嘿嘿！

其实上面的分析也说明一开始对字段好的规范定义多么重要，由于IS NULL 和 IS NOT NULL都不能使用索引，

所以考虑在设计表的时候就对索引列设置为NOT NULL约束。

好了，

现在先YY一下，全量数据为9012条，其中里面有8982条没用的数据，

剩下30条才是最后决定结果的有用数据，坑爹呢。。。如果把这8982条数据去掉，最后只显示30条有用的数据，

那效率肯定会有质的提高。

要怎么把他们去掉呢？？？第一个想到的就是not in，对，⑦SQL not in ⑧就是想要的结果了，关联字段是

channel_id。

这样是没有问题，但经验告诉我们not in效率低下，为什么低下，因为它会使索引失效，类似于笛卡尔集的遍历。

但channel_id是有索引的啊，这么利用它得到和not in 一样的结果呢？？

-----外连接加Null判断-----这是个替换not in的最常用的方法，

也就是⑦.channel_id=⑧.channel_id(+) and ⑧.channel_id is null

①SQL变为⑥SQL最终优化为下面的SQL

⑩SELECT q.*

FROM

(

SELECT C_DMLX.channel_id,C_DMLX.region_code,/*C_DMLX.AWARD_AMOUNT,*/

nvl(C_YDJHCJ.AWARD_AMOUNT,0) C_YDJHCJ,

nvl(C_JHCJHJ.AWARD_AMOUNT,0) C_JHCJHJ,

nvl(C_XYWCJHJ.AWARD_AMOUNT,0) C_XYWCJHJ,

nvl(C_XYWSYZWCJHJ.AWARD_AMOUNT,0) C_XYWSYZWCJHJ,

nvl(C_KZCZCJ.AWARD_AMOUNT,0) C_KZCZCJ,

nvl(C_SF_XYDJCJ.AWARD_AMOUNT,0) C_SF_XYDJCJ,

nvl(C_CJZE.AWARD_AMOUNT,0) C_CJZE

FROM

(

SELECT d.channel_id,d.region_code,c.cycle_id,c.award_amount

FROM CHANNEL.C_AWARD C,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D

WHERE C.CYCLE_ID = A.BILL_CYCLE

AND C.STATE = 1

AND C.CHANNEL_ID = D.CHANNEL_ID

AND D.STATE = 1

AND C.ELEMENT_CODE = 'C_DMLX'

/*AND C.AMOUNT IN (311, 312, 313, 32, 33)*/

) C_DMLX,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

AND ca.award_amount<>0

) C_YDJHCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_JHCJHJ'

AND ca.award_amount<>0

) C_JHCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ'

AND ca.award_amount<>0

) C_XYWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ'

AND ca.award_amount<>0

) C_XYWSYZWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_KZCZCJ'

AND ca.award_amount<>0

) C_KZCZCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ'

AND ca.award_amount<>0

) C_SF_XYDJCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = '_C_CJZE'

AND ca.award_amount<>0

) C_CJZE/*,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D*/

WHERE

C_DMLX.CHANNEL_ID=C_YDJHCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_JHCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWSYZWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_KZCZCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_SF_XYDJCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_CJZE.Channel_Id(+)

) q,

(

SELECT C_DMLX.channel_id

FROM

(

SELECT d.channel_id,d.region_code,c.cycle_id,c.award_amount

FROM CHANNEL.C_AWARD C,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D

WHERE C.CYCLE_ID = A.BILL_CYCLE

AND C.STATE = 1

AND C.CHANNEL_ID = D.CHANNEL_ID

AND D.STATE = 1

AND C.ELEMENT_CODE = 'C_DMLX'

/*AND C.AMOUNT IN (311, 312, 313, 32, 33)*/

) C_DMLX,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

AND ca.award_amount<>0

) C_YDJHCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_JHCJHJ'

AND ca.award_amount<>0

) C_JHCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ'

AND ca.award_amount<>0

) C_XYWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ'

AND ca.award_amount<>0

) C_XYWSYZWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_KZCZCJ'

AND ca.award_amount<>0

) C_KZCZCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ'

AND ca.award_amount<>0

) C_SF_XYDJCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = '_C_CJZE'

AND ca.award_amount<>0

) C_CJZE/*,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D*/

WHERE

C_DMLX.CHANNEL_ID=C_YDJHCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_JHCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWSYZWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_KZCZCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_SF_XYDJCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_CJZE.Channel_Id(+)

AND C_YDJHCJ.award_amount IS NULL

AND C_JHCJHJ.award_amount IS NULL

AND C_XYWCJHJ.award_amount IS NULL

AND C_XYWSYZWCJHJ.award_amount IS NULL

AND C_KZCZCJ.award_amount IS NULL

AND C_SF_XYDJCJ.award_amount IS NULL

AND C_CJZE.award_amount IS NULL

) c

WHERE q.channel_id=c.channel_id(+)

AND c.channel_id IS NULL

该SQL看着很长，但执行起来只需要0.734秒，比⑥SQL查询9012条数据的4.7秒提升了84.4%的查询时间，

这个时间是相当能够接受的了，看来快达到目的了，不要着急，原始SQL的最外还有一层没有分析，

大概看一下，就是一些普通的对内层SQL查询结果字段之间的简单的加减操作，是列之间的加减，由于内层

查询结果只有30条，每条做一次列之间的加减时间消耗可以忽略了，而且这些列字段都是直接取结果，不涉及到

关联查询表达式了，有关列的个数和行的个数哪个对查询结果有较大影响前面分析过了，这里就不再讨论了。

好了，最终优化的SQL就出炉了

SELECT region_code,

sum(C_YDJHCJ) C_YDJHCJ,

sum(C_JHCJHJ) C_JHCJHJ,

sum(C_XYWCJHJ)+SUM(C_XYWSYZWCJHJ) C_XYWCJHJ,

/*sum(C_XYWSYZWCJHJ),*/

sum(C_KZCZCJ) C_KZCZCJ,

sum(C_SF_XYDJCJ) C_SF_XYDJCJ,

sum(C_CJZE)-sum(C_YDJHCJ)-sum(C_JHCJHJ)-sum(C_XYWCJHJ)-SUM(C_XYWSYZWCJHJ)-sum(C_KZCZCJ)-sum(C_SF_XYDJCJ) C_QT FROM

(

SELECT q.*

FROM

(

SELECT C_DMLX.channel_id,C_DMLX.region_code,/*C_DMLX.AWARD_AMOUNT,*/

nvl(C_YDJHCJ.AWARD_AMOUNT,0) C_YDJHCJ,

nvl(C_JHCJHJ.AWARD_AMOUNT,0) C_JHCJHJ,

nvl(C_XYWCJHJ.AWARD_AMOUNT,0) C_XYWCJHJ,

nvl(C_XYWSYZWCJHJ.AWARD_AMOUNT,0) C_XYWSYZWCJHJ,

nvl(C_KZCZCJ.AWARD_AMOUNT,0) C_KZCZCJ,

nvl(C_SF_XYDJCJ.AWARD_AMOUNT,0) C_SF_XYDJCJ,

nvl(C_CJZE.AWARD_AMOUNT,0) C_CJZE

FROM

(

SELECT d.channel_id,d.region_code,c.cycle_id,c.award_amount

FROM CHANNEL.C_AWARD C,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D

WHERE C.CYCLE_ID = A.BILL_CYCLE

AND C.STATE = 1

AND C.CHANNEL_ID = D.CHANNEL_ID

AND D.STATE = 1

AND C.ELEMENT_CODE = 'C_DMLX'

/*AND C.AMOUNT IN (311, 312, 313, 32, 33)*/

) C_DMLX,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

AND ca.award_amount<>0

) C_YDJHCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_JHCJHJ'

AND ca.award_amount<>0

) C_JHCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ'

AND ca.award_amount<>0

) C_XYWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ'

AND ca.award_amount<>0

) C_XYWSYZWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_KZCZCJ'

AND ca.award_amount<>0

) C_KZCZCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ'

AND ca.award_amount<>0

) C_SF_XYDJCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = '_C_CJZE'

AND ca.award_amount<>0

) C_CJZE/*,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D*/

WHERE

C_DMLX.CHANNEL_ID=C_YDJHCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_JHCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWSYZWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_KZCZCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_SF_XYDJCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_CJZE.Channel_Id(+)

) q,

(

SELECT C_DMLX.channel_id

FROM

(

SELECT d.channel_id,d.region_code,c.cycle_id,c.award_amount

FROM CHANNEL.C_AWARD C,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D

WHERE C.CYCLE_ID = A.BILL_CYCLE

AND C.STATE = 1

AND C.CHANNEL_ID = D.CHANNEL_ID

AND D.STATE = 1

AND C.ELEMENT_CODE = 'C_DMLX'

/*AND C.AMOUNT IN (311, 312, 313, 32, 33)*/

) C_DMLX,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_YDJHCJ'

AND ca.award_amount<>0

) C_YDJHCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_JHCJHJ'

AND ca.award_amount<>0

) C_JHCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWCJHJ'

AND ca.award_amount<>0

) C_XYWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_XYWSYZWCJHJ'

AND ca.award_amount<>0

) C_XYWSYZWCJHJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_KZCZCJ'

AND ca.award_amount<>0

) C_KZCZCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = 'C_SF_XYDJCJ'

AND ca.award_amount<>0

) C_SF_XYDJCJ,

( select ca.channel_id,ca.award_amount

from channel.c_award ca,

CHANNEL.C_AWARD_BILL_CYCLE A

where ca.cycle_id = A.BILL_CYCLE

and ca.state = 1

and ca.element_code = '_C_CJZE'

AND ca.award_amount<>0

) C_CJZE/*,

CHANNEL.C_AWARD_BILL_CYCLE A,

CHANNEL.CHANNEL_DEPT D*/

WHERE

C_DMLX.CHANNEL_ID=C_YDJHCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_JHCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_XYWSYZWCJHJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_KZCZCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_SF_XYDJCJ.Channel_Id(+)

AND C_DMLX.CHANNEL_ID=C_CJZE.Channel_Id(+)

AND C_YDJHCJ.award_amount IS NULL

AND C_JHCJHJ.award_amount IS NULL

AND C_XYWCJHJ.award_amount IS NULL

AND C_XYWSYZWCJHJ.award_amount IS NULL

AND C_KZCZCJ.award_amount IS NULL

AND C_SF_XYDJCJ.award_amount IS NULL

AND C_CJZE.award_amount IS NULL

) c

WHERE q.channel_id=c.channel_id(+)

AND c.channel_id IS NULL

)

group by region_code;

耗时0.718秒，比原始SQL查询的一分多钟提高了多少自己算吧。。。嘿嘿！！！

--------------------------------------------------------------------------

以上只是从纯粹的SQL语法上进行的分析优化，仔细的人可能观察到了，我最终优化的SQL把原始

SQL中的一些条件给注释掉了，比如主数据源C_DMLX中的C_AWARD C表的AND C.AMOUNT IN (311, 312, 313, 32, 33)

条件给注释掉了，不是说条件限制的越死，查询结果越少，速度越快吗？？理论上没错，但需要看具体业务，

这里和业务人员沟通后的结论是C_AWARD标中不可能出现AMOUNT字段不在(311, 312, 313, 32, 33)范围内的数据，

除非是人员手动修改数据，修改后就是错误数据，所有相关业务都会错误，所以这个IN的条件根本不需要添加，

IN不能使用索引，即使能用索引，额外添加了一个每条数据都肯定成立的WHERE条件，ORACLE对每条数据还得加个这个没必要的判断，

当然是浪费性能了。所以果断去掉（其实最客观的原因是我见到类似IN,NOT IN这样的关键字就烦，能不用尽量不用）。

那为什么不连AND C.ELEMENT_CODE = 'C_DMLX'等等都去掉呢？？？？

这个嘛。。。我就无语了。。。

需要添加哪些，去掉哪些条件是需要看该SQL业务需求的。

现在解释一下原SQL的业务意思，

查询出所有C_AWARD表中具有ELEMENT_CODE = 'C_DMLX'的并且AMOUNT IN (311, 312, 313, 32, 33)所有渠道(channel_id)，9012条

【上面说过AMOUNT IN (311, 312, 313, 32, 33)没有意义，其它STATE什么的限制就不说了】

然后查询出这9012个渠道的所有那7个ELEMENT_CODE 的酬金钱数，没有某个ELEMENT_CODE 的需要补空，

最后将这9012条记录按照分公司分组统计7个酬金的钱数。

优化的业务思想：

明确了统计范围是以ELEMENT_CODE = 'C_DMLX'的所有店面为准，即9012条数据，那么就将其拿出来作为主表和其它7项

酬金进行外关联，由于最后是求和，那么去掉所有7个酬金都为（0或空）即对SUM结果没有影响的数据，剩下的

数据按照分公司分组统计即可，这样减少了需要统计的源数据条数，可以提高速度。

所以优化不仅要关注SQL语法，还要关注具体的业务，看哪些数据有用，哪些没用，尽量减小每一层SQL需要查询结果条数，

即：尽量往INDEX的原则靠，大数据量里查询极少数据结果出来。

另外去掉一些完全没必要的条件限制。

有时候要添加条件，有时候要减少条件，很烦人啊，哈哈。。。

--------------------------------------------------------------------------

总结一下SQL优化注意点：

1：尽量往INDEX的原则靠拢，大数量查极小数据

2：注意避免使用一些不能使用索引的关键字，函数等

3：SELECT 作为查询表达式存在时，如果和主表有关联关系，那么整体SQL的效率和SELECT查询表达式的个数

成正比。总时间=N*(单个关联SELECT查询表达式执行时间)

优化方式是将SELECT的查询表达式转换为FROM的关联数据源，将原有的外层表作为关联数据源的主表，一起

做外连接。

两种方式都实现了补伪行的目的，但后者时间消耗是一定的，不会随着SELECT的数据源个数增加而增加，

总时间=单个关联SELECT查询表达式执行时间

4：SELECT语句的列数目和结果行数目对执行时间的影响分情况而定，如果查询列不是关联的SELECT表达式的话，

列数目对时间的影响基本忽略，时间主要体现在查询结果的行数上。

5：关注SQL业务，尽量去掉对结果没有任何影响的中间结果（中间变量），即：子查询结果。

6：SELECT的执行顺序

①from字句组装来至不同数据源的数据

②where字句基于指定的条件对记录行进行筛选

③group by字句将数据划分为多个分组

④使用聚集函数进行计算

⑤使用having字句筛选分组

⑥计算所有表达式(即：select后面的字段)

⑦使用order by 字句对结果集进行排序

其中join是在from中执行的，所以提前于where字句

sql.rar (1.4 KB)
下载次数: 4

分享到：

2011-11-11 16:53
浏览 1117
评论(0)
论坛回复 / 浏览 (0 / 2138)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论