将参数化查询的结果插入到 CTE 中

Insert result of a parametrized query into a CTE

提问人：Thomas 提问时间：3/20/2023 最后编辑：Thomas 更新时间：3/25/2023 访问量：118

问：

我正在 Oracle 中查询一个包含数十亿行 （） 的表。该表按月分区（是变化的月份）。DBA 希望我们按月的变化来过滤查询。DBA 警告说，（i） 不这样做可能会导致查询速度变慢，并且 （ii） 可能会影响其他用户，因此他可能会终止未过滤的查询。BIG_TABLEDTE

由于我需要几个月（有时长达 100 个月），因此我使用参数化查询来传递我需要的月份（更多内容见下文）。我将所有月份的结果收集到一个中间表（每月包含大约 2 亿行），然后汇总数据（用于分析）。总和必须按任何变化月份完成。INTERMEDIATE_TABLEFINAL_TABLECHR

-- query1
CREATE TABLE
  INTERMEDIATE_TABLE (
    CHR VARCHAR2(255),
    NBR NUMBER,
    DTE DATE
);

-- query2
INSERT INTO
  INTERMEDIATE_TABLE
SELECT
  CHR,
  NBR,
  DTE
FROM
  BIG_TABLE
WHERE
  DTE = TO_DATE(?, 'YYYY-MM-DD');

-- query3
CREATE TABLE
  FINAL_TABLE AS
SELECT
  CHR,
  SUM(NBR) AS NBR
FROM
  INTERMEDIATE_TABLE
GROUP BY
  CHR;

将查询保存为字符串（、、）后，我使用 R 的 DBI 来运行查询。 将日期一一传递给 中的相等比较，因此查询执行 3 次（即列表不是一次性传递的）。query1query2query3dbExecute()query2

library(DBI)
dbConnect(odbc::odbc(), ...)
dbExecute(con, query1)
dbExecute(con, query2, params = list(c("2020-01-01", "2020-02-01", "2020-03-01")))
dbExecute(con, query3)

我想是暂时的。我可以进行 CTE 吗？INTERMEDIATE_TABLEINSERT INTO

我对替代解决方案持开放态度，但我正在寻找 ANSI 解决方案，因为计划迁移到 PostgreSQL。我想避免使用临时表，因为在 Oracle 中只有数据是临时的，而不是表。当然，事后我可以只做桌子，但首先让它成为临时的似乎更优雅/更明确（而且更有效？DROP

一个可重现的例子：

CREATE TABLE
  BIG_TABLE (
    CHR VARCHAR2(255),
    NBR NUMBER,
    DTE DATE
);

INSERT ALL
  INTO BIG_TABLE VALUES ('A', 2, DATE '2020-01-01')
  INTO BIG_TABLE VALUES ('B', 3, DATE '2020-01-01')
  INTO BIG_TABLE VALUES ('A', 1, DATE '2020-02-01')
  INTO BIG_TABLE VALUES ('B', 2, DATE '2020-02-01')
  INTO BIG_TABLE VALUES ('A', 3, DATE '2020-02-01')
  INTO BIG_TABLE VALUES ('B', 2, DATE '2020-03-01')
  INTO BIG_TABLE VALUES ('B', 4, DATE '2020-03-01')
  INTO BIG_TABLE VALUES ('C', 1, DATE '2020-03-01')
  INTO BIG_TABLE VALUES ('B', 4, DATE '2020-04-01')
  INTO BIG_TABLE VALUES ('D', 2, DATE '2020-05-01')
SELECT 1 FROM DUAL;

期望输出：

CHR NBR
  A   6
  B  11
  C   1
预言机 ANSI-SQL

评论

答：

0赞 nbk 3/20/2023 #1

您可以使用子查询来获取中间表并使用它，以便所有表都可以在一个查询中运行。

CREATE TABLE
  FINAL_TABLE AS
SELECT
  CHR,
  SUM(NBR) NBR
FROM
  (SELECT
  CHR,
  NBR,
  DTE
FROM
  BIG_TABLE
WHERE
  DTE = TO_DATE(?, 'YYYY-MM-DD')) INTERMEDIATE_TABLE
GROUP BY
  CHR;

作为 CTE

CREATE TABLE FINAL_TABLE 
AS 
WITH INTERMEDIATE_TABLE AS ( 
  SELECT
      CHR,
      NBR,
      DTE
    FROM
      BIG_TABLE
    WHERE
      DTE = TO_DATE(?, 'YYYY-MM-DD')
) 
    SELECT
      CHR,
      SUM(NBR) NBR
FROM INTERMEDIATE_TABLE
GROUP BY CHR;

评论

0赞 Thomas 3/20/2023
谢谢！但是，经过测试，我收到一个错误。也许与这个问题有关？ORA-01036
1赞 Paul W 3/20/2023
对于 CTAS，您必须为未直接从另一列获取的任何内容添加别名。因此，这意味着 SUM（NBR） 需要一个别名。但是看看我的答案，子查询、CTE、中间表对于这种用途都毫无意义。
1赞 nbk 3/20/2023
是的，你是对的，你需要一个别名，但这只是一个简短的例子，所以你的方法不适用于真正的查询，我的问题是他真的可以将日期列表传递给相等的比较吗？
1赞 jarlh 3/20/2023
@Thomas，只是一个注释 - CTE 是一个子查询。
1赞 nbk 3/20/2023
@Thomas我用您的代码更改来回答日期比较
0赞 Paul W 3/20/2023 #2

你根本不需要任何类型的中间阶段，甚至不需要子查询。

CREATE TABLE FINAL_TABLE AS
SELECT CHR,
       SUM(NBR) NBR
  FROM BIG_TABLE
 WHERE DTE = ?
 GROUP BY CHR;

当然，对于 10 亿行，您需要并行性，根据表的配置方式，您可能会或可能不会获得并行性。明确请求它可能会有所帮助：

CREATE TABLE FINAL_TABLE PARALLEL (DEGREE 16) NOLOGGING  AS
SELECT CHR,
       SUM(NBR) NBR
  FROM BIG_TABLE
 WHERE DTE = ?
 GROUP BY CHR;

如果您需要几个月，您有多种选择。如果这是一个分区表，我建议以最大化按分区划分并行线程分布的方式进行单个查询：

CREATE TABLE final_table PARALLEL (DEGREE 16) NOLOGGING AS
SELECT chr,
       SUM(nbr) nbr
  FROM (SELECT chr,
               SUM(nbr) nbr
          FROM big_table
         WHERE dte = ?
         GROUP BY chr
        UNION ALL
        SELECT chr,
               SUM(nbr) nbr
          FROM big_table
         WHERE dte = ?
         GROUP BY chr
        UNION ALL
        SELECT chr,
               SUM(nbr) nbr
          FROM big_table
         WHERE dte = ?
         GROUP BY chr)
  GROUP BY chr

如果您需要拉取多个月份，并将它们全部拉到此表中，而一次只拉取一个月，则预先创建该表并插入每个月：

CREATE TABLE final_table(dte date,chr varchar2(18),nbr integer); -- one time

INSERT /*+ append */ into final_table
SELECT /*+ parallel(16) */
       dte,
       chr,
       SUM(nbr) nbr
  FROM big_table
 WHERE dte = ?
 GROUP BY dte, 
          chr;

COMMIT;

-- repeat for every month.

最后，如果你需要跨时间的整体聚合，你可以做一个分组依据来排除日期：

SELECT chr,
       SUM(nbr) nbr
  FROM final_table
 GROUP BY chr

评论

1赞 Paul W 3/20/2023
我的答案是参数化查询（使用绑定变量）。也许你可以解释为什么它不起作用，我可以提出一些建议。
1赞 Paul W 3/20/2023
我编辑了我的答案，了解如何在执行 DBA 要求的同时用多个月填充FINAL_TABLE。
2赞 Paul W 3/20/2023
再次编辑了我的答案，请参阅最后一部分。虽然，作为一个DBA，我会质疑你的DBA的推理......如果你需要 100 个月，要求你一次做一个月并不是一个非常合理的限制。有了这么多，分区不再是这种限制的合理原因。也许没有足够的 TEMP 空间供 GROUP BY 使用？如果是这样，如果周围有十亿行表，则应将 TEMP 大小调整到适当的级别，以支持查询此类对象。我们的用户会定期汇总数十亿行，而无需创建任何表格或分块进行汇总。
1赞 Paul W 3/20/2023
DTE = TO_DATE（？， 'YYYY-MM-DD'） 仅在绑定字符串时适用。通常，应用程序应绑定正确的数据类型。如果绑定真实日期，则不会在它周围使用TO_DATE。
2赞 Paul W 3/20/2023
我认为你应该回到你的 DBA 那里，询问一次一个月的限制的原因。你正在变得复杂，并在没有任何明确理由的情况下为自己制造困难。
0赞 Chris Saxon 3/20/2023 #3

请考虑使用实例化视图 （MV） 而不是临时表来执行此操作。这会产生相同的效果 - 您正在预计算结果并将其存储在表中 - 但具有更多内置控件来帮助您刷新数据。

例如，创建以下 MV：

create materialized view summaries as
  select chr, trunc ( dte, 'mm' ) mth, sum ( nbr ) tot
  from   big_table
  group  by chr, trunc ( dte, 'mm' );

这将存储一行/chr/月。除非每个月有数百万个“chr”值，否则这应该足以避免“按月过滤”的要求，因为您每次读取的数据要少得多。

如果您制作了 MV ，则数据库将自动为您保持最新状态。fast refresh on commit

即使你被迫采用“按月过滤”的方法，你仍然可以将其构建到MV中。

创建一个筛选表来存储所需的月份，在每次运行之前插入值，然后刷新 MV：

create table filter_table (
  month_value date
);

create materialized view summaries as
  select chr, trunc ( dte, 'mm' ) mth, sum ( nbr ) tot
  from   big_table bt
  join   filter_table ft
  on     trunc ( dte, 'mm' ) = ft.month_value
  group  by chr, trunc ( dte, 'mm' );
  
insert into filter_table values ( date '2020-01-01' );
commit;

exec dbms_mview.refresh ( 'summaries' );

select * from summaries;

CHR    MTH                  TOT    
B      01-JAN-2020 00:00         3 
A      01-JAN-2020 00:00         2

上一个：窗口框架上的 SQL 滞后CURRENT_ROW

下一个：ANSI SQL：通过选取列的某些特定值对结果进行排序