配送配货单商品明细查询 sql 优化

最新推荐文章于 2021-06-04 16:52:06 发布

最新推荐文章于 2021-06-04 16:52:06 发布 · 175 阅读

本文通过几个具体的SQL查询案例，展示了如何通过不同的查询策略和技术来优化数据库查询效率，包括使用索引、哈希连接等方法。

c06 --------------7545623条

c05---------------1032652条

vc01--------------427741条

SQL Statement from editor:

select c0602 "商品编码",c0625 "商品条码",
c0103 "商品名称",c0104 "规格",c0604 "配货件数",
c0605 "配货数量",c0618 "计划赠品数",c0606 "实际出库数",
c0621 "出库赠品数",c0610 "门店实收数",c0611 "实收赠品数",
c0609 "剩余商品入库",c0622 "剩余商品报损"
from c05,c06,vc01 where c0501=c0601 and c0602=c0101
and to_char(c0538,'yyyy.mm.dd')
between '2009.1.1' and '2009.2.28'
------------------------------------------------------------

Statement Id=7 Type=SORT
Cost=4827 TimeStamp=20-04-10::16::02:13

(1) SELECT STATEMENT CHOOSE
Est. Rows: 35,042 Cost: 15,955
(9) MERGE JOIN
Est. Rows: 35,042 Cost: 15,955
(6) SORT JOIN
Est. Rows: 35,042 Cost: 11,128
(5) TABLE ACCESS BY INDEX ROWID FZDC.C06 [Analyzed]
(5) Blocks: 34,464 Est. Rows: 35 of 2,411,508 Cost: 9
Tablespace: FZ_DAT
(4) NESTED LOOPS
Est. Rows: 35,042 Cost: 10,342
(2) TABLE ACCESS FULL FZDC.C05 [Analyzed]
(2) Blocks: 7,944 Est. Rows: 1,015 of 406,159 Cost: 1,207
Tablespace: FZ_DAT
(3) UNIQUE INDEX RANGE SCAN FZDC.SYS_C005366 [Analyzed]
Est. Rows: 35 Cost: 2
(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

/////////////////////////////////////////////////////////////////////////////////////////////////////////

SQL Statement from editor:

select c0602 "商品编码",c0625 "商品条码",
c0103 "商品名称",c0104 "规格",c0604 "配货件数",
c0605 "配货数量",c0618 "计划赠品数",c0606 "实际出库数",
c0621 "出库赠品数",c0610 "门店实收数",c0611 "实收赠品数",
c0609 "剩余商品入库",c0622 "剩余商品报损"
from c05,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=20-04-10::16::08:10

(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
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

SQL Statement from editor:

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=20-04-10::16::10:11

(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
//////////////////////////////////////////////////////////////////////////////////////////////////////////

SQL Statement from editor:

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=INDEX
Cost=1 TimeStamp=20-04-10::16::13:41

(1) SELECT STATEMENT CHOOSE
Est. Rows: 23,043 Cost: 6,689
(10) MERGE JOIN
Est. Rows: 23,043 Cost: 6,689
(7) SORT JOIN
Est. Rows: 23,043 Cost: 1,862
(6) TABLE ACCESS BY INDEX ROWID FZDC.C06 [Analyzed]
(6) Blocks: 34,464 Est. Rows: 35 of 2,411,508 Cost: 2
Tablespace: FZ_DAT
(5) NESTED LOOPS
Est. Rows: 23,043 Cost: 1,341
(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) NON-UNIQUE INDEX RANGE SCAN FZDC.IND_C0601 [Not Analyzed]
Est. Rows: 35 Cost: 1
(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
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

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=TABLE ACCESS
Cost=5 TimeStamp=20-04-10::16::15:33

(1) SELECT STATEMENT CHOOSE
Est. Rows: 23,043 Cost: 6,193
(11) MERGE JOIN
Est. Rows: 23,043 Cost: 6,193
(8) SORT JOIN
Est. Rows: 23,043 Cost: 1,366
(7) MERGE JOIN
Est. Rows: 23,043 Cost: 845
(3) TABLE ACCESS BY INDEX ROWID FZDC.C06 [Analyzed]
(3) Blocks: 34,464 Est. Rows: 2,411,508 of 2,411,508 Cost: 826
Tablespace: FZ_DAT
(2) NON-UNIQUE INDEX FULL SCAN FZDC.IND_C0601 [Not Analyzed]
Est. Rows: 2,411,508 Cost: 26
(6) SORT JOIN
Est. Rows: 668 Cost: 19
(5) TABLE ACCESS BY INDEX ROWID FZDC.C05 [Analyzed]
(5) Blocks: 7,944 Est. Rows: 668 of 406,159 Cost: 5
Tablespace: FZ_DAT
(4) NON-UNIQUE INDEX RANGE SCAN FZDC.IND_C05_C0538 [Not Analyzed]
Est. Rows: 1 Cost: 2
(10) SORT JOIN
Est. Rows: 305,274 Cost: 4,827
(9) TABLE ACCESS FULL FZDC.C01 [Analyzed]
(9) Blocks: 10,492 Est. Rows: 305,274 of 305,274 Cost: 1,594
Tablespace: FZ_DAT
///////////////////////////////////////////////////////////////////////////////////////////////