sql速度慢的问题！

Question

 楼主发表于：2009-07-24 15:25:18表r_register的索引 
PK_R_REGISTER Unique CARD_NO, REG_DATE 
R_REGISTERPAYKIND Normal REG_DATE, REGLEVL_CODE, OPER_CODE, PAYKIND_CODE 
R_REGISTER_INDEX1 Normal REG_DATE, DEPT_CODE 表income_expenses的索引 
INCOME_EXPENSES_INDEX3 Normal CARD_NO, OPER_CODE, ACC_DTIME 
INCOME_EXPENSES_INDEX2 Normal BILL_DATE N 
select /*+ index(a,PK_R_REGISTER)*/ 
distinct b.invoice_no  invoice_no, 
        b.own_cost    own_cost, 
        a.dept_name    dept_name, 
        a.empl_code    doct_code, 
        a.mcard_no    mcard_no, 
        a.card_no      card_no, 
        b.cost        cost, 
        a.name        name, 
        a.pact_name    pact_name, 
        a.pact_code    pact_code, 
        b.fee_date    fee_Date, 
        b.oper_code    fee_person_code, 
        a.reglevl_code reglevl_code 
  From (select sum(cost) cost, 
              sum(own_cost) own_cost, 
              card_no, 
              invoice_no, 
              acc_dtime fee_date, 
              bill_date, 
              oper_code 
          from income_expenses 
        where income_expenses.card_no > '-1' 
        group by invoice_no, card_no, acc_dtime, bill_date, oper_code) b, 
      r_register a 
where a.card_no = b.card_no 
  and a.reg_date = b.bill_date 
SELECT STATEMENT, GOAL = CHOOSE      耗费=10236 基数=163609  字节=25195786 
SORT UNIQUE 耗费=10236 基数=163609  字节=25195786 
  HASH JOIN 耗费=6434     基数=163609  字节=25195786 
  VIEW 对象所有者=TLHIS 耗费=910     基数=163609 字节=10798194 
    SORT GROUP BY 耗费=910     基数=163609 字节=10470976 
    TABLE ACCESS BY GLOBAL INDEX ROWID 对象名称=INCOME_EXPENSES 耗费=10 基数=163609 字节=10470976 
      INDEX RANGE SCAN           对象名称=INCOME_EXPENSES_INDEX3 耗费=2 基数=29450 
  TABLE ACCESS BY GLOBAL INDEX ROWID 对象名称=R_REGISTER 耗费=826 基数=2964088 字节=260839744 
      INDEX FULL SCAN 对象名称=PK_R_REGISTER 耗费=26 基数=1  
income_expenses 300万行左右 r_register      150万行左右
 

Answer 1

where income_expenses.card_no > '-1'索引过滤反而影响速度
INCOME_EXPENSES_INDEX3可以改成
group by invoice_no, card_no, acc_dtime, bill_date, oper_code
所有字段，或者前几个字段如果不能改索引，那就改成income_expenses.card_no+0>'-1'强制不用索引

Answer 2

将上面的sql做如下修改
select t.invoice_no      AS invoice_no,
       t.dept_name       as dept_name,
       t.doct_code       as doct_code,
       t.mcard_no        as mcard_no,
       t.card_no         as card_no,
       t.COST            as cost,
       t.fee_person_code as fee_person_code,
       t.name            as name,
       t.pact_name       as pact_name,
       t.own_cost        as own_cost,
       t.reglevl_code    as reglevel_code
  from (select distinct b.invoice_no   invoice_no,
                        b.own_cost     own_cost,
                        a.dept_name    dept_name,
                        a.empl_code    doct_code,
                        a.mcard_no     mcard_no,
                        a.card_no      card_no,
                        b.cost         cost,
                        a.name         name,
                        a.pact_name    pact_name,
                        a.pact_code    pact_code,
                        b.fee_date     fee_Date,
                        b.oper_code    fee_person_code,
                        a.reglevl_code reglevl_code
          From r_register a,
               (select sum(cost) cost,
                       sum(own_cost) own_cost,
                       card_no,
                       invoice_no,
                       acc_dtime fee_date,
                       bill_date,
                       oper_code
                  from income_expenses
                /*where income_expenses.card_no > '-1'*/   如果这个位置加上索引的话 速度会较慢，不知道是什么原因，去掉后反而更快。
                 group by invoice_no,
                          card_no,
                          acc_dtime,
                          bill_date,
                          oper_code) b
         where a.card_no = b.card_no
           and a.reg_date = b.bill_date) t
 where t.pact_code || '' = '3' 强制执行PK_R_REGISTER Unique CARD_NO, REG_DATE 速度较快 
 否则的话，是执行t.pact_code 的索引  速度较慢
   and t.fee_date >= sysdate - 1
   and t.fee_date < sysdate
   and (t.reglevl_code = '2'(传入的变量T) or 'all'(传入的变量T) = 'all')
现在问题是  如果传入的变量T如果是 'all'  则这句就是“真” 就是所有级别代码的数据查询
查询的速度较快   
TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=R_REGISTER 耗费=1 基数=1 字节=88
     INDEX UNIQUE SCAN 对象所有者=TLHIS 对象名称=PK_R_REGISTER 基数=1
不到一秒如果传入的变量T如果是 '2'  一个指定的级别代码 执行的速度就较慢  
TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=R_REGISTER 耗费=826 基数=296 字节=26048
     INDEX FULL SCAN 对象所有者=TLHIS 对象名称=PK_R_REGISTER 耗费=26 基数=2964088
一分左右上面就是 分别执行不同的sql执行计划  我只将不同的部分 给贴上去了
oracle 能不能指定索引的检索方式那  如 强制指定索引采用 INDEX UNIQUE SCAN
还是可以通过其他的方法变通一下 得到解决那
谢谢各位了啊！           

Answer 3

and (t.reglevl_code = '2'(传入的变量T) or 'all'(传入的变量T) = 'all')两个传入的变量是 同一变量  值是相同的  在这个位置这样写  目的是 提示的作用

Answer 4

你只要做好 dbms_stats.gather_table_stats（。）的工作，oracle一般会帮你选出最好的执行方案的

Answer 5

sql的最后
如果传入变量的值=all的话 即
and (t.reglevl_code = 'all'(传入的变量T) or 'all'(传入的变量T) = 'all') 执行计划
TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=R_REGISTER 耗费=1 基数=1 字节=88 
    INDEX UNIQUE SCAN 对象所有者=TLHIS 对象名称=PK_R_REGISTER 基数=1 不到一秒 如果传入变量的值=2的话 即
and (t.reglevl_code = '2'(传入的变量T) or '2'(传入的变量T) = 'all')TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=R_REGISTER 耗费=826 基数=296 字节=26048 
    INDEX FULL SCAN 对象所有者=TLHIS 对象名称=PK_R_REGISTER 耗费=26 基数=2964088 
一分左右 
相同的sql 因为传入的参数的不同 导致了执行速度的差异 有什么样的好方法 无论传入什么样的参数都能较快
的速度执行  
传入的参数不同导致了不同的索引扫描方式  oracle能否指定索引的扫描方式吗。
或者通过什么方法变通一下  把sql的执行速度提上去   
谢谢各位了 啊！
可以把最外层的t去掉，合到里面的子查询吧。
我是把他们写的视图给变成 sql了，这个样更方便大家分析。T表式一个视图。

Answer 6

改成这样试试
and (t.reglevl_code||'' = '2' or '2'||'' = 'all')

Answer 7

还是执行  
TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=R_REGISTER 耗费=826 基数=296 字节=26048 
    INDEX FULL SCAN 对象所有者=TLHIS 对象名称=PK_R_REGISTER 耗费=26 基数=2964088 只有传入的变量 是 'all'的时候  我估计是oracle的优化器直接将t.reglevl_code给屏蔽掉了
sql的条件中只要加入reglevl_code检索就会变慢 但是还是需要这个条件的

Answer 8

在T表的where条件中 如果t.reglevl_code字段加进去 
索引PK_R_REGISTER 将会执行INDEX FULL SCAN 扫描  就得用一分钟左右如果 没有t.reglevl_code 字段
索引PK_R_REGISTER 将会执行INDEX UNIQUE SCAN 扫描  就用一秒钟左右

Answer 9

select t.invoice_no      AS invoice_no, 
      t.dept_name      as dept_name, 
      t.doct_code      as doct_code, 
      t.mcard_no        as mcard_no, 
      t.card_no        as card_no, 
      t.COST            as cost, 
      t.fee_person_code as fee_person_code, 
      t.name            as name, 
      t.pact_name      as pact_name, 
      t.own_cost        as own_cost, 
      t.reglevl_code    as reglevel_code 
  from (select distinct b.invoice_no  invoice_no, 
                        b.own_cost    own_cost, 
                        a.dept_name    dept_name, 
                        a.empl_code    doct_code, 
                        a.mcard_no    mcard_no, 
                        a.card_no      card_no, 
                        b.cost        cost, 
                        a.name        name, 
                        a.pact_name    pact_name, 
                        a.pact_code    pact_code, 
                        b.fee_date    fee_Date, 
                        b.oper_code    fee_person_code, 
                        a.reglevl_code reglevl_code 
          From r_register a, 
              (select sum(cost) cost, 
                      sum(own_cost) own_cost, 
                      card_no, 
                      invoice_no, 
                      acc_dtime fee_date, 
                      bill_date, 
                      oper_code 
                  from income_expenses 
                group by invoice_no, 
                          card_no, 
                          acc_dtime, 
                          bill_date, 
                          oper_code) b 
        where a.card_no = b.card_no 
          and a.reg_date = b.bill_date) t 
where t.pact_code || '' = '3'   
  and t.fee_date >= sysdate - 1 
  and t.fee_date < sysdate 
  and t.reglevl_code = '2'    如果加入and t.reglevl_code = '2'这个条件的话 SELECT STATEMENT, GOAL = CHOOSE  耗费=876 基数=296  字节=35520
 VIEW 对象所有者=TLHIS 对象名称=VIEW_INCOME_EXPENSES  耗费=876 基数=296 字节=35520
  SORT UNIQUE 耗费=876 基数=296   字节=44992
   MERGE JOIN 耗费=865 基数=296   字节=44992
    TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=R_REGISTER 耗费=826 基数=296 字节=26048
     INDEX FULL SCAN 对象所有者=TLHIS 对象名称=PK_R_REGISTER 耗费=26 基数=2964088
    SORT JOIN 耗费=39 基数=1544 字节=98816
     VIEW 对象所有者=TLHIS 耗费=19 基数=1544 字节=98816
      SORT GROUP BY 耗费=19 基数=1544 字节=98816
       FILTER
        TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=INCOME_EXPENSES 耗费=6 基数=1544 字节=98816
         INDEX RANGE SCAN 对象所有者=TLHIS 对象名称=IDX_PAYWAY 耗费=2 基数=2779 sql的执行速度大概在一分钟左右，如何优化这条语句 ，使其查询的速度更快些。 如果不加入and t.reglevl_code = '2'这个条件的话 
即将这句话屏蔽掉的话
SELECT STATEMENT, GOAL = CHOOSE 耗费=1602 基数=1574 字节=188880
 VIEW 对象所有者=TLHIS 对象名称=VIEW_INCOME_EXPENSES 耗费=160 2基数=1574 字节=188880
  SORT UNIQUE 耗费=1602 基数=1574 字节=239248
   NESTED LOOPS 耗费=1563 基数=1574 字节=239248
    VIEW 对象所有者=TLHIS 耗费=19 基数=1544 字节=98816
     SORT GROUP BY 耗费=19 基数=1544 字节=98816
      FILTER
       TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=INCOME_EXPENSES 耗费=6 基数=1544 字节=98816
        INDEX RANGE SCAN 对象所有者=TLHIS  对象名称=IDX_PAYWAY 耗费=2 基数=2779
    TABLE ACCESS BY GLOBAL INDEX ROWID 对象所有者=TLHIS 对象名称=R_REGISTER 耗费=1 基数=1 字节=88
     INDEX UNIQUE SCAN 对象所有者=TLHIS 对象名称=PK_R_REGISTER 基数=1 虽然cost比上一个还大 但是 执行的速度却 不到一秒钟各位不好意思啊 ，重复了好几次 ，让各位费神了啊。