对pl/sql 语句的优化（上集）

最新推荐文章于 2025-04-22 17:02:59 发布

最新推荐文章于 2025-04-22 17:02:59 发布 · 75 阅读

本文介绍了一种通过修改SQL查询语句提高查询效率的方法。通过对日期字段使用更有效的筛选方式，并调整表连接顺序，显著减少了查询时间和资源消耗。

对这个进行优化

select n5001 门店,n5002 部门,n5004 小分类,n5019 商品编码,c01d21 商品名称,
nvl(xse,0)-nvl(dzxs,0) 销售,
mle 毛利,
nvl(xl,0)-nvl(dzsl,0) 销量
from
(select n5001,n5002,n5004,n5019,sum(n5011) xse,sum(n5016) mle,sum(n5023) xl
from n50
where to_char(n5010,'yyyymmdd')>='20100401'
and to_char(n5010,'yyyymmdd')<='20100431'
group by n5001,n5002,n5004,n5019),
(select g08,g09,g02,sum(g03*g07) dzxs,sum(G03) dzsl from batchgoods
where to_char(g04,'yyyymmdd')>='20100401'
and to_char(g04,'yyyymmdd')<='20100431'
group by g08,g09,g02),c01d
where n5001=g08(+)
and n5019=g02(+)
and n5001=c01d00(+)
and n5019=c01d01(+)

执行计划

SELECT STATEMENT, GOAL = CHOOSE Cost=10060 Cardinality=3931 Bytes=475651
MERGE JOIN OUTER Cost=10060 Cardinality=3931 Bytes=475651
SORT JOIN Cost=8063 Cardinality=3931 Bytes=338066
NESTED LOOPS OUTER Cost=7952 Cardinality=3931 Bytes=338066
VIEW Object owner=FZDC Cost=4015 Cardinality=3931 Bytes=239791
SORT GROUP BY Cost=4015 Cardinality=3931 Bytes=149378
TABLE ACCESS FULL Object owner=FZDC Object name=N50 Cost=3955 Cardinality=3931 Bytes=149378
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=C01D Cost=2 Cardinality=1 Bytes=25
INDEX RANGE SCAN Object owner=FZDC Object name=SYS_C003420 Cost=1 Cardinality=1
SORT JOIN Cost=1997 Cardinality=1308 Bytes=45780
VIEW Object owner=FZDC Cost=1972 Cardinality=1308 Bytes=45780
SORT GROUP BY Cost=1972 Cardinality=1308 Bytes=62784
TABLE ACCESS FULL Object owner=FZDC Object name=BATCHGOODS Cost=1952 Cardinality=1308 Bytes=62784

查看了一个

select n5001,n5002,n5004,n5019,sum(n5011) xse,sum(n5016) mle,sum(n5023) xl
from n50
where to_char(n5010,'yyyymmdd')>='20100401'
and to_char(n5010,'yyyymmdd')<='20100431'
group by n5001,n5002,n5004,n5019

其中n5010 是日期已经走全表扫描了。

SELECT STATEMENT, GOAL = CHOOSE Cost=4015 Cardinality=3931 Bytes=149378
SORT GROUP BY Cost=4015 Cardinality=3931 Bytes=149378
TABLE ACCESS FULL Object owner=FZDC Object name=N50 Cost=3955 Cardinality=3931 Bytes=149378

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

改为

select n5001,n5002,n5004,n5019,sum(n5011) xse,sum(n5016) mle,sum(n5023) xl
from n50
where n5010 between to_date('20100401','yyyymmdd') and to_date('20100431','yyyymmdd')
group by n5001,n5002,n5004,n5019

SELECT STATEMENT, GOAL = CHOOSE Cost=1727 Cardinality=1542 Bytes=58596
SORT GROUP BY Cost=1727 Cardinality=1542 Bytes=58596
FILTER
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=N50 Cost=1698 Cardinality=1542 Bytes=58596
INDEX RANGE SCAN Object owner=FZDC Object name=IND_N500312_N5010 Cost=214 Cardinality=78623
//////////////////////////////////////////////////////////////////

同理

select g08,g09,g02,sum(g03*g07) dzxs,sum(G03) dzsl from batchgoods
where
g04 between to_date('20100401','yyyymmdd') and to_date('20100431','yyyymmdd')
group by g08,g09,g02

改后

SELECT STATEMENT, GOAL = CHOOSE
SORT GROUP BY
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=BATCHGOODS
INDEX RANGE SCAN Object owner=FZDC Object name=IND_BATGD2
///////////////////////////////////////////////////////////////

修改完以后:

SELECT STATEMENT, GOAL = CHOOSE Cost=3372 Cardinality=1542 Bytes=186582
MERGE JOIN OUTER Cost=3372 Cardinality=1542 Bytes=186582
SORT JOIN Cost=3321 Cardinality=1542 Bytes=132612
NESTED LOOPS OUTER Cost=3272 Cardinality=1542 Bytes=132612
VIEW Object owner=FZDC Cost=1727 Cardinality=1542 Bytes=94062
SORT GROUP BY Cost=1727 Cardinality=1542 Bytes=58596
FILTER
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=N50 Cost=1698 Cardinality=1542 Bytes=58596
INDEX RANGE SCAN Object owner=FZDC Object name=IND_N500312_N5010 Cost=214 Cardinality=78623
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=C01D Cost=2 Cardinality=1 Bytes=25
INDEX RANGE SCAN Object owner=FZDC Object name=SYS_C003420 Cost=1 Cardinality=1
SORT JOIN Cost=51 Cardinality=1308 Bytes=45780
VIEW Object owner=FZDC Cost=26 Cardinality=1308 Bytes=45780
SORT GROUP BY Cost=26 Cardinality=1308 Bytes=62784
FILTER
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=BATCHGOODS Cost=6 Cardinality=1308 Bytes=62784
INDEX RANGE SCAN Object owner=FZDC Object name=IND_BATGD2 Cost=2 Cardinality=2355
//////////////////////////////////////////////////////////

时间上的改变:

之前是:

1 41.454

2 30.75

之后:

1 22.406

2 17.297

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

SQL> select count(*) from n50;

COUNT(*)
----------
1903776

SQL> select count(*) from c01d;

COUNT(*)
----------
697547

SQL> select count(*) from batchgoods;

COUNT(*)
----------
603340

SQL>

把主从表的顺序排一下。

select n5001 门店,n5002 部门,n5004 小分类,n5019 商品编码,c01d21 商品名称,
nvl(xse,0)-nvl(dzxs,0) 销售,
mle 毛利,
nvl(xl,0)-nvl(dzsl,0) 销量
from
(select g08,g09,g02,sum(g03*g07) dzxs,sum(G03) dzsl from batchgoods
where g04 between to_date('20100401','yyyymmdd') and to_date('20100431','yyyymmdd')
group by g08,g09,g02),
c01d,(select n5001,n5002,n5004,n5019,sum(n5011) xse,sum(n5016) mle,sum(n5023) xl
from n50
where n5010 between to_date('20100401','yyyymmdd') and to_date('20100431','yyyymmdd')
group by n5001,n5002,n5004,n5019)
where n5001=g08(+)
and n5019=g02(+)
and n5001=c01d00(+)
and n5019=c01d01(+)

时间上:

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

1。 23。406

2。 15。844

3。 14。875

下集接着优化。。。哈哈