不好意思,最近出差了,没有及时跟贴 执行查询语句是: select * from V_xsmx where 1=1 and gdsj>=TO_DATE('2005-01-12','YYYY-MM-DD') and gdsj<TO_DATE('2005-01-12','YYYY-MM-DD')+1 order by qymc,xmmc
解释计划结果如下: SELECT STATEMENT Optimizer=CHOOSE 0 SORT (ORDER BY) 1 FILTER 2 NESTED LOOPS 3 MERGE JOIN 4 SORT (JOIN) 5 MERGE JOIN (OUTER) 6 SORT (JOIN) 7 NESTED LOOPS (OUTER) 8 NESTED LOOPS (OUTER) 9 NESTED LOOPS (OUTER) 10 NESTED LOOPS (OUTER) 11 NESTED LOOPS (OUTER) 12 NESTED LOOPS (OUTER) 13 NESTED LOOPS (OUTER) 14 NESTED LOOPS (OUTER) 15 NESTED LOOPS (OUTER) 16 MERGE JOIN (OUTER) 17 SORT (JOIN) 18 NESTED LOOPS (OUTER) 19 TABLE ACCESS (FULL) OF 'T_KFQY_FYGL_LP' 19 INDEX (UNIQUE SCAN) OF 'T_BM_FWLX_PK' (UNIQUE) 17 SORT (JOIN) 22 TABLE ACCESS (FULL) OF 'T_KFQY_FYGL_HS' 16 INDEX (UNIQUE SCAN) OF 'PK _BADW_ZZDM' (UNIQUE) 15 TABLE ACCESS (BY INDEX ROWID ) OF 'T_BM_XFWYT' 25 INDEX (UNIQUE SCAN) OF 'T_ BM_XFWYT_PK' (UNIQUE) 14 INDEX (UNIQUE SCAN) OF 'T_BM_X FWXZ_PK' (UNIQUE) 13 INDEX (UNIQUE SCAN) OF 'T_BM_FWJ G_PK' (UNIQUE) 12 TABLE ACCESS (BY INDEX ROWID) OF ' T_BM_FWHX' 29 INDEX (UNIQUE SCAN) OF 'T_BM_FWH X_PK' (UNIQUE) 11 INDEX (UNIQUE SCAN) OF 'T_BM_FWYT_PK ' (UNIQUE) 10 TABLE ACCESS (BY INDEX ROWID) OF 'T_KF QY_XSXM' 32 INDEX (UNIQUE SCAN) OF 'T_KFQY_XSXM_ PK' (UNIQUE) 9 TABLE ACCESS (BY INDEX ROWID) OF 'T_KFQY _JBQK' 34 INDEX (UNIQUE SCAN) OF 'T_KFQY_JBQK_PK ' (UNIQUE) 8 TABLE ACCESS (BY INDEX ROWID) OF 'T_KFXM_X MGK' 36 INDEX (UNIQUE SCAN) OF 'T_KFXM_XMGK_PK' (UNIQUE) 6 SORT (JOIN) 38 TABLE ACCESS (FULL) OF 'T_BM_XFWLX' 4 SORT (JOIN) 40 TABLE ACCESS (FULL) OF 'T_HT_PAGE16' 3 TABLE ACCESS (BY INDEX ROWID) OF 'T_HT_PAGE1' 42 INDEX (UNIQUE SCAN) OF 'T_HT_PAGE1_PK' (UNIQUE)
select * from V_xsmx where 1=1 and gdsj>=TO_DATE('2005-01-12','YYYY-MM-DD') and gdsj<TO_DATE('2005-01-12','YYYY-MM-DD')+1 order by qymc,xmmc 这SQL还可以优化,不知道实际情况是怎样,我就不改了。但你在gdsj这上面建一个索引,速度有很大的提升。
那出sql看看最好.
必要是建立索引
我用的oracle8.0.5
执行查询语句是:
select * from V_xsmx where 1=1 and gdsj>=TO_DATE('2005-01-12','YYYY-MM-DD')
and gdsj<TO_DATE('2005-01-12','YYYY-MM-DD')+1 order by qymc,xmmc
SELECT STATEMENT Optimizer=CHOOSE
0 SORT (ORDER BY)
1 FILTER
2 NESTED LOOPS
3 MERGE JOIN
4 SORT (JOIN)
5 MERGE JOIN (OUTER)
6 SORT (JOIN)
7 NESTED LOOPS (OUTER)
8 NESTED LOOPS (OUTER)
9 NESTED LOOPS (OUTER)
10 NESTED LOOPS (OUTER)
11 NESTED LOOPS (OUTER)
12 NESTED LOOPS (OUTER)
13 NESTED LOOPS (OUTER)
14 NESTED LOOPS (OUTER)
15 NESTED LOOPS (OUTER)
16 MERGE JOIN (OUTER)
17 SORT (JOIN)
18 NESTED LOOPS (OUTER)
19 TABLE ACCESS (FULL) OF 'T_KFQY_FYGL_LP'
19 INDEX (UNIQUE SCAN) OF 'T_BM_FWLX_PK' (UNIQUE)
17 SORT (JOIN)
22 TABLE ACCESS (FULL) OF 'T_KFQY_FYGL_HS'
16 INDEX (UNIQUE SCAN) OF 'PK
_BADW_ZZDM' (UNIQUE) 15 TABLE ACCESS (BY INDEX ROWID
) OF 'T_BM_XFWYT' 25 INDEX (UNIQUE SCAN) OF 'T_
BM_XFWYT_PK' (UNIQUE) 14 INDEX (UNIQUE SCAN) OF 'T_BM_X
FWXZ_PK' (UNIQUE) 13 INDEX (UNIQUE SCAN) OF 'T_BM_FWJ
G_PK' (UNIQUE) 12 TABLE ACCESS (BY INDEX ROWID) OF '
T_BM_FWHX' 29 INDEX (UNIQUE SCAN) OF 'T_BM_FWH
X_PK' (UNIQUE) 11 INDEX (UNIQUE SCAN) OF 'T_BM_FWYT_PK
' (UNIQUE) 10 TABLE ACCESS (BY INDEX ROWID) OF 'T_KF
QY_XSXM' 32 INDEX (UNIQUE SCAN) OF 'T_KFQY_XSXM_
PK' (UNIQUE) 9 TABLE ACCESS (BY INDEX ROWID) OF 'T_KFQY
_JBQK' 34 INDEX (UNIQUE SCAN) OF 'T_KFQY_JBQK_PK
' (UNIQUE) 8 TABLE ACCESS (BY INDEX ROWID) OF 'T_KFXM_X
MGK' 36 INDEX (UNIQUE SCAN) OF 'T_KFXM_XMGK_PK'
(UNIQUE) 6 SORT (JOIN)
38 TABLE ACCESS (FULL) OF 'T_BM_XFWLX'
4 SORT (JOIN)
40 TABLE ACCESS (FULL) OF 'T_HT_PAGE16'
3 TABLE ACCESS (BY INDEX ROWID) OF 'T_HT_PAGE1'
42 INDEX (UNIQUE SCAN) OF 'T_HT_PAGE1_PK' (UNIQUE)
and gdsj<TO_DATE('2005-01-12','YYYY-MM-DD')+1 order by qymc,xmmc
这SQL还可以优化,不知道实际情况是怎样,我就不改了。但你在gdsj这上面建一个索引,速度有很大的提升。
sort_area_size=5242880
# 5M 视情况设置该值,一般情况用5M够了
sort_area_retained_size =5242880
# 5M 一般情况下sort_area_retained_size = sort_area_size
ORA-01652: 无法通过69128(在表空间TEMPORARY_DATA中)扩展 temp 段
ORA-01652: 无法通过69128(在表空间TEMPORARY_DATA中)扩展 temp 段