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 怎么来判断要不要走索引呢。。请详解
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 怎么来判断要不要走索引呢。。请详解
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
不过时间过滤掉消耗时间不多,应该是出现在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全表扫描,没有走索引
Est. Rows: 23,043 Cost: 6,017
select /*+ use_hash(t1)*/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=SORT
Cost=4827 TimeStamp=21-04-10::11::19:22
(1) SELECT STATEMENT CHOOSE
Est. Rows: 23,043 Cost: 10,644
(9) MERGE JOIN
Est. Rows: 23,043 Cost: 10,644
(6) SORT JOIN
Est. Rows: 23,043 Cost: 5,817
(5) HASH JOIN
Est. Rows: 23,043 Cost: 5,296
(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 Cost: 2
(4) TABLE ACCESS FULL FZDC.C06 [Analyzed]
(4) Blocks: 34,464 Est. Rows: 2,411,508 of 2,411,508 Cost: 5,232
Tablespace: FZ_DAT
(8) SORT JOIN
Est. Rows: 305,274 Cost: 4,827
(7) TABLE ACCESS FULL FZDC.C01 [Analyzed]
(7) Blocks: 10,492 Est. Rows: 305,274 of 305,274 Cost: 1,594
Tablespace: FZ_DAT
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语句放到过程中,建临时表 两两关联查询
(4) Blocks: 34,464 Est. Rows: 2,411,508 of 2,411,508 Cost: 5,232
Tablespace: FZ_DAT
的问题吧