老难的了,我是搞不定,你进来看看?
以下语句执行时长在1秒以内
select T1.STAFF_ID STAFF_ID,
T2.STAFF_NO STAFF_NO,
T2.STAFF_NAME STAFF_NAME,
T4.DEPT_ID DEPT_ID,
T4.DEPT_NAME DEPT_NAME,
T1.CALLER CALLER,
T1.CALLEE CALLEE,
decode(sign(T1.CALL_TIME - to_date( '20050101 ', 'yyyymmdd ')),
1,
to_char(T1.CALL_TIME, 'yyyy/mm/dd hh24:mi:ss '),
'-- ') CALL_TIME,
decode(sign(T1.ANS_TIME - to_date( '20050101 ', 'yyyymmdd ')),
1,
to_char(T1.ANS_TIME, 'yyyy/mm/dd hh24:mi:ss '),
'-- ') ANS_TIME,
decode(sign(T1.CLR_TIME - to_date( '20050101 ', 'yyyymmdd ')),
1,
to_char(T1.CLR_TIME, 'yyyy/mm/dd hh24:mi:ss '),
'-- ') CLR_TIME,
decode(sign(T1.ANS_TIME - to_date( '20050101 ', 'yyyymmdd ')),
1,
trunc((T1.CLR_TIME - T1.ANS_TIME) * 86400),
0) DURATION,
T3.CALL_END_REASON_ID CALL_END_REASON_ID,
T3.CALL_END_REASON CALL_END_REASON
from (select ASSOCIATE_MERGE_ID,
CALLER_USER_TYPE,
CALL_ID,
CALL_SEQ,
NODE_ID,
TERM_ID,
TERM_TYPE,
STAFF_ID,
CALL_TIME,
DIRECTION,
JOIN_MODE,
PRELINK_TIME,
RELEASE_TIME,
IAI_TIME,
ACM_TIME,
ANS_TIME,
CLR_TIME,
PRIMARY_CALLER,
PRIMARY_CALLEE,
CALLER,
CALLEE,
FINISH_REASON,
CALL_TYPE,
QUEUE_START_TIME,
QUEUE_FINISH_TIME,
ACD_NO
from DM_TERM_CALL_LOG_09
where DIRECTION = 1
and STAFF_ID in
( '110 ', '1145 ', '1151 ', '206 ', '2073 ', '2113 ', '22 ',
'252 ', '2735 ', '317 ', '321 ', '3433 ', '3434 ', '3435 ',
'421 ', '4393 ', '48 ', '51 ', '60 ')
and CALL_TIME > =
to_date( '2007/9/10 10:44:53 ', 'yyyy/mm/dd HH24:MI:SS ')
and CALL_TIME <=
to_date( '2007/9/13 10:44:54 ', 'yyyy/mm/dd HH24:MI:SS ')) T1,
ECC_STAFF_MANAGER T2,
DM_CALL_END_REASON T3,
ECC_DEPT_MANAGER T4
where T1.STAFF_ID = T2.STAFF_ID
and T2.DEPT_ID = T4.DEPT_ID
and T1.FINISH_REASON = T3.CALL_END_REASON_ID
但如果在语句的外层加上一个count(*),执行效率就狂低,执行时间在10秒以上
select count(*)
from (select T1.STAFF_ID STAFF_ID,
T2.STAFF_NO STAFF_NO,
T2.STAFF_NAME STAFF_NAME,
T4.DEPT_ID DEPT_ID,
T4.DEPT_NAME DEPT_NAME,
T1.CALLER CALLER,
T1.CALLEE CALLEE,
decode(sign(T1.CALL_TIME - to_date( '20050101 ', 'yyyymmdd ')),
1,
to_char(T1.CALL_TIME, 'yyyy/mm/dd hh24:mi:ss '),
'-- ') CALL_TIME,
decode(sign(T1.ANS_TIME - to_date( '20050101 ', 'yyyymmdd ')),
1,
to_char(T1.ANS_TIME, 'yyyy/mm/dd hh24:mi:ss '),
'-- ') ANS_TIME,
decode(sign(T1.CLR_TIME - to_date( '20050101 ', 'yyyymmdd ')),
1,
to_char(T1.CLR_TIME, 'yyyy/mm/dd hh24:mi:ss '),
'-- ') CLR_TIME,
decode(sign(T1.ANS_TIME - to_date( '20050101 ', 'yyyymmdd ')),
1,
trunc((T1.CLR_TIME - T1.ANS_TIME) * 86400),
0) DURATION,
T3.CALL_END_REASON_ID CALL_END_REASON_ID,
T3.CALL_END_REASON CALL_END_REASON
from (select ASSOCIATE_MERGE_ID,
CALLER_USER_TYPE,
CALL_ID,
CALL_SEQ,
NODE_ID,
TERM_ID,
TERM_TYPE,
STAFF_ID,
CALL_TIME,
DIRECTION,
JOIN_MODE,
PRELINK_TIME,
RELEASE_TIME,
IAI_TIME,
ACM_TIME,
ANS_TIME,
CLR_TIME,
PRIMARY_CALLER,
PRIMARY_CALLEE,
CALLER,
CALLEE,
FINISH_REASON,
CALL_TYPE,
QUEUE_START_TIME,
QUEUE_FINISH_TIME,
ACD_NO
from DM_TERM_CALL_LOG_09
where DIRECTION = 1
and STAFF_ID in
( '110 ', '1145 ', '1151 ', '206 ', '2073 ', '2113 ', '22 ',
'252 ', '2735 ', '317 ', '321 ', '3433 ', '3434 ', '3435 ',
'421 ', '4393 ', '48 ', '51 ', '60 ')
and CALL_TIME > =
to_date( '2007/9/10 10:44:53 ', 'yyyy/mm/dd HH24:MI:SS ')
and CALL_TIME <=
to_date( '2007/9/13 10:44:54 ', 'yyyy/mm/dd HH24:MI:SS ')) T1,
ECC_STAFF_MANAGER T2,
DM_CALL_END_REASON T3,
ECC_DEPT_MANAGER T4
where T1.STAFF_ID = T2.STAFF_ID
and T2.DEPT_ID = T4.DEPT_ID
and T1.FINISH_REASON = T3.CALL_END_REASON_ID)
大家有遇到这种情况吗?有什么处理方式呀?
[解决办法]
你怎么确定执行时间是1秒？第一句查询返回多少记录？
[解决办法]
在PL/SQL的环境下执行第一条语句,开始只是显示一屏的数据,可能会很快,显示的时间也会很短,要把数据都显示完,看哈需要多久的时间,再和第二条比较看
[解决办法]
count(*)本来就会慢些
