最近遇到一個奇怪的問題 一條sql陳述句導致 oracle 臨時表空間不斷增長
sql 如下
select ui.*, ud.depId as departmentDn, di.name department
from user_info ui,
user_department ud,
org_department od,
department_info di
where ui.userid = ud.userId
and di.id = ud.depId
and ud.depId = od.depId
and od.dimension = '2d8794ab77cb4e5a9bbe807da6ce90b2';
洗掉最后一個where條件就不會出現
select ui.*, ud.depId as departmentDn, di.name department
from user_info ui,
user_department ud,
org_department od,
department_info di
where ui.userid = ud.userId
and di.id = ud.depId
and ud.depId = od.depId
感覺可能和資料庫的配置有關，能否幫忙提供一下定位問題的思路
uj5u.com熱心網友回復：
執行計劃看看uj5u.com熱心網友回復：
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 495 | 8 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 495 | 8 (0)| 00:00:01 |
| 2 | MEGGE JOIN CARTESIAN | | 6 | 2190 | 6 (0)| 00:00:01 |
| 3 | MEGGE JOIN CARTESIAN| | 2 | 580 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | ORG_DEPARTMENT | 1 | 130 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 4 | 640 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | USER_INFO | 4 | 640 | 2 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 3 | 225 | 4 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | DEPARTMENT_INFO | 3 | 225 | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | USER_DEPARTMENT | 5 | 650 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("UI"."USERID"="UD"."USERID" AND "DI"."ID"="UD"."DEPID" AND
"UD"."DEPID"="OD"."DEPID")
4 - filter("OD"."DIMENSION"=U'2d8794ab77cb4e5a9bbe807da6ce90b2'
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/203798.html
標籤：開發