这条SQL语句如何优化？

kangaroo76

今天忙了一天别的事，没来得及看着问题，ahlu和husthxd的建议我回头试完把结果给大家，但愿能解决问题.

kangaroo76

最初由 husthxd 发布
[B]

1.尝试重新组织表
-- 把表数据备份到临时表中
-- 重新组织表数据
truncate table ....;
create table ... as select * from temp_table order by prma,fln,fs,fltdate; [/B]

按上面的方法重建了表、索引，其后又将索引放入keep池，consistent gets和physical read都大大减小了但是执行时间都没有缩短，为什么？
执行计划，统计及时间如下：
SQL> select
  2      *
  3  from
  4     SSTATUS_BAK a
  5  where
  6     (
  7      fltdate>=trunc(sysdate)
  8      and fltdate<(trunc(sysdate)+7)
  9     )
10  order by
11     PRMA,FLN,FS,FLTDATE;

32269 rows selected.

Elapsed: 00:00:26.36

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SSTATUS_BAK'
   2    1     INDEX (FULL SCAN) OF 'FLT_IND_BAK' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12597  consistent gets
       3719  physical reads
          0  redo size
    4291448  bytes sent via SQL*Net to client
     174589  bytes received via SQL*Net from client
       2153  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      32269  rows processed

SQL> alter index FLT_IND_BAK storage (buffer_pool keep);

Index altered.

Elapsed: 00:00:00.01
SQL> select
  2      *
  3  from
  4     SSTATUS_BAK a
  5  where
  6     (
  7      fltdate>=trunc(sysdate)
  8      and fltdate<(trunc(sysdate)+7)
  9     )
10  order by
11     PRMA,FLN,FS,FLTDATE;

32269 rows selected.

Elapsed: 00:00:26.49

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SSTATUS_BAK'
   2    1     INDEX (FULL SCAN) OF 'FLT_IND_BAK' (UNIQUE)




Statistics
----------------------------------------------------------
        271  recursive calls
          3  db block gets
      12671  consistent gets
        374  physical reads
          0  redo size
    4291448  bytes sent via SQL*Net to client
     174589  bytes received via SQL*Net from client
       2153  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      32269  rows processed

SQL>
SQL> select
  2      *
  3  from
  4     SSTATUS_BAK a
  5  where
  6     (
  7      fltdate>=trunc(sysdate)
  8      and fltdate<(trunc(sysdate)+7)
  9     )
10  order by
11     PRMA,FLN,FS,FLTDATE;

32269 rows selected.

Elapsed: 00:00:26.34

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SSTATUS_BAK'
   2    1     INDEX (FULL SCAN) OF 'FLT_IND_BAK' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12597  consistent gets
          0  physical reads
          0  redo size
    4291448  bytes sent via SQL*Net to client
     174589  bytes received via SQL*Net from client
       2153  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      32269  rows processed

ahlu

要么从硬件入手，打个比方，就象查字典，当你要查5万字典中的3万个字，哪一种方法都很慢（查索引目录或直接翻字典），而这时快慢而是取决于你翻字典的速度，如果两个人一起翻会快一倍

jo-qiao

是不是TRUNC的影響?你看看時間有沒有辦法換個方式.INDEX是應該按照你的WHERE 後面的欄位建立的,根據哪幾個欄位就建一個INDEX,包括這幾個欄位,才有用吧.

kangaroo76

之所以希望尽快是因为我们的系统虽然是OLTP，但是客户希望能分时段运行该程序将数据导成文本文件(一天十几次，这种功能有类似于OLAP)，所以希望能将运行该程序的成本降到最小。

下面是tkprof生成结果部分（此时已将索引放入keep池）：
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 99
********************************************************************************

select
    *
from
   SSTATUS_BAK a
where
   (
    fltdate>=trunc(sysdate)
    and fltdate<(trunc(sysdate)+7)
   )
order by
   PRMA,FLN,FS,FLTDATE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     2153     12.19      12.17          0      11871          0       32269
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2155     12.19      12.17          0      11871          0       32269

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 99

Rows     Row Source Operation
-------  ---------------------------------------------------
  32269  TABLE ACCESS BY INDEX ROWID SSTATUS_BAK
  32270   INDEX FULL SCAN (object id 1228888)

wnj

要我看还是SQL语句本身的问题，在这条语句执行之前先建一个函数索引试试。
在SQL语句执行之后再使索引无效。
ORDER BY 本身就会使性能下降的。

husthxd

最初由 kangaroo76 发布
[B]今天忙了一天别的事，没来得及看着问题，ahlu和husthxd的建议我回头试完把结果给大家，但愿能解决问题. [/B]

对数据的实时性要求很高吗?
不妨采用物化视图来提高性能.
要注意sysdate条件的处理.

linux_aaa

是不是查询本身已经优化不了了，问题在于Fetch，你的网络不够，直接在Server上执行试试看。

kangaroo76

最初由 linux_aaa 发布
[B]是不是查询本身已经优化不了了，问题在于Fetch，你的网络不够，直接在Server上执行试试看。 [/B]

前面的结果都是直接在server上执行的结果，没有经过网络的。

jiangjs888

假如对主键的修改不多的话,可以考虑使用IOT表,应该有所提高的!