一个SQL速度的问题

zhongqixu

SELECT /*+ALL_ROWS*/
NJS.LOT_NUMBER,
NJS.JO_NUMBER,
NWD.FINISHED_QTY,
NWD.FINISHED_REF_QTY
  FROM NJ_JOB_STATUS NJS,
       NJ_WIPGT_DTL  NWD
WHERE NJS.WIP_ENTITY_ID = NWD.WIP_ENTITY_ID
  AND NWD.ORGANIZATION_ID = 3
   AND NWD.OPERATION_SEQ_NUM = 999
   AND NWD.MATERIAL_TYPE = 'STONE'
   AND NWD.INVENTORY_ITEM_ID = 346561
GROUP BY NJS.LOT_NUMBER,
          NJS.JO_NUMBER,
          NWD.FINISHED_QTY,
          NWD.FINISHED_REF_QTY;
附件是这个SQL的运行计划,看那个计划按道理来说来速度应该不会慢的,但不知道为什么速度巨慢,取一行数据都用了300多秒.

jessie

你为啥要用group by 呢.你又没有类似于sum之类的函数.
说说你需求吧.

jojoz

/*+ALL_ROWS*/
取一行？
做一下表分析

zhongqixu

因为有太多的重复,用GROUP BY 是去重复的.
NWD.INVENTORY_ITEM_ID = 346561
刚好只有一行数据,当它等于其它的时候会是有多行数据的

zhongqixu

执行计划的那个BYTES是什么作用的?为什么我的有一个会有122000那么大的?
还有一个Cardinality的作用又是什么?好像这个数大了那个Bytes也会跟着大的.

jessie

CARDINALITY是指sql语句得到结果集需要扫描的行数
cost 是指指IO成本

jojoz

Cardinality返回行数
BYTE返回的字节
COST不单纯是IO,还有CPU,内存等等的总体成本

如果是过滤,可以用DISTINCT,不需要GROUP

如果单纯取前几行,用FIRST ROW外加ROWNUM过滤,不过你用了GROUP,再用这些已经没意义了

iflxm

COST的值只能做参考，不是绝对值。

orapeasant

根据你的plan, 使用了nested loops. 可以理解为下面这样的访问方法:
FOR a IN (select * from NJ_WIPGT_DTL  NWD where....) LOOP
  FOR b IN (select  * from NJ_JOB_STATUS where WIP_ENTITY_ID = a.WIP_ENTITY_ID)
  LOOP
     RETURN A.*,b.*;
   END LOOP;
END LOOP;

所以 访问NJ_JOB_STATUS 表的时候, 最快的应该是使用基于WIP_ENTITY_ID的索引, 从Plan里看, 使用的是NJ_JOB_STATUS_N3.  这是针对WIP_ENTITY_ID的索引吗?
另外, 表最近有没有analyze过? 最后, 把你的hint去掉後看看plan和运行时间, 就我个人而言, 我选择尽量不用hint. 我所遇到的性能出问题的sql, 多数是因为用错了hint, 或者在不该用hint的时候用了hint. oracle的cbo 正在越来越强大以至于越来越不需要开发人员手工加hint了.