在oracle中优化Group BY

Question

我试图在视图中获取列值的一部分。这是SQL的一部分，METRIC_DETAILS2子查询花费了大量的时间。有什么方法可以优化查询吗?

目标:对于一个给定的警报，用逗号分隔field_value如下所示(原始数据)，我需要分离不同的信号指标和它的类型如下所示(所需数据)。

ABC-High累积
——在黑体中给出的是signal_metrics，后第二个连字符是它的类型。

Original data:
ALERT_ID       FIELD_VALUE
123            ABC-High-Cumulative, ABC-High-Recent, XYZ-High-Cumulative 

Desired Data :
ALERT_ID       SIGNAL_METRIC           SITE_SIGNAL_METRICS_TYPE
123            ABC-High                Recent,Cumulative
123            XYZ-High                Cumulative

WITH METRIC_DETAILS1 AS
( SELECT DISTINCT ALERT_ID, METRICS_WITH_TYPE, SUBSTR(METRICS_WITH_TYPE, 0, INSTR(METRICS_WITH_TYPE, '-', 1, 2)-1) AS SIGNAL_METRIC,
SUBSTR(METRICS_WITH_TYPE, INSTR(METRICS_WITH_TYPE, '-', 1, 2)+1) AS METRIC_TYPE
FROM (
SELECT ALERT_ID, REGEXP_SUBSTR(FIELD_VALUE, '[^,]+', 1, LEVEL) METRICS_WITH_TYPE
FROM (Select ALERT_ID,FIELD_VALUE
FROM CM_ALERT_PAYLOAD_DATA
WHERE FIELD_NAME IN ('Site Signal Metrics Type') )
CONNECT BY regexp_substr(FIELD_VALUE, '[^,]+', 1, LEVEL) IS NOT NULL)),
METRIC_DETAILS2 AS
( SELECT ALERT_ID, SIGNAL_METRIC, COUNT(*) AS METRIC_COUNT
FROM METRIC_DETAILS1
GROUP BY ALERT_ID, SIGNAL_METRIC ) ,
SIGNAL_METRIC_DETAILS AS
( SELECT METRIC_DETAILS2.ALERT_ID,METRIC_DETAILS2.SIGNAL_METRIC,
CASE
WHEN METRIC_COUNT=2 THEN 'Recent,Cumulative'
WHEN METRIC_COUNT=1 THEN METRIC_DETAILS1.METRIC_TYPE
ELSE NULL
END AS SITE_SIGNAL_METRICS_TYPE
FROM METRIC_DETAILS2
INNER JOIN METRIC_DETAILS1
ON METRIC_DETAILS1.ALERT_ID=METRIC_DETAILS2.ALERT_ID 
AND METRIC_DETAILS1.SIGNAL_METRIC=METRIC_DETAILS2.SIGNAL_METRIC
)
Select * from SIGNAL_METRIC_DETAILS;

请留下你的建议。谢谢!

Answer 1

如果您只需要连接来显示度量类型，那么您可以完全避免连接:如果您只有一种类型，那么任何聚合函数都足够了，因为您将获得这个单一值。如果需要将所有类型显示为逗号分隔的列表，则使用LISTAGG

WITH METRIC_DETAILS1 AS (
SELECT
ALERT_ID, METRICS_WITH_TYPE,
SUBSTR(METRICS_WITH_TYPE, 0, INSTR(METRICS_WITH_TYPE, '-', 1, 2)-1) AS SIGNAL_METRIC,
SUBSTR(METRICS_WITH_TYPE, INSTR(METRICS_WITH_TYPE, '-', 1, 2)+1) AS METRIC_TYPE
FROM (
SELECT
ALERT_ID,
REGEXP_SUBSTR(FIELD_VALUE, '[^,]+', 1, LEVEL) METRICS_WITH_TYPE
FROM (
Select ALERT_ID,FIELD_VALUE
FROM CM_ALERT_PAYLOAD_DATA
WHERE FIELD_NAME IN ('Site Signal Metrics Type')
)
CONNECT BY regexp_substr(FIELD_VALUE, '[^,]+', 1, LEVEL) IS NOT NULL
)
)
SELECT
ALERT_ID,
SIGNAL_METRIC,
decode(count(*), 2, 'Recent,Cumulative', max(METRIC_TYPE)) AS SITE_SIGNAL_METRICS_TYPE_hardcoded,
listagg(metric_type, ', ') within group (order by metric_type) as SITE_SIGNAL_METRICS_TYPE_aggregated 
from METRIC_DETAILS1
group by ALERT_ID, SIGNAL_METRIC

聚合呢:没有比聚合更快的计算累积值的方法了。但我确信这不是一个问题，因为这是一个非常基本的操作，已经存在多年了，所以DBMS可以有效地完成它。真正的"慢";这里是递归查询(最慢)和join，因为它强制实现第一个with。

您可能观察到聚合很慢，因为使用它的查询显示结果(在IDE中)比不使用它的查询慢得多。但这是一种欺骗:您可能很快得到前几行，但总体查询执行时间会很慢。实际上，有聚合和没有聚合的情况下，它可能大致相同。

Answer 2

REGEXP_SUBSTR是花费大量时间的操作。我找到了一种替代方法，通过使用XMLTABLE将分隔的列值拆分为行，现在获得了令人难以置信的性能。

...
SELECT ALERT_ID,
METRICS_WITH_TYPE,
SUBSTR(METRICS_WITH_TYPE, 0, INSTR(METRICS_WITH_TYPE, '-', 1, 2)-1) AS SIGNAL_METRIC,
SUBSTR(METRICS_WITH_TYPE, INSTR(METRICS_WITH_TYPE, '-', 1, 2)   +1) AS METRIC_TYPE
FROM
( SELECT DISTINCT ALERT_ID,
METRICS_WITH_TYPE
FROM METRIC_ALERTS,
xmltable(('"' || REPLACE(FIELD_VALUE, ',', '","') || '"') 
passing FIELD_VALUE 
columns METRICS_WITH_TYPE VARCHAR2(1000) path '.')
)
...

谢谢你的反馈@astentx