下面的sql在数据较大的时候效率较低,请问各位高手如何优化?
不胜感激UPDATE T270839 target
SET target.inconsistency = (
SELECT CASE
WHEN count(*) < 2 THEN 0
WHEN STDDEV(refset.value) > 0 THEN (target.value - AVG(refset.value))/STDDEV(refset.value)
ELSE 0
END
FROM T270839 refset
WHERE LEVEL <= 2 AND row_id IS NULL
START WITH refset.node_id = target.node_id
CONNECT BY refset.parent_node_id = PRIOR refset.node_id)
下面是相关的信息可供参考:
The execution plan shows that for each row in "target", T270839 table is full scanned 3 times.
Consider using merge statement without insert:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:556373000346392260
or "create table as select " which removes the update and does not produce redo.
Also,
- connect by could be replaced by self join
- aggregate functions could be replaced with analytics: count(*) -> count(*) over (partition by parent.parent_node_id)
不胜感激UPDATE T270839 target
SET target.inconsistency = (
SELECT CASE
WHEN count(*) < 2 THEN 0
WHEN STDDEV(refset.value) > 0 THEN (target.value - AVG(refset.value))/STDDEV(refset.value)
ELSE 0
END
FROM T270839 refset
WHERE LEVEL <= 2 AND row_id IS NULL
START WITH refset.node_id = target.node_id
CONNECT BY refset.parent_node_id = PRIOR refset.node_id)
下面是相关的信息可供参考:
The execution plan shows that for each row in "target", T270839 table is full scanned 3 times.
Consider using merge statement without insert:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:556373000346392260
or "create table as select " which removes the update and does not produce redo.
Also,
- connect by could be replaced by self join
- aggregate functions could be replaced with analytics: count(*) -> count(*) over (partition by parent.parent_node_id)
解决方案 »
- oracle 行转列问题
- oracle ora-12514 监听程序无法识别连接描述中请求的服务
- oracle 事务
- 求一SQL
- Oralce在引用java代码时出现symbol : variable Base64 问题
- 请高手帮忙纠错
- 菜鸟提问:简单sql语句问题!
- dev9i中如何调整显示在IE中的form窗口大小?
- Oracle 8i建数据库是不是要很长很长时间...........
- 我用的Developer/2000开发,怎么能做到隔5分钟自动select数据一次?(急!在线等!)
- 论坛的各位兄弟姐妹们,你们知道如何把sybase数据库通过OCI移植到oracle数据库中吗?
- 关于分区表的几个问题
1、用你的查询语句整个表被遍历了三遍,
2、可以用存储过程实现。
3、可以用join连接提高效率
4、不要使用count(*),使用count(1),或者count(columnname)
而count(columnname)可能会比count(*)慢
2: CONNECT BY 问题.
1.在有聚合函数的sql里如何用join来连接?
2.count(*) 跟count(1)和 count(columnName)那个效率更高些?