相关知识

1、《Oracle性能诊断艺术》

 

2、手动进行索引扫描

select /*+index(s, IDX_AUTO_TYPE_ID  ) */ m.soft_id, m.version, s.lan_id, s.soft_sub_id
  from tb_soft_main_reg m, tb_soft_sub_reg s
 where m.soft_id = s.soft_id
   and m.auto_type_id = 1571
   and s.contribute_date <=to_date('2011-03-24 00:58:24','YYYY-MM-DD HH24:mi:ss')
   and s.state = 3
   and s.lan_id not in (3, 10, 9, 7, 6, 5, 4, 12, 13, 11)
 order by m.version desc, m.soft_id desc

 

 

3、

       select d1.auto_name ,decode(bb.softIncome,null,0,bb.softIncome) softIncome from
             ta_car_dept  c1,
             ta_cardept_desc d1,
             (           
              select d0.s_code s_code ,         
                                sum(aa.allprice) softIncome
                          from  ta_soft_conf_sub s0,
                                ta_car_dept d0,
                                  (
                                  select  m.soft_conf_id soft_conf_id,  sum(t1.total_amount*t1.curr_exchange_rate)/(
                                    select decode(count(1),0,1,count(1)) from
                                                 ta_soft_conf_sub s,
                                                 ta_car_dept d where s.auto_type_id = d.auto_type_id and s.soft_conf_id = m.soft_conf_id
                                                 ) allprice
                                  from tb_order t1,  tb_orddet_cfgpkg  t2,
                                                 ta_soft_conf_main m
                                          where t1.order_id = t2.order_id
                                                and t1.pay_date between to_date('2010-5-1', 'yyyy-MM-dd') and
                                                                                                       to_date('2011-5-1', 'yyyy-MM-dd') 
                                                and t1.state in (8,9)
                                                and t2.ref_id = m.soft_conf_id  group by m.soft_conf_id
                                   )  aa          
                           where aa.soft_conf_id = s0.soft_conf_id
                                 and s0.auto_type_id = d0.auto_type_id and d0.s_code is not null
                                 group by d0.s_code
                  )bb
                  where     c1.auto_type_id = d1.auto_type_id
                  and c1.s_code = bb.s_code
                  and bb.s_code is not null
                  and c1.type = 1
                  and d1.lan_id = 1    order by bb.softIncome desc 
                 
                 
                 
                 
                  select count(2) from ta_soft_conf_sub s,
                                ta_car_dept d where s.soft_conf_id = 57 and s.auto_type_id = d.auto_type_id
                               
                  7057.46869565217  23