oracle union all 索引,oracle in union all 的性能分析！

大家来看看这条sql语句，oracle的版本是9i的，我想问下，不改变语句的情况下，如何优化它的速度呢，这条语句执行起来，需要近20秒，晕倒，数据量也才几万条而已啊，

因为这条语句用到了 in  union all，所以速度奇慢！如何优化速度呢，

SELECT 'wt.workflow.work.WorkItem',A0.completedBy,A0.description,A0.classnamekeyA2ownership,

A0.idA3A2ownership,A0.classnamekeyC4,A0.idA3C4,A0.classnamekeyB4,A0.priority,

A0.reassigned,A0.required,A0.role,A0.classnamekeyA4,A0.idA3A4,A0.status,

A0.taskURLPathInfo,TO_CHAR(A0.createStampA2,'dd mm yyyy hh24:mi:ss'),

A0.markForDeleteA2,TO_CHAR(A0.modifyStampA2,'dd mm yyyy hh24:mi:ss'),

A0.idA2A2,A0.updateCountA2,TO_CHAR(A0.updateStampA2,'dd mm yyyy hh24:mi:ss'),

A0.variablePermissionMap FROM WorkItem A0

WHERE

(

(A0.completedBy IS NOT NULL )

AND

(A0.idA3A4 IN (

SELECT A0.idA2A2 FROM WfAssignedActivity A0 WHERE ((

A0.idA3parentProcessRef = 1422937))

UNION ALL

SELECT A0.idA2A2 FROM ProjectActivity

A0 WHERE ((A0.idA3parentProcessRef = 1422937))

)))

执行计划如下：

执行步骤:

步骤号 步骤名

10  SELECT STATEMENT

9  NESTED LOOPS

1  PDS.WORKITEM TABLE ACCESS [FULL]

8  SYS.VW_NSO_1 VIEW

7  SORT [UNIQUE]

6  UNION-ALL [PARTITION]

3  PDS.WFASSIGNEDACTIVITY TABLE ACCESS [BY INDEX ROWID]

2  PDS.PK_WFASSIGNEDACTIVITY INDEX [UNIQUE SCAN]

5  PDS.PROJECTACTIVITY TABLE ACCESS [BY INDEX ROWID]

4  PDS.PK_PROJECTACTIVITY INDEX [UNIQUE SCAN]

步骤号 说明 估计成本 估计的返回行数 估计的返回字节数 (KB)

1    此计划步骤检索表 WORKITEM 中的所有行。 280 33,149 19,552.73

2    此计划步骤检索 B*-tree 索引 PK_WFASSIGNEDACTIVITY 中的单个 ROWID。 1 27,010 --

3    此计划步骤通过索引返回的 ROWID 检索表 WFASSIGNEDACTIVITY 中的行。 2 1 0.012

4    此计划步骤检索 B*-tree 索引 PK_PROJECTACTIVITY 中的单个 ROWID。 1 1,958 --

5    此计划步骤通过索引返回的 ROWID 检索表 PROJECTACTIVITY 中的行。 2 1 0.012

6    此计划步骤对一组分区执行一般性合并。

7    此计划步骤接受一个行集合 (其唯一的子级行) 并对其排序以便找出并删除副本。 -- -- --

8    此计划步骤代表由视图 VW_NSO_1 定义的子查询的执行计划。 93 1 0.006

9    此计划步骤通过对驱动即外部行集 (联接的第一个子级) 进行迭代, 并且对每行执行内部行集 (联接的第二个子级) 的步骤来联接两组行。根据查询的 WHERE 子句所指定的联接条件测试相应行对。 280 36,742 21,887.324

10    此计划步骤将该语句指定为 SELECT 语句。 280 36,742 21,887.324

计划截图：

我现在的做法是：

把WfAssignedActivity 和ProjectActivity

的 idA3parentProcessRef 索引drop掉，

速度有所提升，提升了一般的速度，现在执行差不多4秒中

这是为什么呢？

==========继续测试 2010-07-06 15：30

如果把sql语句改成以下方式：

SELECT 'wt.workflow.work.WorkItem',A0.completedBy,A0.description,A0.classnamekeyA2ownership,

A0.idA3A2ownership,A0.classnamekeyC4,A0.idA3C4,A0.classnamekeyB4,A0.priority,

A0.reassigned,A0.required,A0.role,A0.classnamekeyA4,A0.idA3A4,A0.status,

A0.taskURLPathInfo,TO_CHAR(A0.createStampA2,'dd mm yyyy hh24:mi:ss'),

A0.markForDeleteA2,TO_CHAR(A0.modifyStampA2,'dd mm yyyy hh24:mi:ss'),

A0.idA2A2,A0.updateCountA2,TO_CHAR(A0.updateStampA2,'dd mm yyyy hh24:mi:ss'),

A0.variablePermissionMap FROM WorkItem A0 WHERE ((A0.completedBy IS NOT NULL )

AND (A0.idA3A4  IN (

SELECT A0.idA2A2 - 0FROM WfAssignedActivity A0 WHERE ((

A0.idA3parentProcessRef = 1422937)) UNION ALL

SELECT A0.idA2A2 - 0 FROM ProjectActivity

A0 WHERE ((A0.idA3parentProcessRef = 1422937))

)))

那么速度又变成了毫秒级别

检查一下执行计划，发现有些改变，以下解释计划信息：

步骤号 步骤名

10  SELECT STATEMENT

9  HASH JOIN

7  SYS.VW_NSO_1 VIEW

6  SORT [UNIQUE]

5  UNION-ALL

2  PDS.WFASSIGNEDACTIVITY TABLE ACCESS [BY INDEX ROWID]

1  PDS.WFASSIGNEDACTIVITY$COMPOSITE INDEX [RANGE SCAN]

4  PDS.PROJECTACTIVITY TABLE ACCESS [BY INDEX ROWID]

3  PDS.PROJECTACTIVITY$COMPOSITE3 INDEX [RANGE SCAN]

8  PDS.WORKITEM TABLE ACCESS [FULL]

步骤号 说明 估计成本 估计的返回行数 估计的返回字节数 (KB)

1    此计划步骤通过扫描 B*-tree 索引 WFASSIGNEDACTIVITY$COMPOSITE 来检索一个或多个 ROWID (按升序排列)。 1 4 --

2    此计划步骤通过索引返回的 ROWID 检索表 WFASSIGNEDACTIVITY 中的行。 2 4 0.047

3    此计划步骤通过扫描 B*-tree 索引 PROJECTACTIVITY$COMPOSITE3 来检索一个或多个 ROWID (按升序排列)。 2 5 --

4    此计划步骤通过索引返回的 ROWID 检索表 PROJECTACTIVITY 中的行。 4 5 0.059

5    此计划步骤接受多组行, 并将它们 (包括所有副本) 组合为一组。

6    此计划步骤接受一个行集合 (其唯一的子级行) 并对其排序以便找出并删除副本。 6 9 0.105

7    此计划步骤代表由视图 VW_NSO_1 定义的子查询的执行计划。 6 9 0.114

8    此计划步骤检索表 WORKITEM 中的所有行。 280 33,149 19,552.73

9    此计划步骤接受两组行, 分别来自不同的表。已经使用第一个子级所返回的行构建散列表。第二个子级返回的每行接着用于探测散列表以查找满足查询的 WHERE 子句中指定条件的行对。注: Oracle 基于成本的优化程序将使用两个表中它认为较小的表来构建散列表。优化程序利用统计信息来确定较小的表。因此, 过期的统计信息可能会导致优化程序做出错误的选择。 295 11 6.628

10    此计划步骤将该语句指定为 SELECT 语句。 295 11 6.628

用HASH JOIN，速度就变快了，

谁能说明一下原因呢

[本帖最后由 xxciof 于 2010-7-6 15:31 编辑]