最近在做一个大型系统的维护,现在客户要求提高检索数据的速度,情况是每次根据用户输入的检索条件,进行检索,检索后的数据生成CSV文件。本人数据库方面实在是很差,业务也不是很明白,关于提速方面,请高人指点。
现在有四个表,DAWTR080,DAWTR090,DAWTR110,DAWTR130,其中
DAWTR080有2000万条数据,主键为SAKUSEI_YM,SEIKYU_NO,SEIKYU_SNO,为共52列
DAWTR090有200万条数据,主键为SEIKYU_NO,SEIKYU_SNO,共71列
DAWTR110有9万条数据,主键为SEIKYU_NO,SEIKYU_SNO,COMMENT_SNO,共13列
DAWRE130有50万条数据,主键为SET_KEY,共13列
现在检索SQL语句如下
SELECT /*+ RULE */
XXXXXXX (50列)
FROM
DAWTR080 MIZAN_A
,DAWTR090 HEAD_A
,DAWTR090 HEAD_B
,DAWTR090 HEAD_C
,DAWTR110 COMENT_A
,DAWTR110 COMENT_B
,DAWTR110 COMENT_C
,DAWTR080 MIZAN_B
,DAWTR130 CHUBAN
WHERE
MIZAN_A.SAKUSEI_YM = '200408' –输入检索条件项目,必须输入
AND MIZAN_B.SAKUSEI_YM(+) = '200408' –输入检索条件项目,必须输入
and MIZAN_A.URIKAN_CD='D001' —输入检索条件项目
and MIZAN_A.HOJIN_CD='0002278' —输入检索条件项目
AND MIZAN_A.SEIKYU_NO = HEAD_A.SEIKYU_NO
AND MIZAN_A.SEIKYU_SNO = HEAD_A.SEIKYU_SNO
AND MIZAN_A.SEIKYU_NO = COMENT_A.SEIKYU_NO(+)
AND MIZAN_A.SEIKYU_SNO = COMENT_A.SEIKYU_SNO(+)
AND MIZAN_A.COMMENT_SNO = COMENT_A.COMMENT_SNO(+)
AND HEAD_A.MAEUKE_NO = HEAD_B.SEIKYU_NO(+)
AND HEAD_A.MAEUKE_SNO = HEAD_B.SEIKYU_SNO(+)
AND HEAD_B.SEIKYU_NO = MIZAN_B.SEIKYU_NO(+)
AND HEAD_B.SEIKYU_SNO = MIZAN_B.SEIKYU_SNO(+)
AND MIZAN_B.SEIKYU_NO = COMENT_B.SEIKYU_NO(+)
AND MIZAN_B.SEIKYU_SNO = COMENT_B.SEIKYU_SNO(+)
AND MIZAN_B.COMMENT_SNO = COMENT_B.COMMENT_SNO(+)
AND HEAD_A.IKKATU_NO = HEAD_C.SEIKYU_NO(+)
AND HEAD_C.SEIKYU_SNO(+) = '001'
AND HEAD_C.SEIKYU_NO = COMENT_C.SEIKYU_NO(+)
AND HEAD_C.SEIKYU_SNO = COMENT_C.SEIKYU_SNO(+)
AND HEAD_C.COMMENT_SNO = COMENT_C.COMMENT_SNO(+)
红色部分(前四项)是每次输入的检索条件,每次会产生变化,现在的执行需要时间是40秒左右,但是如果输入条件减少的话,时间大幅度增长,现在想问一下有什么方法可以提高检索速度,一下是ORACLE的执行计划:経過: 00:00:41.03実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS (OUTER)
2 1 MERGE JOIN (OUTER)
3 2 SORT (JOIN)
4 3 NESTED LOOPS (OUTER)
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS (OUTER)
7 6 NESTED LOOPS (OUTER)
8 7 NESTED LOOPS (OUTER)
9 8 NESTED LOOPS
10 9 NESTED LOOPS (OUTER)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'DAWT
R080' 12 11 AND-EQUAL
13 12 INDEX (RANGE SCAN) OF 'IX_DAWTR080
_04' (NON-UNIQUE) 14 12 INDEX (RANGE SCAN) OF 'IX_DAWTR080
_08' (NON-UNIQUE) 15 10 TABLE ACCESS (BY INDEX ROWID) OF 'DAWT
R110' 16 15 INDEX (UNIQUE SCAN) OF 'PK_DAWTR110'
(UNIQUE) 17 9 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR0
90' 18 17 INDEX (UNIQUE SCAN) OF 'PK_DAWTR090' (
UNIQUE) 19 8 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR090
' 20 19 INDEX (UNIQUE SCAN) OF 'PK_DAWTR090' (UN
IQUE) 21 7 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR110'
22 21 INDEX (UNIQUE SCAN) OF 'PK_DAWTR110' (UNIQ
UE) 23 6 INDEX (UNIQUE SCAN) OF 'PK_DAWTR090' (UNIQUE)
24 5 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR080'
25 24 INDEX (UNIQUE SCAN) OF 'PK_DAWTR080' (UNIQUE)
26 4 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR110'
27 26 INDEX (UNIQUE SCAN) OF 'PK_DAWTR110' (UNIQUE)
28 2 SORT (JOIN)
29 28 VIEW
30 29 SORT (GROUP BY)
31 30 TABLE ACCESS (FULL) OF 'DAWTR130'
32 1 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR130'
33 32 INDEX (UNIQUE SCAN) OF 'PK_DAWTR130' (UNIQUE)
統計
----------------------------------------------------------
5 recursive calls
29 db block gets
189831 consistent gets
18714 physical reads
0 redo size
8774685 bytes sent via SQL*Net to client
197702 bytes received via SQL*Net from client
1454 SQL*Net roundtrips to/from client
0 sorts (memory)
3 sorts (disk)
21794 rows processed
现在有四个表,DAWTR080,DAWTR090,DAWTR110,DAWTR130,其中
DAWTR080有2000万条数据,主键为SAKUSEI_YM,SEIKYU_NO,SEIKYU_SNO,为共52列
DAWTR090有200万条数据,主键为SEIKYU_NO,SEIKYU_SNO,共71列
DAWTR110有9万条数据,主键为SEIKYU_NO,SEIKYU_SNO,COMMENT_SNO,共13列
DAWRE130有50万条数据,主键为SET_KEY,共13列
现在检索SQL语句如下
SELECT /*+ RULE */
XXXXXXX (50列)
FROM
DAWTR080 MIZAN_A
,DAWTR090 HEAD_A
,DAWTR090 HEAD_B
,DAWTR090 HEAD_C
,DAWTR110 COMENT_A
,DAWTR110 COMENT_B
,DAWTR110 COMENT_C
,DAWTR080 MIZAN_B
,DAWTR130 CHUBAN
WHERE
MIZAN_A.SAKUSEI_YM = '200408' –输入检索条件项目,必须输入
AND MIZAN_B.SAKUSEI_YM(+) = '200408' –输入检索条件项目,必须输入
and MIZAN_A.URIKAN_CD='D001' —输入检索条件项目
and MIZAN_A.HOJIN_CD='0002278' —输入检索条件项目
AND MIZAN_A.SEIKYU_NO = HEAD_A.SEIKYU_NO
AND MIZAN_A.SEIKYU_SNO = HEAD_A.SEIKYU_SNO
AND MIZAN_A.SEIKYU_NO = COMENT_A.SEIKYU_NO(+)
AND MIZAN_A.SEIKYU_SNO = COMENT_A.SEIKYU_SNO(+)
AND MIZAN_A.COMMENT_SNO = COMENT_A.COMMENT_SNO(+)
AND HEAD_A.MAEUKE_NO = HEAD_B.SEIKYU_NO(+)
AND HEAD_A.MAEUKE_SNO = HEAD_B.SEIKYU_SNO(+)
AND HEAD_B.SEIKYU_NO = MIZAN_B.SEIKYU_NO(+)
AND HEAD_B.SEIKYU_SNO = MIZAN_B.SEIKYU_SNO(+)
AND MIZAN_B.SEIKYU_NO = COMENT_B.SEIKYU_NO(+)
AND MIZAN_B.SEIKYU_SNO = COMENT_B.SEIKYU_SNO(+)
AND MIZAN_B.COMMENT_SNO = COMENT_B.COMMENT_SNO(+)
AND HEAD_A.IKKATU_NO = HEAD_C.SEIKYU_NO(+)
AND HEAD_C.SEIKYU_SNO(+) = '001'
AND HEAD_C.SEIKYU_NO = COMENT_C.SEIKYU_NO(+)
AND HEAD_C.SEIKYU_SNO = COMENT_C.SEIKYU_SNO(+)
AND HEAD_C.COMMENT_SNO = COMENT_C.COMMENT_SNO(+)
红色部分(前四项)是每次输入的检索条件,每次会产生变化,现在的执行需要时间是40秒左右,但是如果输入条件减少的话,时间大幅度增长,现在想问一下有什么方法可以提高检索速度,一下是ORACLE的执行计划:経過: 00:00:41.03実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS (OUTER)
2 1 MERGE JOIN (OUTER)
3 2 SORT (JOIN)
4 3 NESTED LOOPS (OUTER)
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS (OUTER)
7 6 NESTED LOOPS (OUTER)
8 7 NESTED LOOPS (OUTER)
9 8 NESTED LOOPS
10 9 NESTED LOOPS (OUTER)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'DAWT
R080' 12 11 AND-EQUAL
13 12 INDEX (RANGE SCAN) OF 'IX_DAWTR080
_04' (NON-UNIQUE) 14 12 INDEX (RANGE SCAN) OF 'IX_DAWTR080
_08' (NON-UNIQUE) 15 10 TABLE ACCESS (BY INDEX ROWID) OF 'DAWT
R110' 16 15 INDEX (UNIQUE SCAN) OF 'PK_DAWTR110'
(UNIQUE) 17 9 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR0
90' 18 17 INDEX (UNIQUE SCAN) OF 'PK_DAWTR090' (
UNIQUE) 19 8 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR090
' 20 19 INDEX (UNIQUE SCAN) OF 'PK_DAWTR090' (UN
IQUE) 21 7 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR110'
22 21 INDEX (UNIQUE SCAN) OF 'PK_DAWTR110' (UNIQ
UE) 23 6 INDEX (UNIQUE SCAN) OF 'PK_DAWTR090' (UNIQUE)
24 5 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR080'
25 24 INDEX (UNIQUE SCAN) OF 'PK_DAWTR080' (UNIQUE)
26 4 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR110'
27 26 INDEX (UNIQUE SCAN) OF 'PK_DAWTR110' (UNIQUE)
28 2 SORT (JOIN)
29 28 VIEW
30 29 SORT (GROUP BY)
31 30 TABLE ACCESS (FULL) OF 'DAWTR130'
32 1 TABLE ACCESS (BY INDEX ROWID) OF 'DAWTR130'
33 32 INDEX (UNIQUE SCAN) OF 'PK_DAWTR130' (UNIQUE)
統計
----------------------------------------------------------
5 recursive calls
29 db block gets
189831 consistent gets
18714 physical reads
0 redo size
8774685 bytes sent via SQL*Net to client
197702 bytes received via SQL*Net from client
1454 SQL*Net roundtrips to/from client
0 sorts (memory)
3 sorts (disk)
21794 rows processed
XXXXXXX (50列)
FROM
DAWTR080 MIZAN_A
,DAWTR090 HEAD_A
,DAWTR090 HEAD_B
,DAWTR090 HEAD_C
,DAWTR110 COMENT_A
,DAWTR110 COMENT_B
,DAWTR110 COMENT_C
,DAWTR080 MIZAN_B
,DAWTR130 CHUBAN
WHERE
MIZAN_A.SEIKYU_NO = HEAD_A.SEIKYU_NO
AND MIZAN_A.SEIKYU_SNO = HEAD_A.SEIKYU_SNO
AND HEAD_A.MAEUKE_NO = HEAD_B.SEIKYU_NO(+)
AND HEAD_A.MAEUKE_SNO = HEAD_B.SEIKYU_SNO(+)
AND HEAD_A.IKKATU_NO = HEAD_C.SEIKYU_NO(+)
AND HEAD_B.SEIKYU_NO = MIZAN_B.SEIKYU_NO(+)
AND HEAD_B.SEIKYU_SNO = MIZAN_B.SEIKYU_SNO(+)
AND MIZAN_B.SEIKYU_NO = COMENT_B.SEIKYU_NO(+)
AND MIZAN_B.SEIKYU_SNO = COMENT_B.SEIKYU_SNO(+)
AND MIZAN_B.COMMENT_SNO = COMENT_B.COMMENT_SNO(+)
AND MIZAN_A.SEIKYU_NO = COMENT_A.SEIKYU_NO(+)
AND MIZAN_A.SEIKYU_SNO = COMENT_A.SEIKYU_SNO(+)
AND MIZAN_A.COMMENT_SNO = COMENT_A.COMMENT_SNO(+)
AND HEAD_C.SEIKYU_NO = COMENT_C.SEIKYU_NO(+)
AND HEAD_C.SEIKYU_SNO = COMENT_C.SEIKYU_SNO(+)
AND HEAD_C.COMMENT_SNO = COMENT_C.COMMENT_SNO(+)
and MIZAN_A.URIKAN_CD='D001' —输入检索条件项目
and MIZAN_A.HOJIN_CD='0002278' —输入检索条件项目
AND HEAD_C.SEIKYU_SNO(+) = '001'
AND MIZAN_B.SAKUSEI_YM(+) = '200408' –输入检索条件项目,必须输入
AND MIZAN_A.SAKUSEI_YM = '200408' –输入检索条件项目,必须输入
这样速度会成倍的增加. 如果把URIKAN_CD,HOJIN_CD设为索引效果更佳.
DAWTR080 MIZAN_A
,DAWTR080 MIZAN_B
,DAWTR090 HEAD_A
,DAWTR090 HEAD_B
,DAWTR090 HEAD_C
,DAWTR130 CHUBAN
,DAWTR110 COMENT_A
,DAWTR110 COMENT_B
,DAWTR110 COMENT_C where .....再改进下.运行应该不会操作十秒.