blaze

1 同样的sql在不同的服务器上效果不同,都是oracle9i,一个正常,一个没有反应,但在sqlplus中都可以执行.

2 发现不正常的服务器上,临时表空间剧增,而且不能释放,时间长了导致无法从套接字中获取数据的异常

3 经过尝试,是由于distinct造成的

sql: select distinct a.a1,a.a2,a.a3....... from t a,如果distinct中的字段很多,而且存在子查询时,就会发生问题,如:

SELECT ts.ID AS id,
ts.SEND_ROOM_ID AS sendRoomId,
ts.ACCEPT_UNIT_ID AS acceptUnitId,
ts.AP_ID AS apId,
ts.SHOULD_ACCEPT_AMOUNT AS shouldAcceptAmount,
ts.REAL_ACCEPT_AMOUNT AS realAcceptAmount,
ts.MEMO AS memo,
ts.MAKE_BILL_PERSON AS makeBillPerson,
ts.MAKE_BILL_PERSON_ID AS makeBillPersonId,
ts.MAKE_BILL_DATE AS makeBillDate,
ts.SEND_PERSON AS sendPerson,
ts.SEND_PERSON_ID AS sendPersonId,
ts.SEND_DATE AS sendDate,
ts.ACCEPT_PERSON AS acceptPerson,
ts.ACCEPT_PERSON_ID AS acceptPersonId,
ts.ACCEPT_DATE AS acceptDate,
ts.AUDIT_PERSON AS auditPerson,
ts.AUDIT_PERSON_ID AS auditPersonId,
ts.AUDIT_DATE AS auditDate,
ts.ISRETURN AS isreturn,
ts.ISDEL AS isdel,
ts.ISACCOUNT AS isaccount,
ts.LEAD_BILL_ID AS leadBillId,
ts.SEND_ROOM_NAME AS sendRoomName,
ts.BILL_NO AS billNo,
ts.CO_BILL_NO AS coBillNo,
ts.ACCEPT_UNIT_NAME AS acceptUnitName,
ts.MAT_NO AS matNo,
ts.MAT_NAME AS matName,
ts.SPECIFICATION AS specification,
ts.MAT_UNIT AS matUnit,
ts.PLAN_ID AS planId,
ts.DELEGATE_FEE AS delegateFee,
ts.OTHER_FEE,
ts.LEAD_BILL_NO AS leadBillNo,
ts.AUDIT_NOTION AS auditNotion,
ts.STEP AS step,
a.id as accountId,
a.ap_id as accountNo,
'0' as leadBillType,
(select room.ROOM_NAME
from T_STORAGE_ROOM_INFO room
where room.id = ts.send_room_id) as roomName,
(select t.mainproject
from t_project_info t
where t.project_no = p.project_no) as projectName,
(select t.dispname
from orgnode t
where t.id = (select t.parentid
from orgnode t
where t.id = p.req_depart_id)) as deport
FROM T_STORAGE_SEND_BILL ts, T_STORAGE_ACCOUNT_BASE a, t_plan_base_info p

WHERE ts.ISRETURN = '0'
AND ts.ISDEL = '0'
AND ts.ISACCOUNT = '0'
AND ts.step = 1
and ts.plan_id = p.id
and ts.BILL_TYPE = '0'
and a.ap_id = ts.ap_id