十万火急，帮忙看一下我的SQL汇总哪里有问题，为何速度如此之慢！

Question

测试背景：
windowsserver2003 32位
Intel Xeon E7520 1.87GHz
内存31.8G
oracle10g10.2.0默认安装
数据库中rp_people_bak_s记录数118万条，执行以下SQL语句，时间达到5分钟！select substr(t1.addresscode,1,9) dzm,0 zhs,0 djrk,count(*) sjrs,0 hjrk,0 czrk,0 s1,sum(case when r6_1='1' and r7_1='1' then 1 else 0 end) s2,sum(case when r6_1='1' and r7_1='2' then 1 else 0 end) s3,sum(case when r6_1='1' and (r7_1='3' or r7_1='4') then 1 else 0 end) s4, sum(case when r6_1='1' and (r7_1='3' or r7_1='4') and r8='2' then 1 else 0 end) s5,sum(case when r6_1='1' and (r7_1='3' or r7_1='4') and r8>='3' then 1 else 0 end) s6,sum(case when r6_1='1' and r7_1='5' then 1 else 0 end) s7,0 s8,sum(case when r7_1='1' and r6_1='2' then 1 else 0 end) s9,sum(case when r7_1='1' and r6_1='3' then 1 else 0 end) s10,sum(case when r7_1='1' and (r6_1='4' or r6_1='5') then 1 else 0 end) s11,sum(case when r7_1='1' and (r6_1='4' or r6_1='5') and r8='2' then 1 else 0 end) s12,sum(case when r7_1='1' and (r6_1='4' or r6_1='5') and r8>='3' then 1 else 0 end) s13,sum(case when r7_1='1' and r6_1='6' then 1 else 0 end) s14,0 csrk, sum(case when ((r6_1='1' and (r7_1='1' or r7_1='2' or r7_1='5') )or (r6_1='1' and (r7_1='3' or r7_1='4') and r8>='3') or (r7_1='1' and (r6_1='4' or r6_1='5') and r8='2') or (r7_1='1' and r6_1='6')) and (r4_1='2009' and r4_2>'10' or  r4_1='2010' and r4_2<'11') then 1 else 0 end) csrk1, 0 swrk, sum(case when r6_1='1' and r7_1='1' then 1 else 0 end) x1,sum(case when r6_1='1' and r7_1='2' and r8='2' then 1 else 0 end) x2,sum(case when r6_1='1' and r7_1='2' and r8>='3' then 1 else 0 end) x3,sum(case when r6_1='1' and r7_1='3' and r8='2' then 1 else 0 end) x4,sum(case when r6_1='1' and r7_1='3' and r8>='3' then 1 else 0 end) x5,sum(case when r6_1='1' and r7_1='4' and r8='2' and substr(r7_2,1,4)<>substr(addresscode,1,4) then 1 else 0 end) x6,sum(case when r6_1='1' and r7_1='4' and r8='2' and substr(r7_2,1,2)<>substr(addresscode,1,2) then 1 else 0 end) x7,sum(case when r6_1='1' and r7_1='4' and r8>='3' and substr(r7_2,1,4)<>substr(addresscode,1,4) then 1 else 0 end) x8,sum(case when r6_1='1' and r7_1='4' and r8>='3' and substr(r7_2,1,2)<>substr(addresscode,1,2) then 1 else 0 end) x9,sum(case when r6_1='1' and r7_1='5' then 1 else 0 end) x10,sum(case when r6_1='2' and r7_1='1' and r8='2' then 1 else 0 end) x11,sum(case when r6_1='2' and r7_1='1' and r8>='3' then 1 else 0 end) x12,sum(case when r6_1='3' and r7_1='1' and r8='2' then 1 else 0 end) x13,sum(case when r6_1='3' and r7_1='1' and r8>='3' then 1 else 0 end) x14,sum(case when r6_1='4' and r7_1='1' and r8='2' then 1 else 0 end) x15,sum(case when r6_1='4' and r7_1='1' and r8>='3' then 1 else 0 end) x16,sum(case when r6_1='5' and r7_1='1' and r8='2' and substr(r6_2,1,4)<>substr(addresscode,1,4) then 1 else 0 end) x17,sum(case when r6_1='5' and r7_1='1' and r8='2' and substr(r6_2,1,2)<>substr(addresscode,1,2) then 1 else 0 end) x18,sum(case when r6_1='5' and r7_1='1' and r8>='3' and substr(r6_2,1,4)<>substr(addresscode,1,4) then 1 else 0 end) x19,sum(case when r6_1='5' and r7_1='1' and r8>='3' and substr(r6_2,1,2)<>substr(addresscode,1,2) then 1 else 0 end) x20,sum(case when r6_1='6' and r7_1='1' and r8='2' then 1 else 0 end) x21,sum(case when r6_1='6' and r7_1='1' and r8>='3' then 1 else 0 end) x22 from rpgd.RP_PEOPLE_BAK_S t1 
where t1.addresscode like '370105%' group by substr(t1.addresscode,1,9) ORDER BY dzm但是相同的表，相同的数据量，相同的汇总语句，在SQLServer中执行只需要10秒左右！
请各位老大，帮帮忙吧，看看是我oracle的问题还是sql语句的问题，小生这厢跪谢了！

Answer 1

哇塞，楼主SQL好强大哦
那么多。
Oracle中你对表相应字段加上索引再试试嘛
分析下表的统计信息等

Answer 2

t1.addresscode like '370105%'把这个使用instr代替like ,然后在这个字段上建立一个函数索引，估计就可以了。。

Answer 3

问题是客户要求，只能查询，绝对不允许任何修改数据库的操作，而且也没给我这个权限。再一个，oracle中已对addresscode建立索引，而sqlserver里同样的表，既无主键，也无索引，速度却快那么多，太令我讶异了

Answer 4

我另外一个疑惑是，为什么sqlserver如此强大呢？

Answer 5

addresscode like '370105%'  改成
instr(addresscode,'370105',1)>0
 初看了下 有些函数处理的字段 可以建立函数索引

Answer 6

强悍的sql,帮你排排版,省的弟兄们晕
select substr(t1.addresscode, 1, 9) dzm,
       0 zhs,
       0 djrk,
       count(*) sjrs,
       0 hjrk,
       0 czrk,
       0 s1,
       sum(case
             when r6_1 = '1' and r7_1 = '1' then
              1
             else
              0
           end) s2,
       sum(case
             when r6_1 = '1' and r7_1 = '2' then
              1
             else
              0
           end) s3,
       sum(case
             when r6_1 = '1' and (r7_1 = '3' or r7_1 = '4') then
              1
             else
              0
           end) s4,
       sum(case
             when r6_1 = '1' and (r7_1 = '3' or r7_1 = '4') and r8 = '2' then
              1
             else
              0
           end) s5,
       sum(case
             when r6_1 = '1' and (r7_1 = '3' or r7_1 = '4') and r8 >= '3' then
              1
             else
              0
           end) s6,
       sum(case
             when r6_1 = '1' and r7_1 = '5' then
              1
             else
              0
           end) s7,
       0 s8,
       sum(case
             when r7_1 = '1' and r6_1 = '2' then
              1
             else
              0
           end) s9,
       sum(case
             when r7_1 = '1' and r6_1 = '3' then
              1
             else
              0
           end) s10,
       sum(case
             when r7_1 = '1' and (r6_1 = '4' or r6_1 = '5') then
              1
             else
              0
           end) s11,
       sum(case
             when r7_1 = '1' and (r6_1 = '4' or r6_1 = '5') and r8 = '2' then
              1
             else
              0
           end) s12,
       sum(case
             when r7_1 = '1' and (r6_1 = '4' or r6_1 = '5') and r8 >= '3' then
              1
             else
              0
           end) s13,
       sum(case
             when r7_1 = '1' and r6_1 = '6' then
              1
             else
              0
           end) s14,
       0 csrk,
       sum(case
             when ((r6_1 = '1' and (r7_1 = '1' or r7_1 = '2' or r7_1 = '5')) or
                  (r6_1 = '1' and (r7_1 = '3' or r7_1 = '4') and r8 >= '3') or
                  (r7_1 = '1' and (r6_1 = '4' or r6_1 = '5') and r8 = '2') or
                  (r7_1 = '1' and r6_1 = '6')) and
                  (r4_1 = '2009' and r4_2 > '10' or
                  r4_1 = '2010' and r4_2 < '11') then
              1
             else
              0
           end) csrk1,
       0 swrk,
       sum(case
             when r6_1 = '1' and r7_1 = '1' then
              1
             else
              0
           end) x1,
       sum(case
             when r6_1 = '1' and r7_1 = '2' and r8 = '2' then
              1
             else
              0
           end) x2,
       sum(case
             when r6_1 = '1' and r7_1 = '2' and r8 >= '3' then
              1
             else
              0
           end) x3,
       sum(case
             when r6_1 = '1' and r7_1 = '3' and r8 = '2' then
              1
             else
              0
           end) x4,
       sum(case
             when r6_1 = '1' and r7_1 = '3' and r8 >= '3' then
              1
             else
              0
           end) x5,
       sum(case
             when r6_1 = '1' and r7_1 = '4' and r8 = '2' and
                  substr(r7_2, 1, 4) <> substr(addresscode, 1, 4) then
              1
             else
              0
           end) x6,
       sum(case
             when r6_1 = '1' and r7_1 = '4' and r8 = '2' and
                  substr(r7_2, 1, 2) <> substr(addresscode, 1, 2) then
              1
             else
              0
           end) x7,
       sum(case
             when r6_1 = '1' and r7_1 = '4' and r8 >= '3' and
                  substr(r7_2, 1, 4) <> substr(addresscode, 1, 4) then
              1
             else
              0
           end) x8,
       sum(case
             when r6_1 = '1' and r7_1 = '4' and r8 >= '3' and
                  substr(r7_2, 1, 2) <> substr(addresscode, 1, 2) then
              1
             else
              0
           end) x9,
       sum(case
             when r6_1 = '1' and r7_1 = '5' then
              1
             else
              0
           end) x10,
       sum(case
             when r6_1 = '2' and r7_1 = '1' and r8 = '2' then
              1
             else
              0
           end) x11,
       sum(case
             when r6_1 = '2' and r7_1 = '1' and r8 >= '3' then
              1
             else
              0
           end) x12,
       sum(case
             when r6_1 = '3' and r7_1 = '1' and r8 = '2' then
              1
             else
              0
           end) x13,
       sum(case
             when r6_1 = '3' and r7_1 = '1' and r8 >= '3' then
              1
             else
              0
           end) x14,
       sum(case
             when r6_1 = '4' and r7_1 = '1' and r8 = '2' then
              1
             else
              0
           end) x15,
       sum(case
             when r6_1 = '4' and r7_1 = '1' and r8 >= '3' then
              1
             else
              0
           end) x16,
       sum(case
             when r6_1 = '5' and r7_1 = '1' and r8 = '2' and
                  substr(r6_2, 1, 4) <> substr(addresscode, 1, 4) then
              1
             else
              0
           end) x17,
       sum(case
             when r6_1 = '5' and r7_1 = '1' and r8 = '2' and
                  substr(r6_2, 1, 2) <> substr(addresscode, 1, 2) then
              1
             else
              0
           end) x18,
       sum(case
             when r6_1 = '5' and r7_1 = '1' and r8 >= '3' and
                  substr(r6_2, 1, 4) <> substr(addresscode, 1, 4) then
              1
             else
              0
           end) x19,
       sum(case
             when r6_1 = '5' and r7_1 = '1' and r8 >= '3' and
                  substr(r6_2, 1, 2) <> substr(addresscode, 1, 2) then
              1
             else
              0
           end) x20,
       sum(case
             when r6_1 = '6' and r7_1 = '1' and r8 = '2' then
              1
             else
              0
           end) x21,
       sum(case
             when r6_1 = '6' and r7_1 = '1' and r8 >= '3' then
              1
             else
              0
           end) x22
  from rpgd.RP_PEOPLE_BAK_S t1
 where t1.addresscode like '370105%'
 group by substr(t1.addresscode, 1, 9)
 ORDER BY dzm

Answer 7

r6_1 = '3' and r7_1 = '1' and r8 >= '3' 这些字段是什么类型 的 字符型 的？ 

Answer 8

换成instr没见到什么效果，因为addresscode上有索引，like右边的%应该没什么大问题。建了函数索引稍微好一些，但还是很慢，虽然不到两分钟，还是长！这才100多万数据，将来上千万了怎么办

Answer 9

因为addresscode上有索引，like右边的%应该没什么大问题用like的话是不会走索引的

Answer 10

建多个函数索引可以吗？因为addresscode是动态的截取的，分为4、6、9、12几种情况截取分组汇总。再一个，建索引不是一个很完美的解决办法，一是客户不会让我动那个，二是奇怪的是sqlserver既没索引也没优化，数据量300多万，也是10秒内出结果，难道是我oracle的配置有问题？有什么办法能够提高oracle全表扫描速度？

Answer 11

天哪，快救命啊，oracle查询为什么这么慢啊！！！！！要被解雇了，555

Answer 12

刚才在笔记本上做了测试，addresscode索引，共110多万条数据
select count(*) from rpgd.rp_people_bak_s where instr(addresscode,'370105',1)>0
执行结果796896，执行时间101秒，天内，这性能

Answer 13

挺简单的一个SQL,这么多内存，能用的有多少？

Answer 14

用OEM里面的SQL Advisor看一下，oracle给什么建议。

Answer 15

可能是建的表有问题！我另外建了一个表，只包含需要的字段，没有索引，数据量复制到400多万，查询速度却快得多，几秒钟！
我贴出来建表语句，帮我看看是哪里的问题！
create table RPGD.RP_PEOPLE_BAK_S
(
  ADDRESSCODE   NVARCHAR2(15),
  FAMILYID      NVARCHAR2(36),
  BATCHID       NVARCHAR2(36),
  DYNAMICFORMID NVARCHAR2(36),
  PEOPLEINDEX   NUMBER(1),
  INNERSEQ      NUMBER(2),
  H1            NVARCHAR2(3),
  R1_1          NVARCHAR2(5),
  R1_2          NVARCHAR2(5),
  R2            NVARCHAR2(1),
  R3            NVARCHAR2(1),
  R4_1          NVARCHAR2(4),
  R4_2          NVARCHAR2(2),
  R5            NVARCHAR2(2),
  R6_1          NVARCHAR2(1),
  R6_2          NVARCHAR2(6),
  R7_1          NVARCHAR2(1),
  R7_2          NVARCHAR2(6),
  R8            NVARCHAR2(1),
  R9            NVARCHAR2(1),
  R10           NVARCHAR2(1),
  R11           NVARCHAR2(1),
  R12           NVARCHAR2(1),
  ISFRONT       NUMBER(1),
  PAGENUMBER    NUMBER(1),
  EXP1          NUMBER(1),
  EXP2          NUMBER(1),
  EXP3          NUMBER(1),
  EXP4          NUMBER(1),
  EXP5          NUMBER(1),
  EXP6          NUMBER(1),
  EXP7          NUMBER(1),
  EXP8          NUMBER(1),
  EXP9          NUMBER(1),
  EXP10         NUMBER(1),
  EXP11         NUMBER(1),
  EXP12         NUMBER(1),
  EXP13         NUMBER(1),
  EXP14         NUMBER(1),
  EXP15         NUMBER(1),
  EXP16         NUMBER(1),
  EXP17         NUMBER(1),
  EXP18         NUMBER(1),
  EXP19         NUMBER(1),
  EXP20         NUMBER(1),
  EXP21         NUMBER(1),
  EXP22         NUMBER(1),
  EXP23         NUMBER(1),
  EXP24         NUMBER(1),
  EXP25         NUMBER(1),
  EXP26         NUMBER(1),
  EXP27         NUMBER(1),
  EXP28         NUMBER(1),
  EXP29         NUMBER(1),
  EXP30         NUMBER(4)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 496M
    minextents 1
    maxextents unlimited
  );
alter table RPGD.RP_PEOPLE_BAK_S
  add constraint FK_PEOPLE_BAK_S foreign key (ADDRESSCODE)
  references RPGD.RP_ADDRESSCODE (ADDRESSCODE);
alter table RPGD.RP_PEOPLE_BAK_S
  add constraint FK_PEOPLE_BAK_S2 foreign key (FAMILYID)
  references RPGD.RP_FAMILY_BAK_S (FAMILYID);

Answer 16

addresscode字段是什么类型的？varchar or varchar2？
“select count(*) from rpgd.rp_people_bak_s where instr(addresscode,'370105',1)>0
”中只有你的instr可能导致效率低了

Answer 17

anylyze之后count是快了，但汇总语句执行还是慢，toad rebuild table之后，执行速度暴涨。太奇怪了，oracle维护真不是一般的费劲

Answer 18

rebuild index?
table move?