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秒多一点.
这个查询的时间正常吗?能更快一些吗?
我试了一下,字段越多时间越多.
请大家帮忙指点一下哦!
解决方案 »
- 字段为空的时候才更新,怎么写?
- 监听程序当前无法识别连接描述符中所给出的sid,services进程只有一个
- oracle中,数据库实例,数据库,用户,三者之间的相互关系是什么???
- Oracle中varchar2和varchar有什么区别,使用起来一样吗?
- 从access导入到oracle的表,改了字段名再保存后就出现了两个相同名称的表?
- dbms_metadata.get_ddl报错误
- 关于游标
- 一个简单问题:如何设置时间的系统默认值为:yyyy-mm-dd hh:mm:ss这样的格式?
- oracle装好了后,net8和oracle database assistant都打不开,该怎么办?
- ****************超级难题*****************
- 我要用这个函数,UTL_FILE.PUT_LINE(); 来向文件里写数据,那预先在sql*plus里面打什么命令设置一下,才行呢??
- 一个连接oracle 中文乱码的问题
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'
否则索引不会起作用的