我使用IBM的COTS系统，名为Maximo Asset Management。系统有一个350,000行的WORKORDER表。
Maximo有一个名为关系的概念，可用于从相关记录中提取数据。
关系如何运作:
对于每个WORKORDER记录，系统使用关系中的WHERE子句运行一个选择查询来拉入相关记录(截图)。
相关记录:
在本例中，相关记录是名为WOTASK的自定义数据库视图中的行。ROLLUP_VW .
在一篇相关的文章中，我探讨了我可以在视图中使用的不同的SQL rollup技术:按x分组，也可以获取其他字段。当我在完整的WORKORDER表上运行这些选项时，我所研究的这些选项的执行效果是相似的。
然而，在现实中，Maximo被设计为一次只能获得一行——通过单独的select语句。因此，查询的执行非常不同
我将每个查询包装在一个带有WHERE子句的外部查询中，该子句选择特定的工作顺序。我这样做是为了模仿Maximo使用关系时的做法。
查询1 b:(集团;选择性聚合)
性能非常好，即使只选择一条记录，因为使用了索引(仅37毫秒)。
select
*
from
(
select
wogroup as wonum,
sum(actlabcost) as actlabcost_tasks_incl,
sum(actmatcost) as actmatcost_tasks_incl,
sum(acttoolcost) as acttoolcost_tasks_incl,
sum(actservcost) as actservcost_tasks_incl,
sum(actlabcost + actmatcost + acttoolcost + actservcost) as acttotalcost_tasks_incl,
max(case when istask = 0 then rowstamp end) as other_wo_columns
from
maximo.workorder
group by
wogroup
)
where
wonum in ('WO360996')
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 4 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 1 | 34 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| WORKORDER | 1 | 34 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | WORKORDER_NDX32 | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("WOGROUP"='WO360996')
查询# 2:(SUM窗口函数)
性能相对慢，因为没有使用索引(3秒)。
select
*
from
(
select
wonum,
actlabcost_tasks_incl,
actmatcost_tasks_incl,
acttoolcost_tasks_incl,
actservcost_tasks_incl,
acttotalcost_tasks_incl,
other_wo_columns
from
(
select
wonum,
istask,
sum(actlabcost ) over (partition by wogroup) as actlabcost_tasks_incl,
sum(actmatcost ) over (partition by wogroup) as actmatcost_tasks_incl,
sum(acttoolcost) over (partition by wogroup) as acttoolcost_tasks_incl,
sum(actservcost) over (partition by wogroup) as actservcost_tasks_incl,
sum(actlabcost + actmatcost + acttoolcost + actservcost) over (partition by wogroup) as acttotalcost_tasks_incl,
rowstamp as other_wo_columns
from
maximo.workorder
)
where
istask = 0
)
where
wonum in ('WO360996')
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 355K| 61M| | 14789 (1)| 00:00:01 |
|* 1 | VIEW | | 355K| 61M| | 14789 (1)| 00:00:01 |
| 2 | WINDOW SORT | | 355K| 14M| 21M| 14789 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| WORKORDER | 355K| 14M| | 10863 (2)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("WONUM"='WO360996' AND "ISTASK"=0)
问题:
为什么#1B中的GROUP BY查询可以使用索引(快)，而#2中的Sum Window Function不能使用索引(慢)?
您的两个查询是不同的，因为您使用的第一个:
select wogroup as wonum,
第二个你只用:
select wonum,
这意味着您不会在WOGROUP上使用索引，因为您正在过滤WONUM列而不是WOGROUP列(恰好已别名为WONUM)。
看起来你的第二个查询可以被纠正并减少(通过将过滤器移动到内部子查询并删除分区，因为你已经过滤了)到:
select wonum,
actlabcost_tasks_incl,
actmatcost_tasks_incl,
acttoolcost_tasks_incl,
actservcost_tasks_incl,
acttotalcost_tasks_incl,
other_wo_columns
from (
select wogroup AS wonum,
istask,
sum(actlabcost ) over () as actlabcost_tasks_incl,
sum(actmatcost ) over () as actmatcost_tasks_incl,
sum(acttoolcost) over () as acttoolcost_tasks_incl,
sum(actservcost) over () as actservcost_tasks_incl,
sum(actlabcost + actmatcost + acttoolcost + actservcost) over () as acttotalcost_tasks_incl,
rowstamp as other_wo_columns
from maximo.workorder
where wogroup = 'WO360996'
)
where istask = 0;