Início / dba / Perguntas / 299932
Accepted
User1974
Asked: 2021-09-22 11:51:37 +0800 CST

Agrupe por x, obtenha outros campos também (comparando opções e desempenho)

  • 772

Eu sou um novato, tentando encontrar a melhor maneira de resumir meus dados. Oráculo 19c.

Eu tenho uma tabela WORKORDER que tem ordens de serviço (ISTASK=0) e tarefas (ISTASK=1).

  • As tarefas são filhas de ordens de serviço.
  • Ordens de serviço e tarefas são agrupadas por WOGROUP.

Os custos são divididos em quatro colunas:

  • actlabcost(custo real de mão de obra)
  • actmatcost(custo real do material)
  • acttoolcost(custo real da ferramenta)
  • actservcost(custo real do serviço)

As colunas de custo não são anuláveis. Portanto, não precisamos nos preocupar em converter nulos em zeros para evitar fazer contas com nulos.

Select 'WO1361' as WONUM, 'WO1361' as WOGROUP, 0 as ISTASK, 0 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167457977' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1362' as WONUM, 'WO1362' as WOGROUP, 0 as ISTASK, 0 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167458280' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1363' as WONUM, 'WO1363' as WOGROUP, 0 as ISTASK, 270.14 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167483430' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1367' as WONUM, 'WO1363' as WOGROUP, 1 as ISTASK, 540.27 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167482806' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1370' as WONUM, 'WO1363' as WOGROUP, 1 as ISTASK, 202.6 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167483431' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1364' as WONUM, 'WO1364' as WOGROUP, 0 as ISTASK, 88.86 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167459454' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1366' as WONUM, 'WO1364' as WOGROUP, 1 as ISTASK, 33.77 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167458946' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1365' as WONUM, 'WO1365' as WOGROUP, 0 as ISTASK, 67.53 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167459331' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1368' as WONUM, 'WO1368' as WOGROUP, 0 as ISTASK, 236.37 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167461627' as OTHER_WO_COLUMNS FROM DUAL

WONUM  WOGROUP     ISTASK ACTLABCOST ACTMATCOST ACTTOOLCOST ACTSERVCOST OTHER_WO_COLUMNS
------ ------- ---------- ---------- ---------- ----------- ----------- ----------------
WO1361 WO1361           0          0          0           0           0 167457977       
WO1362 WO1362           0          0          0           0           0 167458280       
WO1363 WO1363           0     270.14          0           0           0 167483430       
WO1367 WO1363           1     540.27          0           0           0 167482806       
WO1370 WO1363           1      202.6          0           0           0 167483431       
WO1364 WO1364           0      88.86          0           0           0 167459454       
WO1366 WO1364           1      33.77          0           0           0 167458946       
WO1365 WO1365           0      67.53          0           0           0 167459331       
WO1368 WO1368           0     236.37          0           0           0 167461627       


Notice rows 3-5 are in WOGROUP #WO1363. And rows 6-7 are in WOGROUP #WO1364.

Problema:

Desejo somar os custos da ordem de serviço por WOGROUP (incluindo os custos da tarefa), mas não quero mostrar as linhas da tarefa no conjunto de resultados. Em outras palavras, quero acumular os custos da tarefa para suas ordens de serviço pai.

Para as linhas da ordem de serviço, também quero incluir outras colunas que não foram agrupadas (ou seja, OTHER_WO_COLUMNS).

Eu encontrei algumas maneiras de fazer isso.

Opção nº 1: (GROUP BY, JOIN e SUM)

A consulta faz um GROUP BY (SUM) em uma subconsulta para obter os custos totais da ordem de serviço. Em seguida, ele seleciona as ordens de serviço (excluindo tarefas) e se junta à subconsulta para trazer os custos totais.

--The suffix "_ti" stands for "tasks included".
select
    a.wonum,
    a.istask,
    b.actlabcost_ti,
    b.actmatcost_ti,
    b.actservcost_ti,
    b.acttoolcost_ti,
    b.acttotalcost_ti,
    other_wo_columns
from
    cte a
left join
    (
    select 
        wogroup as wonum, 
        sum(actlabcost)  as actlabcost_ti,
        sum(actmatcost)  as actmatcost_ti,
        sum(actservcost) as actservcost_ti,
        sum(acttoolcost) as acttoolcost_ti,
        sum(actlabcost + actmatcost + actservcost + acttoolcost) as acttotalcost_ti
    from 
        cte 
    group by 
        wogroup
    ) b
    on a.wonum = b.wonum
where
    istask = 0

WONUM      ISTASK ACTLABCOST_TI ACTMATCOST_TI ACTSERVCOST_TI ACTTOOLCOST_TI ACTTOTALCOST_TI OTHER_WO_COLUMNS
------ ---------- ------------- ------------- -------------- -------------- --------------- ----------------
WO1361          0             0             0              0              0               0 167457977       
WO1362          0             0             0              0              0               0 167458280       
WO1363          0       1013.01             0              0              0         1013.01 167483430       
WO1364          0        122.63             0              0              0          122.63 167459454       
WO1365          0         67.53             0              0              0           67.53 167459331       
WO1368          0        236.37             0              0              0          236.37 167461627

Executei a consulta em uma tabela de produção completa (a tabela WORKORDER tem 4.500 linhas) e obtive este plano de explicação:

Plan hash value: 1879239811
 
---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |  9841 |   586K|   622   (2)| 00:00:01 |
|   1 |  SORT GROUP BY      |           |  9841 |   586K|   622   (2)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |           |  9841 |   586K|   620   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| WORKORDER |  4609 |   184K|   310   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| WORKORDER |  9841 |   192K|   310   (1)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."WONUM"="WOGROUP"(+))
   3 - filter("A"."ISTASK"=0)

O custo estimado é de 622 .

Opção nº 2: (função analítica SUM)

Eu encontrei uma maneira de fazer isso com a função analítica SUM . E envolvi a consulta da função analítica SUM em uma consulta externa que oculta as linhas da tarefa.

--The suffix "_ti" stands for "tasks included".  
select
    wonum,
    istask,
    actlabcost_ti,
    actmatcost_ti,
    acttoolcost_ti,
    actservcost_ti,
    acttotalcost_ti,
    other_wo_columns
from
    (
    select
        wogroup as wonum,
        istask,
        sum(actlabcost ) over (partition by wogroup) as actlabcost_ti,
        sum(actmatcost ) over (partition by wogroup) as actmatcost_ti,
        sum(acttoolcost) over (partition by wogroup) as acttoolcost_ti,
        sum(actservcost) over (partition by wogroup) as actservcost_ti,
        sum(actlabcost + actmatcost + acttoolcost + actservcost) over (partition by wogroup) as acttotalcost_ti,
        other_wo_columns
    from
        cte
    )
where
    istask=0

WONUM      ISTASK ACTLABCOST_TI ACTMATCOST_TI ACTTOOLCOST_TI ACTSERVCOST_TI ACTTOTALCOST_TI OTHER_WO_COLUMNS
------ ---------- ------------- ------------- -------------- -------------- --------------- ----------------
WO1361          0             0             0              0              0               0 167457977       
WO1362          0             0             0              0              0               0 167458280       
WO1363          0       1013.01             0              0              0         1013.01 167483430       
WO1364          0        122.63             0              0              0          122.63 167459454       
WO1365          0         67.53             0              0              0           67.53 167459331       
WO1368          0        236.37             0              0              0          236.37 167461627

Também executei esta consulta em produção e obtive este plano de explicação:

Plan hash value: 2003557620
 
---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |  9841 |  1749K|   312   (2)| 00:00:01 |
|*  1 |  VIEW               |           |  9841 |  1749K|   312   (2)| 00:00:01 |
|   2 |   WINDOW SORT       |           |  9841 |   394K|   312   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| WORKORDER |  9841 |   394K|   310   (1)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("ISTASK"=0)

O custo estimado é de 312 , que é aproximadamente metade da primeira consulta.

Eu acho que é mais rápido porque ele faz apenas uma verificação completa da tabela (a outra consulta fez duas verificações completas).

Pergunta:

Qual é a melhor/mais rápida maneira de resumir esses dados?

Como mencionado, notei que a opção #2 é mais rápida que a #1. Mas para ser honesto, o número 2 parece um pouco atrasado para mim. Recebi algumas críticas bastante duras por essa abordagem no Stack Overflow, então acho que não é uma boa maneira de estruturar uma consulta.

oracle query-performance

2 respostas

  1. Best Answer

    Minha clara preferência está na opção #2. IMHO é bastante em linha reta. Você agrega por partição, depois mantém apenas as linhas que lhe interessam. Como o cálculo de funções de janela sem cláusula de enquadramento ( BETWEEN) é muito bem otimizado, não é que você faça muito e jogue fora depois.

    No entanto, alguns comentários sobre a Opção #1:

    • Por que você fez uma left join(não uma junção interna?)

    • Em vez da auto-junção, você pode buscar os other_wo_columnsagregados seletivos usando:

      MAX(CASE WHEN istask = 0 THEN other_wo_column END)

      Se houver apenas uma linha istask = 0por grupo, ele retornará apenas o valor dessa linha. Eu usei maxporque funciona em todos os tipos de dados - como min- mas não como sum.

      Se você tiver muitas colunas, o código fica um pouco confuso, é claro.

      É bem possível que esta seja a abordagem mais rápida, mas provavelmente não por uma grande margem em comparação com a #2.

      Mais sobre esta técnica:

    Por fim, sobre o CUSTO

    Não use COST como uma métrica para comparar diferentes abordagens de consulta. O valor COST é usado para determinar o melhor plano de execução possível entre as várias possibilidades para a mesma consulta no mesmo banco de dados com os mesmos dados . O cálculo do valor COST é altamente otimizado para esse propósito específico - ou seja, tudo o que não é necessário para atender a esse propósito específico é simplesmente ignorado por motivos de desempenho. Consequentemente, usar para algo diferente tem grandes chances de dar resultados errados.

    Não é apenas para ser usado para comparar consultas diferentes .

    • 1

  2. Para acompanhar a resposta de @Markus Winand:

    ...Re Opção #1:

    • Em vez da auto-junção, você pode buscar os other_wo_columnsagregados seletivos usando:

      MAX(CASE WHEN istask = 0 THEN other_wo_column END)

      Se houver apenas uma linha istask = 0por grupo, ele retornará apenas o valor dessa linha. Eu usei maxporque funciona em todos os tipos de dados - como min- mas não como sum.

      Se você tiver muitas colunas, o código fica um pouco confuso, é claro.

      É bem possível que esta seja a abordagem mais rápida, mas provavelmente não por uma grande margem em comparação com a #2.

      Mais sobre esta técnica:

    Peguei essas sugestões e fiz uma nova versão da Query #1:

    Consulta 1b: (agregados seletivos)

    --The suffix "_ti" stands for tasks included.    
select 
    wogroup as wonum, 
    sum(actlabcost)  as actlabcost_ti,
    sum(actmatcost)  as actmatcost_ti,
    sum(actservcost) as actservcost_ti,
    sum(acttoolcost) as acttoolcost_ti,
    sum(actlabcost + actmatcost + actservcost + acttoolcost) as acttotalcost_ti,
    max(case when istask = 0 then other_wo_columns end) as other_wo_columns
from 
    cte 
group by 
    wogroup


WONUM  ACTLABCOST_TI ACTMATCOST_TI ACTSERVCOST_TI ACTTOOLCOST_TI ACTTOTALCOST_TI OTHER_WO_COLUMNS
------ ------------- ------------- -------------- -------------- --------------- ----------------
WO1361             0             0              0              0               0 167457977       
WO1362             0             0              0              0               0 167458280       
WO1363       1013.01             0              0              0         1013.01 167483430       
WO1364        122.63             0              0              0          122.63 167459454       
WO1365         67.53             0              0              0           67.53 167459331       
WO1368        236.37             0              0              0          236.37 167461627

    Eu sei que Markus sugeriu que não deveríamos comparar custos entre consultas. Mas vou fazê-lo de qualquer maneira porque acho útil.

    Executei a consulta em uma tabela de produção completa (a tabela WORKORDER tem 4.500 linhas) e obtive este plano de explicação:

    Plan hash value: 318895193
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  4749 |   153K|   312   (2)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |  4749 |   153K|   312   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| WORKORDER | 10111 |   325K|   310   (1)| 00:00:01 |
--------------------------------------------------------------------------------

    O custo estimado é de 312 , que é o mesmo da segunda consulta. Portanto, a Consulta #1b e a Consulta #2 estão empatadas.

    A consulta #1b é rápida porque faz apenas uma varredura completa da tabela. Ao contrário da Consulta 1(a), que fez duas varreduras completas de tabela.

    • 0

relate perguntas