Join / Log in
Learn how to increase the operational efficiency of the assets you manage, and improve overall equipment effectiveness by using IoT data and AI.
Reduce the operational costs of the facilities you manage, and create more engaging occupant experiences through the application of IoT data and AI.
Learn how IoT data and AI are being applied to transform the end-to-end engineering lifecycle.
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?
exists (select wonum from woancestor where ancestor in (select wonum from workorder whereclassstructureid = '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 in Toad, 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!
classstructureid = '60720'and status = 'APPR'
exists (select wonum from woancestor where ancestor in (select wonum from workorder whereclassstructureid = '60720'and status = 'APPR') and wonum=workorder.wonum)
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.
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.