//现有如下sql,感觉效能很差,而且对于内部逻辑不清楚,
//请各位大虾仅从sql语句效能/异常角度对他分析改善建议
SELECT DISTINCT sc.oem_warranty_status warranty, u.unit_key job_name,
TO_CHAR (toh.complete_time, 'DD-MON-YYYY') release_date,
toh2.op_name
|| DECODE (toh2.complete_reason,
NULL, '',
': ' || toh2.complete_reason
) operation_at_deadline,
TO_CHAR (NVL (toh2.start_time, toh2.complete_time),
'DD-MON-YYYY'
) operation_date
FROM unit u,
work_order wo,
dc_slr_servicecall sc,
tracked_object_status tos,
tracked_object_history toh,
tracked_object_history toh2
WHERE u.order_key = wo.order_key
AND wo.uda_8 = 'M'
AND sc.object_key = wo.order_key
AND u.tobj_status_key = tos.tobj_status_key
AND tos.tobj_key = toh.tobj_key
AND tos.tobj_key = toh2.tobj_key
AND toh.route_step_name IN (110, 90, 120, 130)
AND toh.complete_time >= TO_DATE ('01/01/07', 'mm/dd/yy')
AND toh.complete_time < TO_DATE ('12/31/07', 'mm/dd/yy') + 1
AND toh2.tobj_history_key =
(SELECT MAX (toh3.tobj_history_key)
FROM tracked_object_history toh3
WHERE toh3.tobj_key = u.unit_key
AND NVL (toh3.start_time, toh3.complete_time) <
TRUNC (toh.complete_time)
+ DECODE (TO_CHAR (toh.complete_time, 'Dy'),
'Fri', 3,
'Sat', 3,
'Sun', 2,
1
)
+ 1)
ORDER BY 1, 4, 3
//请各位大虾仅从sql语句效能/异常角度对他分析改善建议
SELECT DISTINCT sc.oem_warranty_status warranty, u.unit_key job_name,
TO_CHAR (toh.complete_time, 'DD-MON-YYYY') release_date,
toh2.op_name
|| DECODE (toh2.complete_reason,
NULL, '',
': ' || toh2.complete_reason
) operation_at_deadline,
TO_CHAR (NVL (toh2.start_time, toh2.complete_time),
'DD-MON-YYYY'
) operation_date
FROM unit u,
work_order wo,
dc_slr_servicecall sc,
tracked_object_status tos,
tracked_object_history toh,
tracked_object_history toh2
WHERE u.order_key = wo.order_key
AND wo.uda_8 = 'M'
AND sc.object_key = wo.order_key
AND u.tobj_status_key = tos.tobj_status_key
AND tos.tobj_key = toh.tobj_key
AND tos.tobj_key = toh2.tobj_key
AND toh.route_step_name IN (110, 90, 120, 130)
AND toh.complete_time >= TO_DATE ('01/01/07', 'mm/dd/yy')
AND toh.complete_time < TO_DATE ('12/31/07', 'mm/dd/yy') + 1
AND toh2.tobj_history_key =
(SELECT MAX (toh3.tobj_history_key)
FROM tracked_object_history toh3
WHERE toh3.tobj_key = u.unit_key
AND NVL (toh3.start_time, toh3.complete_time) <
TRUNC (toh.complete_time)
+ DECODE (TO_CHAR (toh.complete_time, 'Dy'),
'Fri', 3,
'Sat', 3,
'Sun', 2,
1
)
+ 1)
ORDER BY 1, 4, 3
其次,where条件的执行顺序是:从where语句条件的最后一个开始逐渐执行,因此希望楼主把能把范围缩小到最小的条件放在where语句的最后,具体条件,楼主可以根据自己的情况自己写。第三,ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时, 会运用排序及合并的方式连接它们。首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
例如:
表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间0.96秒
选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间26.09秒
我的where条件中有三个表格两两关联,关系如下
u.order_key = wo.order_key
AND sc.object_key = wo.order_key 现在我同事改为如下
u.order_key = wo.order_key
AND sc.object_key = wo.order_key
AND sc.object_key = u.order_key
后,cost会立马降低很多,请问为什么?