我使用 IBM 的一個名為Maximo Asset Management的 COTS 系統。系統有一個包含 350,000 行的 WORKORDER 表。
Maximo 有一個稱為關系的概念，可用于從相關記錄中提取資料。
關系如何運作：
對于每個單獨的WORKORDER 記錄，系統使用關系中的 WHERE 子句運行選擇查詢以拉入相關記錄。截屏
相關記錄：
在這種情況下，相關記錄是名為 WO TASK ROLLUP_VW的自定義資料庫視圖中的行。
在一篇相關的文章中，我探討了可以在視圖中使用的不同 SQL 匯總技術：Group by x, get other fields too。
當我在完整的 WORKORDER 表上運行它們時，我探索的選項彼此相似。
然而，實際上，Maximo 被設計為一次只獲取一行——通過單獨的 select 陳述句。因此，當僅選擇單個 WORKORDER 記錄時，查詢的執行方式非常不同。
我已經使用選擇特定 WONUM 的 WHERE 子句將查詢包裝在外部查詢中。我這樣做是為了模仿 Maximo 在使用關系連接到我的查詢/視圖時所做的事情。
查詢 1b：（GROUP BY；選擇性聚合）
性能非常好，因為使用了索引：只有 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 不能使用索引（慢）？
uj5u.com熱心網友回復：
您的兩個查詢與您使用的第一個查詢不同：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/333125.html