SQL优化求助

Question

请各位大虾帮忙看看这条指令有没有优化的空间~
现在执行一次大概要1秒，生产环境每次执行需要循环传值，大概循环执行几百次..
数据量说明：a表60万  b表180万  e表260万
SELECT cucy,up,qty,signdat 
FROM 
  (
    select 
    nvl(a.cucy,a.qpcucy) cucy, 
    nvl(b.ytxup,b.qpytxup) up ,
    NVL(b.cprqty,b.caqqty) qty,
    a.signdat,a.vndno 
    from txf000hfc21 a,txf000hfc22 b,txf000hba32 e
    where a.purco=b.purco and a.purshno= b.purshno and a.vndno=b.vndno AND a.purco=e.purco AND a.purshno=e.purshno AND b.puritm=e.puritm
    and  a.purco='5' and Nvl(b.ordhmat,e.mtno)='A2P1P34400019WA7'  AND a.rckmk='Y'
    ORDER BY a.signdat desc, up,qty
  ) 
WHERE ROWNUM=1;
执行计划：
Plan hash value: 3415185491                                                                                 
                                                                                                            
------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |     1 |    38 |       |  8428   (1)| 00:01:42 |
|*  1 |  COUNT STOPKEY                  |                  |       |       |       |            |          |
|   2 |   VIEW                          |                  |     1 |    38 |       |  8428   (1)| 00:01:42 |
|*  3 |    SORT ORDER BY STOPKEY        |                  |     1 |   108 |       |  8428   (1)| 00:01:42 |
|   4 |     NESTED LOOPS                |                  |     1 |   108 |       |  8427   (1)| 00:01:42 |
|*  5 |      HASH JOIN                  |                  |  2774 |   192K|  2904K|  4893   (1)| 00:00:59 |
|*  6 |       TABLE ACCESS FULL         | TXF000HFC21      | 58247 |  2218K|       |  3505   (2)| 00:00:43 |
|*  7 |       INDEX RANGE SCAN          | IX_TXF000HBA32_5 |   134K|  4190K|       |   964   (1)| 00:00:12 |
|*  8 |      TABLE ACCESS BY INDEX ROWID| TXF000HFC22      |     1 |    37 |       |     2   (0)| 00:00:01 |
|*  9 |       INDEX UNIQUE SCAN         | PK_TXF000HFC22   |     1 |       |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
                                                                                                            
Predicate Information (identified by operation id):                                                         
---------------------------------------------------                                                         
                                                                                                            
   1 - filter(ROWNUM=1)                                                                                     
   3 - filter(ROWNUM=1)                                                                                     
   5 - access("A"."PURCO"="E"."PURCO" AND "A"."PURSHNO"="E"."PURSHNO")                                      
   6 - filter("A"."PURCO"='5' AND "A"."RCKMK"='Y')                                                          
   7 - access("E"."PURCO"='5')                                                                              
   8 - filter(NVL("B"."ORDHMAT","E"."MTNO")='A2P1P34400019WA7')                                             
   9 - access("B"."PURCO"='5' AND "A"."PURSHNO"="B"."PURSHNO" AND "A"."VNDNO"="B"."VNDNO" AND               
              "B"."PURITM"="E"."PURITM")                                                                    

Answer 1

1 子查询的结果集多大，貌似只有1条，还要包一层么
2  filter("A"."PURCO"='5' AND "A"."RCKMK"='Y')  这两个字段可否加index   

Answer 2

1.子查询结果集一般返回三十几条，因为要取最近的一条，所以要包一层。
2.加了index，用强制走索引之后发现性能更差..最后改法变成两张表连接之后，时间缩短为原来的四分之一..

Answer 3

1）Predicate Information
   8 - filter(NVL("B"."ORDHMAT","E"."MTNO")='A2P1P34400019WA7')  
   这个隐式除NULL，可否在外面做2）把子查询提到with里面为怎样？
   也就是：
SELECT cucy,up,qty,signdat 
FROM 
  (
    select 
    nvl(a.cucy,a.qpcucy) cucy, 
    nvl(b.ytxup,b.qpytxup) up ,
    NVL(b.cprqty,b.caqqty) qty,
    a.signdat,a.vndno 
    from txf000hfc21 a,txf000hfc22 b,txf000hba32 e
    where a.purco=b.purco and a.purshno= b.purshno and a.vndno=b.vndno AND a.purco=e.purco AND a.purshno=e.purshno AND b.puritm=e.puritm
    and  a.purco='5' and Nvl(b.ordhmat,e.mtno)='A2P1P34400019WA7'  AND a.rckmk='Y'
    ORDER BY a.signdat desc, up,qty
  ) 
WHERE ROWNUM=1;
这整部分放到with里面，lz测试，毕竟with内部是经过优化的

Answer 4

楼主子查询里用ORDER BY 挺耗费资源的；建议使用分析函数来代替
SELECT CUCY, UP, QTY, SIGNDAT
  FROM (SELECT NVL(A.CUCY, A.QPCUCY) CUCY,
               NVL(B.YTXUP, B.QPYTXUP) UP,
               NVL(B.CPRQTY, B.CAQQTY) QTY,
               A.SIGNDAT,
               A.VNDNO,
               ROW_NUMBER() OVER(ORDER BY A.SIGNDAT DESC, UP, QTY) NUM
          FROM TXF000HFC21 A, TXF000HFC22 B, TXF000HBA32 E
         WHERE A.PURCO = B.PURCO
           AND A.PURSHNO = B.PURSHNO
           AND A.VNDNO = B.VNDNO
           AND A.PURCO = E.PURCO
           AND A.PURSHNO = E.PURSHNO
           AND B.PURITM = E.PURITM
           AND A.PURCO = '5'
           AND NVL(B.ORDHMAT, E.MTNO) = 'A2P1P34400019WA7'
           AND A.RCKMK = 'Y')
 WHERE NUM = 1;

Answer 5

txf000hba32 e表好像很大啊,可以先缩小E表结果集试试,而且你SELECT里没用到E表的字段,应该可以用EXISTS来代替，使用半联结查询应该会快些SELECT cucy,up,qty,signdat 
FROM 
  (
    select 
    nvl(a.cucy,a.qpcucy) cucy, 
    nvl(b.ytxup,b.qpytxup) up ,
    NVL(b.cprqty,b.caqqty) qty,
    a.signdat,a.vndno 
    from  txf000hfc21 a,txf000hfc22 b
    where a.purco=b.purco and a.purshno= b.purshno and a.vndno=b.vndno 
    and  a.purco='5'  AND a.rckmk='Y'
    and exists(select 1 from txf000hba32 e where  a.purco=e.purco AND a.purshno=e.purshno AND b.puritm=e.puritm and Nvl(b.ordhmat,e.mtno)='A2P1P34400019WA7' )
    )
版主说的 子查询因子化(with ..as)和分析函数也应该都可以减少执行时间，都试试吧

Answer 6

我怎么觉着这里用分析函数性能会更差涅
楼主贴下最后的修改的sql 

Answer 7

感谢版主大驾光临
基本上我的思路是减少表连接，刚好三表只用到nvl，所以最终做法是换成两张表连接，原来的三表nvl用exists替代。SQL如下：
Ps:有试过用分析函数替代Order by，发现性能还变差了点点..
   还没试过用with替代子查询喔..我待会儿试试看，感谢各位!
SELECT cucy,up,qty,signdat INTO p_purcucy,p_purup,p_purqty,p_purdat
  FROM
  (
  select
    nvl(a.cucy,a.qpcucy) cucy,
    nvl(b.ytxup,b.qpytxup) up ,
    NVL(b.cprqty,b.caqqty) qty,
    a.signdat,a.vndno,b.ordhmat
    from txf000hfc21 a,txf000hfc22 b
    where a.purco=b.purco and a.purshno= b.purshno and a.vndno=b.vndno
    and  a.purco=P_PURCO AND a.rckmk='Y'
    and (b.ordhmat=P_MTNO OR
    (b.ordhmat IS NULL AND EXISTS (SELECT 1 FROM txf000hba32 WHERE purco=b.purco AND purshno=b.purshno AND puritm=b.puritm AND mtno=P_MTNO)))
    AND EXISTS (SELECT 1 FROM txf000hba32 WHERE purco=b.purco AND purshno=b.purshno AND puritm=b.puritm)
    ORDER BY a.signdat desc, up,qty
  )
WHERE ROWNUM=1;

Answer 8

因为没有像诸如union all之类的频繁调用with字句，在这里加了with字句，性能提升微乎其微呢
WITH temp AS 
(
  select /*+ materialize */
    nvl(a.cucy,a.qpcucy) cucy,
    nvl(b.ytxup,b.qpytxup) up ,
    NVL(b.cprqty,b.caqqty) qty,
    a.signdat,a.vndno,b.ordhmat
    from txf000hfc21 a,txf000hfc22 b
    where a.purco=b.purco and a.purshno= b.purshno and a.vndno=b.vndno
    and  a.purco='5' AND a.rckmk='Y'
    and (b.ordhmat='A2P1P34400019WA7' OR
    (b.ordhmat IS NULL AND EXISTS (SELECT 1 FROM txf000hba32 WHERE purco=b.purco AND purshno=b.purshno AND puritm=b.puritm AND mtno='A2P1P34400019WA7')))
    AND EXISTS (SELECT 1 FROM txf000hba32 WHERE purco=b.purco AND purshno=b.purshno AND puritm=b.puritm)
    ORDER BY a.signdat desc, up,qty
  )
SELECT cucy,up,qty,signdat 
  FROM
  temp
WHERE ROWNUM=1;

Answer 9

别的不清楚，感觉nvl这个函数挺影响性能挺有影响的。我们这边就有个sql，并发执行的时候，总是在这个sql调用的地方发生锁表。俺没啥好法子，考虑能否在调用nvl的字段处，将字段增加一个DEFAULT呢？然后就不使用nvl函数了。