为什么oracle的view检索速度那么慢？

[neo_andenson]

只不过从两个表里面检索数据，速度太慢了，总是要半个多小时才能显示
当然，很多列都是用了decode函数的

怎么样才能提高它的检索速度呢？各位帮忙

[neo_andenson]

都下班了吗？除了升级还有别的方法吗？

[marshal2002]

建好索引
优化initsid.ora文件中的相关参数
等等

[mashansj]

优化查询
索引
最好把view的定义贴出来

[neo_andenson]

SELECT MAX(A.EFFECTIVE_MONTH) EFFECTIVE_MONTH,
MAX(A.EMP_ID) EMP_ID,
MAX(A.WAGES) WAGES,
MAX(A.PROMOTION_WAGES) PROMOTION_WAGES,
MAX(ROUND(A.PROMOTION_WAGES/2,2)) PROMOTION_WAGES_NEW,
MAX(ROUND(A.PROMOTION_WAGES/2,2)) JIABAN_WAGES,
MAX(A.GROSS) GROSS,
MAX(A.DEDUCE) DEDUCE,
MAX(A.NET) NET,
MAX(DECODE(B.AD,'D001',AD_AMT,0)) POSITION,
MAX(DECODE(B.AD,'D011',AD_AMT,0)) MANGUN,
MAX(DECODE(B.AD,'D101',AD_AMT,0)) JAGYUK,
MAX(DECODE(B.AD,'D105',AD_AMT,0)) G3TECH,
MAX(DECODE(B.AD,'D107',AD_AMT,0)) G3HOURSE,
MAX(DECODE(B.AD,'D103',AD_AMT,0)) AWARD,
MAX(DECODE(B.AD,'D002',AD_AMT,0)) BOKLI,
MAX(DECODE(B.AD,'D003',AD_AMT,0)) GYEBAL,
MAX(DECODE(B.AD,'D004',AD_AMT,0)) SEASON_SUMMER,
MAX(DECODE(B.AD,'D005',AD_AMT,0)) SEASON_WINTER,
MAX(DECODE(B.AD,'D009',AD_AMT,0))
+ MAX(DECODE(B.AD,'D010',AD_AMT,0)) DANGJIK,
MAX(DECODE(B.AD,'D006',AD_AMT,0)) OT1,
MAX(DECODE(B.AD,'D007',AD_AMT,0)) OT2,
MAX(DECODE(B.AD,'D008',AD_AMT,0)) OT3,
MAX(DECODE(B.AD,'D100',AD_AMT,0)) SOGUB_PLUS,
MAX(DECODE(B.AD,'D020',AD_AMT,0)) YABAN,
MAX(DECODE(B.AD,'C166',AD_AMT,0)) SIKDEA,
MAX(DECODE(B.AD,'C164',AD_AMT,0)) KISUKSA,
MAX(DECODE(B.AD,'C165',AD_AMT,0)) WATER_TAX,
MAX(DECODE(B.AD,'C163',AD_AMT,0)) ELECTRIC_TAX,
MAX(DECODE(B.AD,'C201',AD_AMT,0)) YANGNO_INSURANCE,
MAX(DECODE(B.AD,'C202',AD_AMT,0)) EUELYO_INSURANCE,
MAX(DECODE(B.AD,'C203',AD_AMT,0)) JUBANG_INSURANCE,
MAX(DECODE(B.AD,'C204',AD_AMT,0)) GONGSANG_INSURANCE,
MAX(DECODE(B.AD,'C205',AD_AMT,0)) SILUP_INSURANCE,
MAX(DECODE(B.AD,'C206',AD_AMT,0)) SEANGYUK_INSURANCE,
MAX(DECODE(B.AD,'C201',EMPLOYER_AMT,0)) YANGNO_INSURANCE_C,
MAX(DECODE(B.AD,'C202',EMPLOYER_AMT,0)) EUELYO_INSURANCE_C,
MAX(DECODE(B.AD,'C203',EMPLOYER_AMT,0)) JUBANG_INSURANCE_C,
MAX(DECODE(B.AD,'C204',EMPLOYER_AMT,0)) GONGSANG_INSURANCE_C,
MAX(DECODE(B.AD,'C205',EMPLOYER_AMT,0)) SILUP_INSURANCE_C,
MAX(DECODE(B.AD,'C206',EMPLOYER_AMT,0)) SEANGYUK_INSURANCE_C,
MAX(DECODE(B.AD,'C207',AD_AMT,0)) SODUK_TAX,
MAX(DECODE(B.AD,'C100',AD_AMT,0)) SOGUB_MINUS,
MAX(A.DEPT) DEPT,
MAX(A.COSTCT) COSTCT,
MAX(A.JG) JG,
MAX(A.JL) JL,
MAX(A.NAME) NAME,
MAX(A.NET_BASIC) NET_BASIC,
MAX(A.NET_PI) NET_PI,
MAX(A.GITA_SUDANG1) GITA_SUDANG1,
MAX(DECODE(B.AD,'D040',AD_AMT,0)) OVER_PAY,
MAX(DECODE(B.AD,'D051',AD_AMT,0)) ADD_PAY,
MAX(DECODE(B.AD,'D109',AD_AMT,0)) ALLOWANCE_OTHER,
MAX(DECODE(B.AD,'C133',AD_AMT,0)) MULCT,
MAX(DECODE(B.AD,'D110',AD_AMT,0)) GONGLING
FROM PAYHIS1T A, PAYHIS2T B
WHERE A.EMP_ID = B.EMP_ID (+)
AND A.EFFECTIVE_MONTH = B.EFFECTIVE_MONTH (+)
GROUP BY A.EFFECTIVE_MONTH,
A.EMP_ID

[neo_andenson]

initsid.ora文件中的相关参数？
initsid.ora？在哪里可以找到

索引我优化过了，现在我对这个decode函数简直是又爱又恨

[marshal2002]

在oracle安装目录如：c:\orant\database\下
另：initsid.ora文件名中的sid是实例名如实例名为orcl那么文件名为initorcl.ora

上面说的是oracle806以下版本

[neo_andenson]

是啊，我们这用的oracle7.2
我怎么才能看到服务器oracle安装目录？

[marshal2002]

在dos状态运行 set path
在里面应该可以看到oracle相关的路径
或者在注册表
software/oracle下有oracle_home的值就是

[teleid]

换个法，这种东西不慢才怪！

[killerdanny]

MAX不检查索引的！建多少也每用！