I use a COTS system by IBM called Maximo Asset Management. The system has a WORKORDER table with 350,000 rows.
Maximo has a concept called relationships that can be used to pull in data from related records.
How relationships work:
For each individual WORKORDER record, the system uses the WHERE clause from the relationship to run a select query to pull in the related record (screenshot).
Related Records:
In this case, the related records are rows in a custom database view called WOTASKROLLUP_VW.
In a related post, I explored different SQL rollup techniques that I could use in the view: Group by x, get other fields too. The options I explored performed similarly to each other when I ran them on the full WORKORDER table.
However, in reality, Maximo is designed to only get one row at a time -- via individual select statements. As such, the queries are performing very differently when only selecting a single WORKORDER record.
I’ve wrapped each query in an outer query with a WHERE clause that selects a specific work order. I’ve done this to mimic what Maximo does when it uses relationships.
Query 1b: (GROUP BY; selective aggregates)
Performance is very good, even when only selecting a single record, because indexes were used (only 37 milliseconds).
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')
Query #2: (SUM window function)
Performance is relatively slow, when selecting a single record, because indexes weren't used (3 seconds).
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)
Question:
Why was the GROUP BY query in #1B able to use indexes (fast), but the Sum Window Function in #2 wasn't able to use indexes (slow)?
Your two queries are different as the first on you use:
select wogroup as wonum,
And the second one you just use:
select wonum,
Which means you won't use an index on WOGROUP as you are filtering on the WONUM column instead of the WOGROUP column (that just happens to have been aliased to WONUM).
It looks like your second query could be corrected and reduced (by moving the filter to the inner sub-query and get rid of the partition by as you are then already filtering) to:
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;