Group BY query utilizes indexes, but window function query doesn't-CodePudding

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 the queries in outer queries with WHERE clauses that select a specific WONUM. I’ve done this to mimic what Maximo does when it uses relationships to join to my queries/views.

Query 1b: (GROUP BY; selective aggregates)

Performance is very good 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 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)?

CodePudding user response：

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;