选择工单(通过参数)及其子项 [英] Select workorders (via parameters) and their children

问题描述

我有一个 Oracle 12c 查询，最终将用于 BIRT 报告(在 IBM 的 Maximo Asset Management 平台中).

---

查询/报告将检索记录如下:

1. 根据用户选择的参数检索工单.
   • 所有参数都是可选的.
   • 出于测试目的，我使用

     ^{蓝色方框显示有孩子的父母的例子.}

     ---

     查询:

     --短格式定义:--wo = 工单--act = 实际(实际成本、实际日期等)--pmtr = 参数和wo_pmtr as ( --WOs 按参数过滤选择赢得，家长，层次路径，类结构ID，分配，工作类型，地位，trunc(actstart) 作为actstart，trunc(actfinish) 作为 actfinish，实验室成本，行动成本，行动服务成本，行动工具成本，实际总成本从工单在哪里--在Toad中使用绑定变量作为参数(:wonum 为空或wonum = :wonum)和 (:division 为空或除法 = :division)和 (:worktype 为 null 或 worktype = :worktype)和 (:status 为 null 或 status = :status)和 (:actstart 为空或 trunc(actstart) >= :actstart)和 (:actfinish 为 null 或 trunc(actfinish) <= :actfinish)--检索分类与参数匹配的工单-- 并检索任何子分类--分类层次结构不要与工作指令层次结构混淆and (:classstructureid is null or (exists (select 1 from classancestor where ((ancestor = :classstructureid)) and (classstructureid=wo.classstructureid)))))选择wo_pmtr.wonum,wo_pmtr.parent,wo_pmtr.hierarchypath,wo_pmtr.classstructureid,wo_pmtr.division,wo_pmtr.worktype,wo_pmtr.status,wo_pmtr.actstart,wo_pmtr.actfinish,wo_pmtr.actlabcost,wo_pmtr.actmatcost,wo_pmtr.actservcost,wo_pmtr.acttoolcost,wo_pmtr.acttotalcost,合并(wo_pmtr.parent，wo_pmtr.wonum)parent_coalesced从wo_pmtr --按参数过滤的WOsunion --union 将删除重复项(与 union all 不同)select --select 过滤后的 WO 的孩子wo.wonum,我的父母，wo.hierarchypath,wo.classstructureid,wo.division,wo.worktype,wo.status,trunc(wo.actstart) 作为 actstart，trunc(wo.actfinish) 作为 actfinish，wo.actlabcost,wo.actmatcost,wo.actservcost,wo.acttoolcost,wo.acttotalcost,合并(wo.parent，wo.wonum)parent_coalesced从工作订单 wo -- 从基本工作订单表中选择*不*按参数过滤左连接wo_pmtrwo.parent = wo_pmtr.wonum在哪里wo.parent 不为空——其中 WO 是一个孩子并且 wo_pmtr.wonum 不为空——其中 WO 是过滤后的 WO 的子项--注意:其中一些工单可能已通过第一个查询中的参数选择-- 如果存在重复项，我们需要消除它们(通过联合)订购parent_coalesced，层次路径

     ^db<>fiddle

     有没有办法减少全表扫描次数来提高性能?

     解决方案

     您可以在 WORKORDER 表上使用许多索引.在您的查询中突出的是，您不是指应该几乎总是与 WONUM 一起出现的 SITEID 列，因为它们一起构成了主键"，这在 Oracle Maximo 中是唯一的索引.

     您可以通过运行此查询来确认这一点:

      select attributename from maxattribute where objectname='WORKORDER' AND PRIMARYKEYCOLSEQ IS NOT NULL;

     此外，一个好的做法是确定您需要哪些索引来查询所需的数据.您可以使用自己喜欢的 SQL 工具或从 Database Configuration Maximo 应用程序查看表的索引.

     I have a Oracle 12c query that will eventually be used in a BIRT report (in IBM's Maximo Asset Management platform).

     ---

     The query/report will retrieve records as follows:

     1. Retrieve workorders based on the parameters that were selected by the user.
        • All of the parameters are optional.
        • For testing purposes, I'm using bind variables as parameters (in Toad for Oracle).
        • It doesn't matter if the workorders are parents, children, or neither.
     2. Of the workorders that were retrieved, also select any children of those workorders.
        • There are only two levels to the parent/child hierarchy: parents and children (no grandchildren, etc.)

     ---

     The output looks like this:

     ^{The blue boxes show examples of parents with children.}

     ---

     The query:

     --short form definitions:
     --wo   = workorder
     --act  = actual (actual cost, actual date, etc.)
     --pmtr = parameters
     
     with 
     wo_pmtr as (  --WOs filtered by parameters
     select
         wonum,
         parent,
         hierarchypath,
         classstructureid,
         division,
         worktype,
         status, 
         trunc(actstart)  as actstart,
         trunc(actfinish) as actfinish,
         actlabcost,
         actmatcost,
         actservcost,
         acttoolcost,
         acttotalcost   
     from
         workorder wo
     where   
         --using bind variables as parameters in Toad
             (:wonum            is null     or wonum             = :wonum)
         and (:division         is null     or division          = :division)
         and (:worktype         is null     or worktype          = :worktype)
         and (:status           is null     or status            = :status)
         and (:actstart         is null     or trunc(actstart)  >= :actstart)
         and (:actfinish        is null     or trunc(actfinish) <= :actfinish)
     
         --retrieve workorders where the classification matches the parameter 
         --and retrieve any child classifications too
         --the classification hierarchy is not to be confused with the workorder hierarchy
     
         and (:classstructureid is null     or (exists (select 1 from classancestor where ((ancestor = :classstructureid)) and (classstructureid=wo.classstructureid))))
     )
     
     select
         wo_pmtr.wonum,
         wo_pmtr.parent,
         wo_pmtr.hierarchypath,
         wo_pmtr.classstructureid,
         wo_pmtr.division,
         wo_pmtr.worktype,
         wo_pmtr.status, 
         wo_pmtr.actstart,
         wo_pmtr.actfinish,
         wo_pmtr.actlabcost,
         wo_pmtr.actmatcost,
         wo_pmtr.actservcost,
         wo_pmtr.acttoolcost,
         wo_pmtr.acttotalcost,
         coalesce(wo_pmtr.parent, wo_pmtr.wonum) parent_coalesced
     from
         wo_pmtr      --WOs filtered by parameters
     union            --union will remove duplicates (unlike union all)
     select           --select children of the filtered WOs
         wo.wonum,
         wo.parent,
         wo.hierarchypath,
         wo.classstructureid,
         wo.division,
         wo.worktype,
         wo.status, 
         trunc(wo.actstart)  as actstart,
         trunc(wo.actfinish) as actfinish,
         wo.actlabcost,
         wo.actmatcost,
         wo.actservcost,
         wo.acttoolcost,
         wo.acttotalcost,
         coalesce(wo.parent, wo.wonum) parent_coalesced
     from
         workorder wo  --select from the base workorder table *without* filtering by the parameters
     left join
         wo_pmtr
         on wo.parent = wo_pmtr.wonum
     where
         wo.parent         is not null  --where WO is a child
         and wo_pmtr.wonum is not null  --where WO is a child of the filtered WOs 
                                        --caution: some of those workorders might have already been selected via the parameters in the first query
                                        --we need to eliminate duplicates if they exist (via the union)
     order by
         parent_coalesced, 
         hierarchypath
     

     ^{db<>fiddle here}

     ---

     Question:

     The query makes several full table scans:

     Is there a way to reduce the number of full table scans to improve performance?

     解决方案

     There are many indexes that you can use on the WORKORDER table. What stands out in your query is that you're not referring to the SITEID column which should almost always come with the WONUM as together they compose the "primary key", which in Oracle Maximo is a unique index.

     You can confirm this by running this query:

        select attributename from maxattribute where objectname='WORKORDER' AND PRIMARYKEYCOLSEQ IS NOT NULL;
     

     Also, a good practice is to identify which indexes you will need to query the needed data. You can have a look at the table's indexes with your favorite SQL tool or from the Database Configuration Maximo application.

     这篇关于选择工单(通过参数)及其子项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！