group by 速度很慢该怎么解决

pjm_ocp

物理读很大加大PGA_TARGET试试,是动态参数

battleman

看你的执行计划,索引选择性不高啊,表跟索引都分析了吗?

mihawk

原帖由 jock_liu 于 2008-4-18 13:03 发表
select pallet_no
from sfism4.r_wip_tracking_t
where pallet_no  'N/A' and in_station_time > sysdate-365
group by pallet_no
order by pallet_no
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9751 Card=83606 Byte
          s=1922938)

   1    0   SORT (GROUP BY) (Cost=9751 Card=83606 Bytes=1922938)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'R_WIP_TRACKING_T' (Cos
          t=9061 Card=204653 Bytes=4707019)

   3    2       INDEX (RANGE SCAN) OF 'R107_TIME' (NON-UNIQUE) (Cost=5
          81 Card=35621)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    2707189  consistent gets
     182834  physical reads
      96804  redo size
    3264686  bytes sent via SQL*Net to client
      98766  bytes received via SQL*Net from client
       8935  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     134003  rows processed

试试:
1. 把索引加在 r_wip_tracking_t( in_station_time, pallet_no)的字段组合上
2. SQL改为
select distinct pallet_no
from sfism4.r_wip_tracking_t
where in_station_time > sysdate-365
把pallet_no<>'N/A'的条件放在程序端去.

mihawk

看执行计划
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9751 Card=83606 Bytes=1922938)
   1    0   SORT (GROUP BY) (Cost=9751 Card=83606 Bytes=1922938)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'R_WIP_TRACKING_T' (Cost=9061 Card=204653 Bytes=4707019)
   3    2       INDEX (RANGE SCAN) OF 'R107_TIME' (NON-UNIQUE) (Cost=581 Card=35621)

成本估算中占大头的不是group by的成本, 因为它的成本只有690(9751-9061=690);
而真正在成本中占大头的是TABLE ACCESS (BY INDEX ROWID), 它的成本是8480(9061-581=8480);
所以应该把优化的精力放在去掉table access这个步骤上.