select /*+index(c01d SYS_C003420)*/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('20100430','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('20100430','yyyymmdd')
group by n5001,n5002,n5004,n5019)
where n5001=g08(+)
and n5019=g02(+)
and n5001=c01d00(+)
and n5019=c01d01(+)SELECT STATEMENT, GOAL = CHOOSE Cost=32884 Cardinality=30832 Bytes=3730672
MERGE JOIN OUTER Cost=32884 Cardinality=30832 Bytes=3730672
SORT JOIN Cost=32833 Cardinality=30832 Bytes=2651552
NESTED LOOPS OUTER Cost=31974 Cardinality=30832 Bytes=2651552
VIEW Object owner=FZDC Cost=1098 Cardinality=30832 Bytes=1880752
SORT GROUP BY Cost=1098 Cardinality=30832 Bytes=1171616
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=N50 Cost=668 Cardinality=30832 Bytes=1171616
INDEX RANGE SCAN Object owner=FZDC Object name=IND_N500312_N5010 Cost=86 Cardinality=30832
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
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 执行的时间为 12--15 秒之间
MERGE JOIN OUTER Cost=32884 Cardinality=30832 Bytes=3730672
SORT JOIN Cost=32833 Cardinality=30832 Bytes=2651552
NESTED LOOPS OUTER Cost=31974 Cardinality=30832 Bytes=2651552
VIEW Object owner=FZDC Cost=1098 Cardinality=30832 Bytes=1880752
SORT GROUP BY Cost=1098 Cardinality=30832 Bytes=1171616
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=N50 Cost=668 Cardinality=30832 Bytes=1171616
INDEX RANGE SCAN Object owner=FZDC Object name=IND_N500312_N5010 Cost=86 Cardinality=30832
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
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
NESTED LOOPS OUTER Cost=31974 Cardinality=30832 Bytes=2651552
c01d 6万batchgoods 4万
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('20100430', 'yyyymmdd')
group by g08, g09, g02
) t1,
c01d t2,
(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('20100430', 'yyyymmdd')
group by n5001, n5002, n5004, n5019
) t3
where n5001 = g08(+)
and n5019 = g02(+)
and n5001 = c01d00(+)
and n5019 = c01d01(+)
SQL Statement from editor:
select /*+ use_hash(t1 t2 t3) */
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('20100430', 'yyyymmdd')
group by g08, g09, g02
) t1,
c01d t2,
(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('20100430', 'yyyymmdd')
group by n5001, n5002, n5004, n5019
) t3
where n5001 = g08(+)
and n5019 = g02(+)
and n5001 = c01d00(+)
and n5019 = c01d01(+)
------------------------------------------------------------
Statement Id=4203172 Type=
Cost=2.64018716311899E-308 TimeStamp=20-04-10::11::24:27
(1) SELECT STATEMENT CHOOSE
Est. Rows: 30,832 Cost: 2,336
(12) HASH JOIN OUTER
Est. Rows: 30,832 Cost: 2,336
(7) HASH JOIN OUTER
Est. Rows: 30,832 Cost: 2,276
(5) VIEW (Embedded SQL)
Est. Rows: 30,832 Cost: 874
(4) SORT GROUP BY
Est. Rows: 30,832 Cost: 874
(3) TABLE ACCESS BY INDEX ROWID FZDC.N50 [Analyzed]
(3) Blocks: 26,048 Est. Rows: 30,832 of 1,572,452 Cost: 668
Tablespace: FZ_DAT
(2) NON-UNIQUE INDEX RANGE SCAN FZDC.IND_N500312_N5010 [Analyzed]
Est. Rows: 30,832 Cost: 86
(6) TABLE ACCESS FULL FZDC.C01D [Analyzed]
(6) Blocks: 17,124 Est. Rows: 572,753 of 572,753 Cost: 1,045
Tablespace: FZ_DAT
(11) VIEW (Embedded SQL)
Est. Rows: 1,093 Cost: 13
(10) SORT GROUP BY
Est. Rows: 1,093 Cost: 13
(9) TABLE ACCESS BY INDEX ROWID FZDC.BATCHGOODS [Not Analyzed]
(9) Est. Rows: 1,093 Cost: 6
Tablespace: FZ_DAT
(8) NON-UNIQUE INDEX RANGE SCAN FZDC.IND_BATGD2 [Not Analyzed]
Est. Rows: 1,967 Cost: 2
select /*+ use_hash(t1 t2 t3) */
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('20100430', 'yyyymmdd')
group by g08, g09, g02
) t1,
c01d t2,
(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('20100430', 'yyyymmdd')
group by n5001, n5002, n5004, n5019
) t3
where n5001 = g08(+)
and n5019 = g02(+)
and n5001 = c01d00(+)
and n5019 = c01d01(+)
------------------------------------------------------------
Statement Id=4203172 Type=
Cost=2.64018716311899E-308 TimeStamp=20-04-10::11::24:27
(1) SELECT STATEMENT CHOOSE
Est. Rows: 30,832 Cost: 2,336
(12) HASH JOIN OUTER
Est. Rows: 30,832 Cost: 2,336
(7) HASH JOIN OUTER
Est. Rows: 30,832 Cost: 2,276
(5) VIEW (Embedded SQL)
Est. Rows: 30,832 Cost: 874
(4) SORT GROUP BY
Est. Rows: 30,832 Cost: 874
(3) TABLE ACCESS BY INDEX ROWID FZDC.N50 [Analyzed]
(3) Blocks: 26,048 Est. Rows: 30,832 of 1,572,452 Cost: 668
Tablespace: FZ_DAT
(2) NON-UNIQUE INDEX RANGE SCAN FZDC.IND_N500312_N5010 [Analyzed]
Est. Rows: 30,832 Cost: 86
(6) TABLE ACCESS FULL FZDC.C01D [Analyzed]
(6) Blocks: 17,124 Est. Rows: 572,753 of 572,753 Cost: 1,045
Tablespace: FZ_DAT
(11) VIEW (Embedded SQL)
Est. Rows: 1,093 Cost: 13
(10) SORT GROUP BY
Est. Rows: 1,093 Cost: 13
(9) TABLE ACCESS BY INDEX ROWID FZDC.BATCHGOODS [Not Analyzed]
(9) Est. Rows: 1,093 Cost: 6
Tablespace: FZ_DAT
(8) NON-UNIQUE INDEX RANGE SCAN FZDC.IND_BATGD2 [Not Analyzed]
Est. Rows: 1,967 Cost: 2
(6) Blocks: 17,124 Est. Rows: 572,753 of 572,753 Cost: 1,045
Tablespace: FZ_DAT
还能优化吗?