关于SQL优化的问题

下面的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:
[url=http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::11_QUESTION_ID:556373000346392260]http://asktom.oracle.com/pls/ask ... :556373000346392260[/url]
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)

根据上面的信息，有些疑惑：
1.在有聚合函数的sql里如何用join来连接？
2.count(*) 跟count(1)和 count(columnName)那个效率更高些？