一个sql 优化

SQL code

  SQL Statement from editor:        select c0602 "商品编码",c0625 "商品条码",    c0103 "商品名称",c0104 "规格",c0604 "配货件数",    c0605 "配货数量",c0618 "计划赠品数",c0606 "实际出库数",    c0621 "出库赠品数",c0610 "门店实收数",c0611 "实收赠品数",    c0609 "剩余商品入库",c0622 "剩余商品报损"     from c05 t1,c06,vc01 where c0501=c0601  and c0602=c0101      and c0538    between to_date('2009.1.1','yyyy-mm-dd') and to_date('2009.2.28','yyyy-mm-dd')  ------------------------      Statement Id=7   Type=INDEX  Cost=2  TimeStamp=21-04-10::09::44:47         (1)  SELECT STATEMENT  CHOOSE      Est. Rows: 23,043  Cost: 11,365       (10)  MERGE JOIN      Est. Rows: 23,043  Cost: 11,365           (7)  SORT JOIN                 Est. Rows: 23,043  Cost: 6,538               (6)  TABLE ACCESS BY INDEX ROWID FZDC.C06  [Analyzed]                (6)   Blocks: 34,464 Est. Rows: 35 of 2,411,508  Cost: 9                     Tablespace: FZ_DAT                   (5)  NESTED LOOPS                         Est. Rows: 23,043  Cost: 6,017                       (3)  TABLE ACCESS BY INDEX ROWID FZDC.C05  [Analyzed]                        (3)   Blocks: 7,944 Est. Rows: 668 of 406,159  Cost: 5                             Tablespace: FZ_DAT                           (2)  NON-UNIQUE INDEX RANGE SCAN FZDC.IND_C05_C0538  [Not Analyzed]                                 Est. Rows: 1,055  Cost: 2                       (4)  UNIQUE INDEX RANGE SCAN FZDC.SYS_C005366  [Analyzed]                             Est. Rows: 35  Cost: 2           (9)  SORT JOIN                 Est. Rows: 305,274  Cost: 4,827               (8)  TABLE ACCESS FULL FZDC.C01  [Analyzed]                (8)   Blocks: 10,492 Est. Rows: 305,274 of 305,274  Cost: 1,594                     Tablespace: FZ_DAT

1. 请继续优化。。。太慢了。

2。 当block rows cost 怎么来判断要不要走索引呢。。请详解


[解决办法]
你这个我猜测是慢在时间的转换上了.

考虑对c0538建立索引再试试.
[解决办法]
--先写个子查询，在连接
select c0602 "商品编码",c0625 "商品条码",
c0103 "商品名称",c0104 "规格",c0604 "配货件数",
c0605 "配货数量",c0618 "计划赠品数",c0606 "实际出库数",
c0621 "出库赠品数",c0610 "门店实收数",c0611 "实收赠品数",
c0609 "剩余商品入库",c0622 "剩余商品报损"
from (select * from c05
where c0538 between to_date('2009.1.1','yyyy-mm-dd') and to_date ('2009.2.28','yyyy-mm-dd'))
t1,c06,vc01 where c0501=c0601 and c0602=c0101
[解决办法]
这个执行计划和SQLPLUS里看到的不太一样,有点晕....
不过时间过滤掉消耗时间不多,应该是出现在c0602=c0101的MERGER上,
而且Est. Rows: 305,274 Cost: 4,827
(8) TABLE ACCESS FULL FZDC.C01 [Analyzed]
(8) Blocks: 10,492 Est. Rows: 305,274 of 305,274 Cost: 1,594
Tablespace: FZ_DAT
似乎C01全表扫描,没有走索引

[解决办法]
那些字段是那个表的？没有表结构。
多表连接最好给表指定别名，能区分字段所属表。
[解决办法]
select c0602 "商品编码",c0625 "商品条码",
c0103 "商品名称",c0104 "规格",c0604 "配货件数",
c0605 "配货数量",c0618 "计划赠品数",c0606 "实际出库数",
c0621 "出库赠品数",c0610 "门店实收数",c0611 "实收赠品数",
c0609 "剩余商品入库",c0622 "剩余商品报损"
from c05 t1,c06,vc01 where c0501=c0601 and c0602=c0101

and c0538 >= to_date('2009.1.1','yyyy-mm-dd') c0538<= to_date('2009.2.28','yyyy-mm-dd')


between 速度比较慢；如果实在慢可以把SQL语句放到过程中，建临时表 两两关联查询
[解决办法]
执行计划的第5和10步，感觉有点问题

[解决办法]
是不是几个表关联的拦位没有索引啊？

Mark.