配送已到货订单号查询 sql 语句优化

最新推荐文章于 2022-10-10 09:43:33 发布

最新推荐文章于 2022-10-10 09:43:33 发布 · 276 阅读

本文介绍了一个具体的SQL查询优化过程，包括原始查询语句及其执行计划、通过创建索引改进后的查询语句及其新的执行计划。展示了如何通过创建索引减少查询成本并提高效率。

select c0501 "订单编号",
c0503 "供应商编码",a0302 "供应商名称",
to_char(c0515,'yyyy.mm.dd') "订货日期",
to_char(c0516,'yyyy.mm.dd') "预定交货日期"
from c05,a03 where c0503=a0301 and
c0502='01' and to_char(c0515,'yyyy.mm.dd')
between '2009.02.01' and '2009.02.28' and c0523='4'
SELECT STATEMENT, GOAL = CHOOSE Cost=500 Cardinality=1059

Bytes=69894
HASH JOIN Cost=500 Cardinality=1059 Bytes=69894
TABLE ACCESS FULL Object owner=FZDC Object name=C05 Cost=486

Cardinality=23 Bytes=667
TABLE ACCESS FULL Object owner=FZDC Object name=A03 Cost=13 Cardinality=15193

Bytes=562141

-------------------------1.75
-------------------------1.75
-------------------------1.75
create index IND_C05_C0515 on c05(c0515)
analyze table c05 compute statistics

select c0501 "订单编号",
c0503 "供应商编码",a0302 "供应商名称",
to_char(c0515,'yyyy.mm.dd') "订货日期",
to_char(c0516,'yyyy.mm.dd') "预定交货日期"
from c05,a03 where c0503=a0301 and
c0502='01' and c0515
between to_date('2009.02.01','yyyy-mm-dd') and to_date('2009.02.28','yyyy-mm-dd')
and c0523='4'
SQL Statement from editor:

select c0501 "订单编号",
c0503 "供应商编码",a0302 "供应商名称",
to_char(c0515,'yyyy.mm.dd') "订货日期",
to_char(c0516,'yyyy.mm.dd') "预定交货日期"
from c05,a03 where c0503=a0301 and
c0502='01' and c0515
between to_date('2009.02.01','yyyy-mm-dd') and to_date('2009.02.28','yyyy-mm-dd')
and c0523='4'
------------------------------------------------------------

Statement Id=100 Type=
Cost=1.95941868211261E-307 TimeStamp=20-04-10::10::45:17

(1) SELECT STATEMENT CHOOSE
Est. Rows: 344 Cost: 153
(6) NESTED LOOPS
Est. Rows: 344 Cost: 153
(3) TABLE ACCESS BY INDEX ROWID FZDC.C05 [Analyzed]
(3) Blocks: 27,580 Est. Rows: 13 of 1,052,721 Cost: 140
Tablespace: FZ_DAT
(2) NON-UNIQUE INDEX RANGE SCAN FZDC.IND_C05_C0515 [Analyzed]
Est. Rows: 2,101 Cost: 6
(5) TABLE ACCESS BY INDEX ROWID FZDC.A03 [Not Analyzed]
(5) Est. Rows: 27 Cost: 1
Tablespace: FZ_DAT
(4) UNIQUE INDEX UNIQUE SCAN FZDC.SYS_C0012284 [Not Analyzed]
Est. Rows: 1
-----------------------------------0.125
-----------------------------------0.11
-----------------------------------0.094