对pl/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 n5010 between to_date('20100401','yyyymmdd') and to_date('20100430','yyyymmdd')
group by n5001,n5002,n5004,n5019),
(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
where n5001=g08(+)
and n5019=g02(+)
and n5001=c01d00(+)
and n5019=c01d01(+)

SELECT STATEMENT, GOAL = CHOOSE Cost=10163 Cardinality=30832 Bytes=3730672
MERGE JOIN OUTER Cost=10163 Cardinality=30832 Bytes=3730672
MERGE JOIN OUTER Cost=1794 Cardinality=30832 Bytes=2959872
SORT JOIN Cost=1743 Cardinality=30832 Bytes=1880752
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
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
SORT JOIN Cost=8369 Cardinality=572753 Bytes=14318825
TABLE ACCESS FULL Object owner=FZDC Object name=C01D Cost=2601 Cardinality=572753 Bytes=14318825

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

经过查看c01d 表发现

SQL> select * from user_ind_columns where table_name='C01D';

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------- ----------- -------
IND_C01D12 C01D C01D12 1 22 0 ASC
IND_C01D_C0109 C01D C01D09 1 6 6 ASC
IND_C01D_C0126 C01D C01D26 1 6 6 ASC
IND_C01D_C0133 C01D C01D33 1 8 8 ASC
IND_C01D_C0165 C01D C01D65 1 6 6 ASC
SYS_C003420 C01D C01D00 1 2 2 ASC
SYS_C003420 C01D C01D01 2 8 8 ASC

7 rows selected
///////////////////////////////////////////////////////

c01d00 和 c01d01 的索引名称一样。

select * from c01d where c01d00='13'

SELECT STATEMENT, GOAL = CHOOSE Cost=2601 Cardinality=52068 Bytes=11090484
TABLE ACCESS FULL Object owner=FZDC Object name=C01D Cost=2601 Cardinality=52068 Bytes=11090484

select * from c01d where c01d01='13'

SELECT STATEMENT, GOAL = CHOOSE Cost=15 Cardinality=4 Bytes=852
TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=C01D Cost=15 Cardinality=4 Bytes=852
INDEX SKIP SCAN Object owner=FZDC Object name=SYS_C003420 Cost=13 Cardinality=4

c01d00 没有走索引。

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

SQL> select * from user_indexes where table_name='C01D';

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ---------- --------------- ----------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ----------
GJH_C01D01 NORMAL FZDC C01D TABLE NONUNIQUE DISABLED FZ_DAT 2 255 65536 1 2147483645 10 YES VALID 1 1 NO N N N DEFAULT NO NO NO
IND_C01D12 NORMAL FZDC C01D TABLE NONUNIQUE DISABLED FZ_DAT 2 255 15867904 1 2147483645 10 YES 2 1094 3 364 152 249979 VALID 582008 2500 2010-4-12 6:2 1 1 NO N N N DEFAULT NO NO NO
IND_C01D_C0109 NORMAL FZDC C01D TABLE NONUNIQUE DISABLED FZ_DAT 2 255 19054592 1 2147483645 10 YES 2 1472 4048 1 26 106720 VALID 585856 2500 2010-4-12 6:2 1 1 NO N N N DEFAULT NO NO NO
IND_C01D_C0126 NORMAL FZDC C01D TABLE NONUNIQUE DISABLED FZ_DAT 2 255 14868480 1 2147483645 10 YES 2 1309 3 436 54 71122 VALID 586432 2500 2010-4-12 6:2 1 1 NO N N N DEFAULT NO NO NO
IND_C01D_C0133 NORMAL FZDC C01D TABLE NONUNIQUE DISABLED FZ_DAT 2 255 26558464 1 2147483645 10 YES 2 1631 13 125 33 232145 VALID 586072.666 2500 2010-4-12 6:2 1 1 NO N N N DEFAULT NO NO NO
IND_C01D_C0165 NORMAL FZDC C01D TABLE NONUNIQUE DISABLED FZ_DAT 2 255 25788416 1 2147483645 10 YES 2 1472 3680 1 35 132112 VALID 585856 2500 2010-4-12 6:2 1 1 NO N N N DEFAULT NO NO NO
SYS_C003420 NORMAL FZDC C01D TABLE UNIQUE DISABLED FZ_DAT 2 255 20234240 1 2147483645 10 YES 2 1777 579302 1 1 240339 VALID 579302 2500 2010-4-12 6:2 1 1 NO N N N DEFAULT NO NO NO

7 rows selected

原来表中建了一个聚组索引。

create index gjh_c01d00 on c01d(c01d00)

以后还是不行。

所以用了强化索引 select /*+index (c01d SYS_C003420)*/ * from c01d where c01d01='13'

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

执行的时间：

1. 17.953

2. 14.375

3. 12.562

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(+)

SELECT STATEMENT, GOAL = CHOOSE Cost=4231 Cardinality=30832 Bytes=3730672
HASH JOIN OUTER Cost=4231 Cardinality=30832 Bytes=3730672
HASH JOIN OUTER Cost=4146 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 FULL Object owner=FZDC Object name=C01D Cost=2601 Cardinality=572753 Bytes=14318825
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

执行时间:

18.219

10.984

9.328

如果大家认为还可以优化请给出建议。