sql 语句如何优化

Question

select a. *, m.amount
  from tableA  a,
       (
       select b.fieldD, sum(c.total_amount) amount
           from tableA b, tableB c
          where b.fieldC = 100 and
                b.fieldA in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF') and
                b.fieldId = c.fieldId
          group by b.fieldD
          ) m
 where a.fieldC = 100 and a.fieldD = m.fieldD and
       a.fieldA = 'GG'       
这句sql当中对同一个表扫描了两次,所以效率太低,有什么办法可以避免这种写法?
tableA,tableB 是主从表关系。
请不要用sql server 中太特殊的语法，因为要用到oracle中。
在oracle中无人回答。

Answer 1

tableA , tableB 都在百万级要5分钟才能出来。客户不能忍受。

Answer 2

select a. *, m.amount
  from tableA  a,
       (
       select b.fieldD, sum(c.total_amount) amount
           from tableA b, tableB c
          where b.fieldC = 100 and
                b.fieldA in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF') and
                b.fieldId = c.fieldId
          group by b.fieldD
          ) m
 where a.fieldC = 100 and a.fieldD = m.fieldD and
       a.fieldA = 'GG'       
这句sql当中对同一个表扫描了两次,所以效率太低,有什么办法可以避免这种写法?
tableA,tableB 是主从表关系。
请不要用sql server 中太特殊的语法，因为要用到oracle中。
在oracle中无人回答。------------------------------------------SQL语句的写法是根据你的业务要求，改写起来效果不能很明显。先分析一下你的SQL的执行路径：1、
首先会分别对tableA和tableB应用filter动作（使用m子查询中的where条件）。然后进行连接，可能会是nestloop或hash join...这取决于你的两个表数据过滤情况。然后进行汇总（group by）输出m结果集。2、接下来会将m结果集与tableA（外层）过滤后（a.fieldC = 100 and a.fieldA = 'GG'）的结果集进行连接，还是有多种连接方式。最后输出a. *, m.amount大致分析了一下执行的路径，就会对你的描述产生疑惑：“对同一个表扫描了两次”肯定指的是tableA了。但是你没有建立相关的索引吗？如果说外层的查询就算建立索引也会通过rowid定位到表中，我们权当这是“表扫描”，但是内层的查询应该不会发生产生表扫描（all table access）的情况！应该是索引扫描（index scan）才对。根据这一点，我们可以首先考虑建立索引来提高效率。可以考虑建立的索引：create index idx_1 on tableA(fieldC,fieldA,fieldId,fieldD)create index idx_2 on tableB(fieldId,total_amount)
建立完这两个索引后别忘了重新执行分析，以保证统计值准确。建立完这两个索引后，内层的执行计划应该是对idx_1和idx_2进行索引扫描（index scan）然后连接输出m结果集，再与外层的经过索引扫描（index scan + rowid to table）的结果集进行连接。如果查询计划不对，请检查你的优化器参数设置，不要使用rbo要使用cbo。如果还是没有采用请用/* index*/提示强制指定....
上面的是单纯从索引方面考虑。如果还是不能提高速度，考虑建立实体化视图（物化视图）。可以只将m部分进行实体化。如果tableA和tableB基本属于静态表，可以考虑将整条语句实体化。

Answer 3

select a. *, m.amount
  from tableA  a,
       (
       select b.fieldD, sum(c.total_amount) amount
           from tableA b, tableB c
          where b.fieldC = 100 and
                b.fieldA in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF') and   --这里限制了tableA.fieldA in('AA', 'BB', 'CC', 'DD', 'EE', 'FF')这里面没有'GG',所以m中应该没有'GG'的fileID吧
                b.fieldId = c.fieldId
          group by b.fieldD
          ) m
 where a.fieldC = 100 and a.fieldD = m.fieldD and
       a.fieldA = 'GG'    --这里却要求 tableA.fieldA = 'GG' 所以tableA 和m相连接，应该没有数据吧。

Answer 4

vovo2000(没人要的猫) (--这里限制了tableA.fieldA in('AA', 'BB', 'CC', 'DD', 'EE', 'FF')这里面没有'GG',所以m中应该没有'GG'的fileID吧
－－－－－－－－－－－－－－－－－－－－－－－
是不是有點矛盾？？這個因好象得不到這個果。一個是fieldA ，一個是fileID。

Answer 5

我想办法模拟一下场景：
tableA:
ta_id cust_id status_desc campaign_plan_id
1 001 OCC 100
2 001 2M 100
3 002 2M 100
4 003 OCC 100
5 003 OCC 100
6 001 2M 100tableB:
tb_id ta_id product_id total_amount
1 2 005 200.00
2 3 006 600.00
3 6 009 900.00
tableA和tableB是主从表关系。tableB的ta_id引用tableA的主键ta_id。
tableA的ta_id和tableB的tb_id是主键，是oracle的sequence。其他的想cust_id(客户编号) 等都有索引，但不是唯一索引。把"OCC"叫作预期登记，把"2M"叫作正式登记。一般正式登记的客户会购买产品，记入tableB，从下面的数据中可以看出 001客户他买了
200.00+900.00=1100.00 （因为tableB中的ta_id =2,6 对应到tableA中是2,6）
那我现在想知道作过预期登记的(OCC)的这些客户当中哪些最终真的作正式登记了，并且要知道正式登记情况下他购买的产品金额是多少。于是呼：
select a. *, m.amount
  from tableA  a,
       (
       select b.cust_id, sum(c.total_amount) amount
           from tableA b, tableB c
          where b.campaign_plan_id = 100 and
                b.status_desc in ('2M', '12+', '12-') and
                b.ta_id = c.ta_id
          group by b.cust_id
          ) m
 where a.camapign_plan_id = 100 and a.cust_id = m.cust_id and
       a.status_desc = 'OCC'       
其中 '12+', '12-'也和'2M'一样的性质。现在就是对tableA，自身关联了以下，导致速度慢下来了，这应该是算法不好的原因。
希望老大们有好注意。

Answer 6

to zjcxc(邹建)  老大。
如果有特殊的写法也好。那我看看能不能有oracle相对应的写法。

Answer 7

表的结构重发一下.
tableA:
ta_id cust_id status_desc campaign_plan_id
1 001 OCC 100
2 001 2M 100
3 002 2M 100
4 003 OCC 100
5 003 OCC 100
6 001 2M 100tableB:
tb_id ta_id product_id total_amount
1 2 005 200.00
2 3 006 600.00
3 6 009 900.00

Answer 8

Oracle支持分区表，对大表分区如何？

Answer 9

领教了，to zjcxc(邹建) ，ashzs((可以包含中文字符)) ，收教了，高人！！我佩服了

Answer 10

首先，你以前建立的索引使用上了吗？你要看看执行计划！如果没有用上，是没有作用的！
在oracle中启用执行计划：
SQL> @?/rdbms/admin/utlxplan.sql
SQL> @?/sqlplus/admin/plustrce.sql 
SQL> grant plustrace to public
SQL> set autotrace on
SQL> your sql...其次，看了你的表结构。感觉可以从tableA下点功夫。
2M、OCC、12+、12-这几种记录可能只是在各自的记录时间上不同，其他的字段信息大体都应该是相同的，status_desc这个字段最好拆成多个字段（根据你的登记状态的种类：2M、OCC、12+、12-）这样将会使多条记录合成一条！然后将登记的更改时间（如果需要的话）分成几个字段。这样将大幅度的减少记录数，较少连接查询时的循环次数，提高效率！第二种需要变更幅度较大，建议还是通过索引来提高效率。

Answer 11

要确认你的Oracle用索引了么？
有些情况下是不用索引的，即使你建立了索引。
我知道涉及隐性类型转换时候索引就不管用。

Answer 12

to ashzs((可以包含中文字符)) 索引该用的都用上了，索引也不是越多越好的。
//status_desc这个字段最好拆成多个字段（根据你的登记状态的种类：2M、OCC、12+、12-）这样将会使多条记录合成一条！呵呵~~，是啊，能这样真的好啊。sql是好写了，但是以后有新的状态出来，要再加字段吗?因为我不是设计人员。表结构一般不能动。如果为了这个查询改表结构会牵动很多人的代码要变动，代价会很大。

Answer 13

因为从你的描述看，ａ．＊实际是预约客户的所有信息，但是一般来说预约客户的所有信息和它对应的正式客户的所有信息应该是正式客户的所有信息更准确也许你的语句可以简化为       select b.fieldD,..., sum(c.total_amount) amount
           from tableA b, tableB c
          where b.fieldC = 100 and
                b.fieldA in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF') and
                b.fieldId = c.fieldId
                and exists (
                 select 1 from tableA a 
                 where a.fieldC = 100 and a.fieldD = b.fieldD and
                  a.fieldA = 'GG'       
                )
          group by b.fieldD,...

Answer 14

我说的是说
“那我现在想知道作过预期登记的(OCC)的这些客户当中哪些最终真的作正式登记了，并且要知道正式登记情况下他购买的产品金额是多少。
”能否变换成“那我现在想知道正式登记的客户当中哪些作过预期登记的(OCC)，并且要知道正式登记情况下他购买的产品金额是多少。
”这里换了主体，关键是能不能换，换过来的效率我想应该能高些，不知道oracle对exists的处理机制能否支持

Answer 15

Yang_(扬帆破浪) 有道理。 exists 可以试一下。但group by b.fieldD,...会让金额细化了。我只要金额按cust_id 来group by 就可以了。还是对我有启发的。

Answer 16

oracle 里可以
select b.fieldD,..., sum(c.total_amount) amount
...
          group by b.fieldD
吗？可以这样就方便些