sql - 选择工单（通过参数）及其子代

问题描述

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

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

根据用户选择的参数检索工单。
- 所有参数都是可选的。
- 出于测试目的，我使用绑定变量作为参数（在 Toad for Oracle 中）。
- 工单是父母还是孩子都没有关系。
在检索到的工单中，还要选择这些工单的任何子代。
- 父/子层次结构只有两个级别：父母和孩子（没有孙子等）

输出如下所示：

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

查询：

--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<>在这里摆弄}

问题：

该查询进行了几次全表扫描：

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

标签： sqloracleoracle12cquery-performancemaximo

您可以在 WORKORDER 表上使用许多索引。在您的查询中突出的是，您没有引用 SITEID 列，该列几乎总是应与 WONUM 一起出现，因为它们共同构成“主键”，在 Oracle Maximo 中是唯一索引。

您可以通过运行以下查询来确认这一点：

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

此外，一个好的做法是确定查询所需数据所需的索引。您可以使用您最喜欢的 SQL 工具或从数据库配置 Maximo 应用程序查看表的索引。

sql - 选择工单（通过参数）及其子代

问题描述

解决方案

推荐阅读