SQL语句如下:
SELECT A.COMPCD,
A.EMP_TYPE,
A.EMPNO,
packeg1.emp_name(A.COMPCD, 'NAT',A.EMPNO) EMPNM,
A.DEPTCD,
packeg1.dept_short_name(A.COMPCD,'NAT',A.DEPTCD) DEPTNM,
A.TYPECD,
packeg1.base_code_name(A.COMPCD,'NAT','PR008',A.TYPECD) TYPENM,
A.OCCUCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR007',A.OCCUCD) OCCUNM,
A.DUTYCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR006',A.DUTYCD) DUTYNM,
A.LEVLCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR004',A.LEVLCD) LEVLNM,
A.EMSCHOOL,
packeg1.base_code_name(A.COMPCD,'NAT','PR009',A.EMSCHOOL) EMSCHOOLNM,
A.EMRESNO,
A.EMSTATUS,
A.EMENTDT,
A.EMRETDT,
A.EMSEX,
B.EPWAGETP
FROM HRMASTER A,
PRMASTER B
WHERE ( A.COMPCD = B.COMPCD )
AND ( A.EMPNO = B.EMPNO )
AND ( A.COMPCD = '010' )
AND ( A.EMSTATUS <> '4' )
AND ( NVL(A.DEPTCD,'%') LIKE '%')
AND ( B.EPWAGETP IN (SELECT WAGECD
FROM PRUSERS
WHERE COMPCD = '010'
AND userid = 'FREEVIEW'
UNION
SELECT WAGECD
FROM PRUSERS_ADD
WHERE COMPCD = '010'
AND userid = 'FREEVIEW') )执行该语句后返回记录数据为4000条,时间5秒多一点.
这个查询的时间正常吗?能更快一些吗?
我试了一下,字段越多时间越多.
请大家帮忙指点一下哦!
SELECT A.COMPCD,
A.EMP_TYPE,
A.EMPNO,
packeg1.emp_name(A.COMPCD, 'NAT',A.EMPNO) EMPNM,
A.DEPTCD,
packeg1.dept_short_name(A.COMPCD,'NAT',A.DEPTCD) DEPTNM,
A.TYPECD,
packeg1.base_code_name(A.COMPCD,'NAT','PR008',A.TYPECD) TYPENM,
A.OCCUCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR007',A.OCCUCD) OCCUNM,
A.DUTYCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR006',A.DUTYCD) DUTYNM,
A.LEVLCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR004',A.LEVLCD) LEVLNM,
A.EMSCHOOL,
packeg1.base_code_name(A.COMPCD,'NAT','PR009',A.EMSCHOOL) EMSCHOOLNM,
A.EMRESNO,
A.EMSTATUS,
A.EMENTDT,
A.EMRETDT,
A.EMSEX,
B.EPWAGETP
FROM HRMASTER A,
PRMASTER B
WHERE ( A.COMPCD = B.COMPCD )
AND ( A.EMPNO = B.EMPNO )
AND ( A.COMPCD = '010' )
AND ( A.EMSTATUS <> '4' )
AND ( NVL(A.DEPTCD,'%') LIKE '%')
AND ( B.EPWAGETP IN (SELECT WAGECD
FROM PRUSERS
WHERE COMPCD = '010'
AND userid = 'FREEVIEW'
UNION
SELECT WAGECD
FROM PRUSERS_ADD
WHERE COMPCD = '010'
AND userid = 'FREEVIEW') )执行该语句后返回记录数据为4000条,时间5秒多一点.
这个查询的时间正常吗?能更快一些吗?
我试了一下,字段越多时间越多.
请大家帮忙指点一下哦!
2)尽量使用exists来替换in
试了你说的方法,时间上基本上没有改变.
另表中Compcd,Empno两字段均设成索引了.
AND ( NVL(A.DEPTCD,'%') LIKE '%') --这一句一点作用都没有
AND ( B.EPWAGETP IN (SELECT WAGECD
FROM PRUSERS
WHERE COMPCD = '010'
AND userid = 'FREEVIEW'
UNION
SELECT WAGECD
FROM PRUSERS_ADD
WHERE COMPCD = '010'
AND userid = 'FREEVIEW') ) ;
建议这样:
AND ( exists (SELECT 1
FROM PRUSERS s
WHERE COMPCD = '010'
AND userid = 'FREEVIEW'
and s.WAGECD = B.EPWAGETP)
OR exists (SELECT 1
FROM PRUSERS_ADD s
WHERE COMPCD = '010'
AND userid = 'FREEVIEW'
and s.WAGECD = B.EPWAGETP) ) ;
SQL语句如下:
SELECT A.COMPCD,
A.EMP_TYPE,
A.EMPNO,
packeg1.emp_name(A.COMPCD, 'NAT',A.EMPNO) EMPNM,
A.DEPTCD,
packeg1.dept_short_name(A.COMPCD,'NAT',A.DEPTCD) DEPTNM,
A.TYPECD,
packeg1.base_code_name(A.COMPCD,'NAT','PR008',A.TYPECD) TYPENM,
A.OCCUCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR007',A.OCCUCD) OCCUNM,
A.DUTYCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR006',A.DUTYCD) DUTYNM,
A.LEVLCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR004',A.LEVLCD) LEVLNM,
A.EMSCHOOL,
packeg1.base_code_name(A.COMPCD,'NAT','PR009',A.EMSCHOOL) EMSCHOOLNM,
A.EMRESNO,
A.EMSTATUS,
A.EMENTDT,
A.EMRETDT,
A.EMSEX,
B.EPWAGETP
FROM HRMASTER A,
PRMASTER B
WHERE ( A.COMPCD = B.COMPCD )
AND ( A.EMPNO = B.EMPNO )
AND ( A.COMPCD = '010' )
AND ( A.EMSTATUS <> '4' )
这样的话时间还是5秒多一点,是不是就是最短的检索时间了.
2)相关索引;
3)package1里面的SQL优化或去除package1测试
试一下单表查询加子查询
SELECT A.COMPCD,
A.EMP_TYPE,
A.EMPNO,
packeg1.emp_name(A.COMPCD, 'NAT',A.EMPNO) EMPNM,
A.DEPTCD,
packeg1.dept_short_name(A.COMPCD,'NAT',A.DEPTCD) DEPTNM,
A.TYPECD,
packeg1.base_code_name(A.COMPCD,'NAT','PR008',A.TYPECD) TYPENM,
A.OCCUCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR007',A.OCCUCD) OCCUNM,
A.DUTYCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR006',A.DUTYCD) DUTYNM,
A.LEVLCD,
packeg1.base_code_name(A.COMPCD,'NAT','PR004',A.LEVLCD) LEVLNM,
A.EMSCHOOL,
packeg1.base_code_name(A.COMPCD,'NAT','PR009',A.EMSCHOOL) EMSCHOOLNM,
A.EMRESNO,
A.EMSTATUS,
A.EMENTDT,
A.EMRETDT,
A.EMSEX,
(select B.EPWAGETP from PRMASTER B where ( A.COMPCD = B.COMPCD ) AND ( A.EMPNO = B.EMPNO )) EPWAGETP
FROM HRMASTER A
WHERE ( A.COMPCD = '010' )
AND ( A.EMSTATUS <> '4' )
如果建索引了不要这样写,改成A.EMSTATUS > '4' or A.EMSTATUS < '4'
否则索引不会起作用的