4千万级，SQL求优化。

Question

 SELECT c.DM02NODE ,
        c.DM02ACCT ,
        c.dm02accs ,
        to_char(c.dm02opndat, 'yyyymmdd') ,
        ETL_GET_CSTM_NO(c.DM02ACCT) ,
        ETL_GET_CARD_NO(c.DM02ACCT) ,
        ETL_GET_ACCMDM(c.DM02ACCT) ,
        ETL_GET_ACCTYPE2(c.DM02ACCT) ,
        case
          when to_date('20090331', 'yyyy/mm/dd') >
               substr(c.dm02lstdte, 0, 9) then
           '1'
          else
           ''
        end,
        case
          when ETL_GET_ACCTYPE2(c.DM02ACCT) = '1' then
           etl_get_clsdat(c.DM02ACCT)
          else
           ''
        end,
        
        '结算户',
        c.DM02ACCT || c.dm02accs ,
        c.dm02bale,
        'ODS',
        '20120331',
        'MED',
        '20120331'
   FROM DPFM02 c
  where exists (select 1
           from sop_mir_dpfm01_a a
          where c.dm02acct = a.dm01acct
            and a.dm01clipro = '1' and c.DM02SBNO in ('200302'))DPFM02 和 sop_mir_dpfm01_a 各自都有4千多万的数据。
上面的函数，查询的表不太样，但都是单表查询，都是根据一个 帐号=帐号 拿出个我需要的字段。请各位大神给些意见，对待这样大数据量的SQL，应该如何写，才是最合理的呢？我利用这么多函数 好不好呢？
还有exists的应用合理吗？？？

Answer 1

2
 where exists (select 1
           from sop_mir_dpfm01_a a
          where c.dm02acct = a.dm01acct
            and a.dm01clipro = '1' and c.DM02SBNO in ('200302'))
大大们，而且我的where 条件  尤其是   c.DM02SBNO in ('200302') 这里。
这里暂时写的 只是一个科目，可是，真正的情况是会 判断至少15个科目，   where c.DM02SBNO in ('20040101',
                        '20040102',
                        '20040103',
                        '20040104',
                        '20040105',
                        '20040106',
                        '20040201',
                        '20040202',
                        '20040203',
                        '20040301',
                        '20040302',
                        '20040303',
                        '20040401',
                        '20040402',
                        '20040403',
                        '20040701',
                        '20040702',
                        '20040703',
                        '20050103',
                        '20050104',
                        '20050105',
                        '20050106',
                        '20050107',
                        '20050108',
                        '20050109',
                        '20050110',
                        '20050111',
                        '20050112',
                        '20050113',
                        '20050114',
                        '20050117',
                        '20050118',
                        '20050119')
当判断这种多科目的时候，这样写是不可以的吧？？？？速度太慢了吧

Answer 2

用in时，sql语句是用不到索引的，所以速度肯定慢

Answer 3

直接做表DPFM02 和 sop_mir_dpfm01_的  hash关联，不要使用exists。

Answer 4

c.DM02SBNO in ('200302')条件写在外层过滤，而且这字段重复率大的话，建位图索引可以会好点

Answer 5

或者将c.DM02SBNO in ('200302')条件再创建一个表，建索引，再两个表连接

Answer 6

where exists (select 1
           from sop_mir_dpfm01_a a
          where c.dm02acct = a.dm01acct
            and a.dm01clipro = '1' and c.DM02SBNO in ('200302'))
1.如果可能的话where子句中的两个谓词列考虑创建索引,这样子,连接条件上也存在索引,则表sop_mir_dpfm01_a只扫描索引即可提取所需数据.可以使用index_join提示.
2.考虑为两个谓词列中的一个存在索引,假定dm01clipro值取值范围小,考虑建位图索引.如果索引在DM02SBNO列,能否考虑改写 in 谓词为like. 那个in 之后的貌似也有些规律 ,如 like '200401%'
3.同样的问题是主查询中的这个substr(c.dm02lstdte, 0, 9)是9个位,这个有问题吧
   去掉substr这个转换,这个列应该本身是一个日期型吧case
          when to_date('20090331', 'yyyy/mm/dd') >
               substr(c.dm02lstdte, 0, 9) then
           '1'
          else
           ''
看下面的例子
scott@CNMMBO>  select case when hiredate<to_date('1981/12/31','yyyy/mm/dd') then '1' else '' end flag,empno,ename
  2  from emp where empno in (7369,7788);FLAG                      EMPNO ENAME
-------------------- ---------- ----------
1                          7369 SMITH
unknown                    7788 SCOTT    
4.尝试使用连接来替换exists,有些时候使用连接比exists高效
   

Answer 7

exists (select 1
           from sop_mir_dpfm01_a a
          where c.dm02acct = a.dm01acct
            and a.dm01clipro = '1' and c.DM02SBNO in ('200302'))如果  DM02SBNO in ('200302'))
 in 里的条件过多 可以先简历临时编码表 进行表连接 
 创建临时表 关于  sop_mir_dpfm01_a  查询后的结果  用
DPFM02  
和临时表关联如果可以有并行 加上并行  
4000W 级别的表 有表分区吗？？ 有加上看看

Answer 8

虽然还没真正用过Oracle，但是这几天看到书上一位大牛说过，当查询的时候应该注意函数的使用，因为如果函数使用不当会导致建立在某个字段上的函数失效，这样的情况下应该转换查询方式

Answer 9

不知道这样行不行，楼主可以测试一下是否可以提高查询速度
select * from (
select jgbm,cfh from sjpt_cfls ,row_number() over(partition by jgbm,cfh from sjpt_cfls order by jgbm,cfh from sjpt_cfls)
where HANDLETIME between to_date('2012-04-01','yyyy-mm-dd') and to_date('2012-04-12','yyyy-mm-dd') and kss=2  ) rw
where rw =1  

Answer 10

源数据4千万算一般大吧，但有索引不是什么问题。做ETL  join千万级的数是常有的事...不用exist，直接join，再用where条件过滤。注意建索引(至于走的是hash还是merge，或者nested什么的就自己分析执行计划吧，如果有想法就hits)不过，我觉得你这里自己封装的函数用的太多了，一条数一条数慢慢地查，肯定慢。我这里也做ETL，不过从不建这样的函数。都是表与表之间直接关联，然后拖数。你们是为了简化业务逻辑才这样做吗？做大数据函数太多伤不起啊