语句执行需要4分多钟，不知道怎么优化

Question

关联的视图
create or replace view v_gzjb_sumglide_profit
(goods_inprice, sbill_detail_okweight, profit_class, sumglide_makeinv_billcode, sumglide_makeinv_billbatch)
as 
select g.goods_inprice, g.sbill_detail_okweight,g.profit_class,f.sumglide_makeinv_billcode,f.sumglide_makeinv_billbatch from warehouse_sumglide f,calculate_profit g where 
f.sumglide_billcode=g.init_billcode and f.sumglide_billbatch=g.init_billbatch and f.billtype_code=g.init_billtype and g.profit_billtype<>'M003';查询语句
select (select sum(round((g.goods_inprice/1.17*g.sbill_detail_okweight),2)) from v_gzjb_sumglide_profit g where  
 b.makeinv_billcode=g.sumglide_makeinv_billcode  and b.makeinv_billbatch=g.sumglide_makeinv_billbatch
and g.profit_class=1)
, b.data_bweight,cast('6401' as varchar(32)) as name,
CONCAT('跨月主营业务成本   '||a.datas_balcorpname||' ',CONCAT(concat(CONCAT(to_char(a.makeinv_date,'yyyy-mm-dd'),'  采购发票  '),' 结算单号 '||a.makeinv_billcode),' 入库单号 '||makeinv_detail_optcode)),
a.datas_balcorp,a.datas_balcorpname,'','','','','','','','','',
(select t.pntree_name from BASIC_PARTSNAME t where t.partsname_name=b.partsname_name),'','','','',''
from invoice_makeinv a,invoice_makeinv_detail b 
where (select sum(round((f.goods_inprice*f.sbill_detail_okweight),2)) from v_gzjb_sumglide_profit f where  
 b.makeinv_billcode=f.sumglide_makeinv_billcode  and b.makeinv_billbatch=f.sumglide_makeinv_billbatch )!=0 and  a.makeinv_billcode=b.makeinv_billcode and a.member_code=b.member_code and 
a.makeinv_billcode='MP20121122001'  and a.billtype_code='F006';

Answer 1

先单独执行2个嵌套语句， 看慢在哪一句。 貌似两个嵌套的SQL 都不是很复杂， 可以考虑通过建索引来解决。

Answer 2

select count(*) from invoice_makeinv_detail  where makeinv_billcode='MP20121122001'  有355条。我现在采用左链接了select 
sum(round((g.goods_inprice / 1.17 * g.sbill_detail_okweight),2))
/*(select sum(round((g.goods_inprice / 1.17 * g.sbill_detail_okweight),
                         2))
          from v_gzjb_sumglide_profit g
         where b.makeinv_billcode = g.sumglide_makeinv_billcode
           and b.makeinv_billbatch = g.sumglide_makeinv_billbatch
           and g.profit_class = 1)*/
, max(b.data_bweight),cast('1405' as varchar(32)) as name,
CONCAT('跨月回冲库存商品   '||max(a.datas_balcorpname)||' ',CONCAT(concat(CONCAT(to_char(max(a.makeinv_date),'yyyy-mm-dd'),'  采购发票  '),' 结算单号 '||max(a.makeinv_billcode)),' 入库单号 '||max(makeinv_detail_optcode))),
max(a.datas_balcorp),max(a.datas_balcorpname),'','','','','','','','','',
b.pntree_name,'','','','',''
from invoice_makeinv a,invoice_makeinv_detail b  left join  v_gzjb_sumglide_profit g 
on (b.makeinv_billcode=g.sumglide_makeinv_billcode  and b.makeinv_billbatch=g.sumglide_makeinv_billbatch and nvl(g.profit_class,0) = 1)
where   a.makeinv_billcode=b.makeinv_billcode and a.member_code=b.member_code and
 a.makeinv_billcode='MP20121122001'  and a.billtype_code='F006'
 having sum(round((g.goods_inprice / 1.17 * g.sbill_detail_okweight),2))<>0 
 group by b.pntree_name;

Answer 3

建索引了，发现有些索引根本没发用啊 
对表invoice_makeinv 的字段makeinv_billcode建了索引，结果却是全表检索 SQL> explain plan for select t.* from invoice_makeinv t where t.makeinv_billcode = 'MP20120416026';
 
Explained
 
Executed in 0.016 seconds
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3395678150
--------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     1 |       |     2   (0)
|   1 |  SORT AGGREGATE        |                   |     1 |    40 |
|*  2 |   TABLE ACCESS FULL    | FUND_ACCOUNTGLIDE |  6045 |   236K|  1622   (3)
|   3 |    SORT AGGREGATE      |                   |     1 |    40 |
|*  4 |     TABLE ACCESS FULL  | FUND_ACCOUNTGLIDE |   832 | 33280 |  1622   (3)
|   5 |      SORT AGGREGATE    |                   |     1 |    43 |
|*  6 |       TABLE ACCESS FULL| FUND_ACCOUNTGLIDE |     3 |   129 |  1596   (2)
|   7 |  FAST DUAL             |                   |     1 |       |     2   (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DATAS_BALCORPNAME"='广州承宏贸易有限公司' AND "ACCOUNTGLIDE_CLASS"=2 AND
              "ACCOUNTGLIDE_FUNDFLAG"=0)
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   4 - filter("ACCOUNTGLIDE_CLASS"=3 AND "DATAS_BALCORPNAME"='广州承宏贸易有限公司' AND
              "ACCOUNTGLIDE_FUNDFLAG"=0)
   6 - filter("ACCOUNTGLIDE_BILLTYPE"='W028' AND "ACCOUNTGLIDE_CLASS"=0 AND
              "DATAS_BALCORPNAME"='广州承宏贸易有限公司')
Note
-----
   - 'PLAN_TABLE' is old version
 
28 rows selected
 
Executed in 7.801 seconds

Answer 4

看一下cost吧然后建索引试验一下