Expand all | Collapse all

WHERE clause WOs: Get children too

  • 1.  WHERE clause WOs: Get children too

    Posted Thu November 19, 2020 07:20 PM
    Edited by User1971 Thu November 19, 2020 07:28 PM
    I have a WHERE clause in the WOTRACK list view:

    classstructureid = '60720'
    and status = 'APPR'

    For the WOs that were returned, I also want to get their child WOs too.

    What's the best way to do this?

  • 2.  RE: WHERE clause WOs: Get children too

    Posted Thu November 19, 2020 07:27 PM
    Edited by User1971 Sat December 26, 2020 06:43 PM

    One way to do this is to wrap the WHERE clause in an EXISTS operator that references the WOANCESTOR table:

    exists (select wonum from woancestor where ancestor in (select wonum from workorder where

    classstructureid = '60720' and status = 'APPR'

    ) and wonum=workorder.wonum)

    The performance of that EXISTS operator is surprisingly good.

    I tried various other 'proper' ways of getting the children in a full-blown query, but EXISTS in the WHERE clause had the lowest explain-plan cost. I guess the saying, "It's not stupid, if it works" applies here!

  • 3.  RE: WHERE clause WOs: Get children too

    Posted Fri November 20, 2020 03:50 AM
    With 40+ million WOANCESTOR records this might not be as good as you might think.

    When you are compiling a query the first thing you must do is understand the unique key of a foreign table which will match the attributes from the primary table you are querying from. In Maximo you will learn that the best way to do this is to understand whether the object/table exists at SITE, ORG, SYSTEM or other levels, which you can do in Database Configuration.

    WORKORDER object exists at the SITE level and so you should always join by both WONUM and SITEID. Forgetting to join to the SITEID for site level objects is incredibly common, so you are not alone.

    You haven't done this in your code snippet but this message is for other techies. DO NOT join site level objects including the ORGID. I remember one query which a colleague was working on, the query ran to about 5-6 pages in a single SQL statement and was taking way over 30 minutes, I think it was actually more than an hour. There were 20+ million work orders. My colleague asked me whether I could look at the query to see whether I had any ideas. It was riddled with clauses of ORGID=ORGID. Take that out, and it will probably cut the amount of time to run the query by about half, it did. For every subquery, the database was having to check whether for a large number of records the ORGID in one row was equal to the ORGID in the other. In Maximo, the SITEID is unique across all organizations, making a check on ORGID superfluous for two tables that are both at the SITE level, for example, finding the work orders belonging to an asset or location. 

    Andrew Jeffery

  • 4.  RE: WHERE clause WOs: Get children too

    Posted Sat November 21, 2020 06:20 PM
    Edited by User1971 Sat November 21, 2020 06:27 PM

    Thanks Jeffery. What you're saying makes perfect sense.

    It's really strange though, when I add and siteid=workorder.siteid  to the end of the EXISTS query from my original comment, it actually got slower. I have no idea why though.

    Before adding and siteid=workorder.siteid: the Oracle explain-plan cost was 1,792.
    After adding and siteid=workorder.siteid: the Oracle explain-plan cost was 1,896.

    That's 5.5% more expensive. Clearly I have no idea how Oracle works.

  • 5.  RE: WHERE clause WOs: Get children too

    Posted Mon November 23, 2020 03:25 AM
    Hi - Not sure where my reply over the weekend went. I'm assuming you received it, perhaps it was sent privately.

    Anyway the book I was referring to which I found useful in my near year on performance analysis on a very large database, 20million+ work orders, was called "SQL Performance Explained" by Markus Winand. ISBN 978-3-9503078-2-5. It is not a thick book, just short of 200 pages, but it makes it a relatively easy read for a difficult technical subject.

    It uses the Explain Plan to compare queries and helps to explain what the text in the Explain Plan means rather than just focusing on the end result. After I got more into Explain Plan I started to look more carefully at some of the earlier statements in the query rather than just the ones towards the end.

    My copy covers Oracle, SQLServer, MySQL and PostgresSQL.

    Andrew Jeffery

  • 6.  RE: WHERE clause WOs: Get children too

    Posted Mon November 23, 2020 08:26 PM
    Edited by User1971 Mon November 23, 2020 08:27 PM

    Thanks Andrew. That really helps. I ordered that book this morning. Looking forward to reading it.

    And yes, I got your other message as a private message:

    "Learning to work with Explain Plan takes time. What I would do is learn what the words in Explain Plan actually mean. I spent a year on performance tuning, and that was one of my Eureka moments, when I actually understood the differences. I'll try and find the book I found for reference.

    Also, use a Maximo SQL log to trap the actual SQL performed. You'll find that there can be some big differences, Maximo will be adding to the query your security profile, Site access, Data Restrictions, Global or Application restrictions, etc. Use the Explain Plan on the result.

    My biggest tip on performance though will be to be very carefully about Workorder indexes, especially those involving the Status attribute. Indexes are easy to add, less easy to take out if you do not document why they were created in the first place. Workorder is often the largest table​. I've seen indexes involving a status field being greater than 1GB, that is a lot of disk IO when you change Status, even more so if it is also changing status on child work orders and tasks. More so again when there are more than 10 indexes involving the Status column !"


    Thanks again for your help. I've been struggling with this explain plan business.

  • 7.  RE: WHERE clause WOs: Get children too

    Posted Tue December 01, 2020 04:33 AM
    Edited by User1971 Sat December 26, 2020 06:58 PM

    This is the current query:

    with nt as (select * from maxsnb.wotaskrollup_vw) --non-task WOs
        trunc(actstart)  as actstart,
        trunc(actfinish) as actfinish,
        exists (select wonum from maxsnb.woancestor where wonum=nt.wonum and ancestor in (select wonum from nt where 
            --parameters go here
    order by
        case when parent is null then null else hierarchypath end   nulls first --parents first, children second

    I've left siteid=nt.siteid out of the subqueries for now, since it was making the explain-plan-cost go up. I'll need to revisit this.

  • 8.  RE: WHERE clause WOs: Get children too

    Posted Sat December 26, 2020 07:00 PM
    Edited by User1971 Sat January 23, 2021 01:48 AM

    For those of us that have purchased the book SQL Performance Explained by Markus Winand, the book can also be downloaded here: