sql - 尽 pipe 查询中出现 ORA-904，UPDATE 工作正常(但非常非常慢)

Question

我有一个 UPDATE 语句，其中包含 WHERE 中的子查询来查找重复项。子查询存在错误，这些错误在运行子查询本身时会显示出来，但在 UPDATE 语句中运行时不会显示错误，并且 DML 运行正常(但非常慢)。

查看表格设置:

CREATE TABLE RAW_table
(
  ERROR_LEVEL      NUMBER(3),
  RAW_DATA_ROW_ID  INTEGER,
  ATTRIBUTE_1      VARCHAR2(4000 BYTE)
)
;

INSERT INTO RAW_table VALUES (0,    2,  '509NTQD9Q868');
INSERT INTO RAW_table VALUES (0,    2,  '509NTQD9Q868');
INSERT INTO RAW_table VALUES (0,    2,  '509NTQD9Q868');
INSERT INTO RAW_table VALUES (0,    3,  '509NTVS9Q863');
INSERT INTO RAW_table VALUES (0,    3,  '509NTVS9Q863');
INSERT INTO RAW_table VALUES (0,    3,  '509NTVS9Q863');

COMMIT;

有错误的查询是:

SELECT UPPER(ATTRIBUTE_1), rid
  FROM ( SELECT UPPER(ATTRIBUTE_1)
              , ROWID AS rid
              , ROW_NUMBER() OVER ( PARTITION BY UPPER (ATTRIBUTE_1) ORDER BY RAW_DATA_ROW_ID) AS RN
           FROM RAW_table
       )
 WHERE RN > 1;

运行时给出ORA-00904:“ATTRIBUTE_1”:无效标识符。

但是，在 WHERE 语句中使用上述查询(截至第 4 行)的以下 DML 工作正常:

set timing on

UPDATE RAW_table
   SET ERROR_LEVEL   = 4
 WHERE (UPPER (ATTRIBUTE_1), ROWID) 
       IN (SELECT UPPER (ATTRIBUTE_1), rid
           FROM (SELECT UPPER (ATTRIBUTE_1), ROWID AS rid
                     , ROW_NUMBER() OVER ( PARTITION BY UPPER (ATTRIBUTE_1) ORDER BY RAW_DATA_ROW_ID) AS RN
                  FROM RAW_table
                )
           WHERE RN > 1
          )
;

4 rows updated.
Elapsed: 00:00:00.36

为什么？为什么？为什么？

我预计 UPDATE 也会失败，并出现 ORA-00904: "ATTRIBUTE_1": invalididentifier。为什么它没有失败？

然而，真正的问题不是更新实际上有效，而是它运行得非常慢。

当我将子查询更正为不触发 ORA-00904: "ATTRIBUTE_1": invalididentifier 时，如下所示:

UPDATE RAW_table
   SET ERROR_LEVEL   = 4
 WHERE (UPPER (ATTRIBUTE_1), ROWID) 
        IN (SELECT checked_column, rid
           FROM (SELECT UPPER (ATTRIBUTE_1) AS checked_column, ROWID AS rid
                     , ROW_NUMBER() OVER ( PARTITION BY UPPER (ATTRIBUTE_1) ORDER BY RAW_DATA_ROW_ID) AS RN
                  FROM RAW_table
                )
           WHERE RN > 1
          )
;

在 11.000 行的测试数据集上，查询加速了近 400 倍:

SELECT COUNT(*) FROM RAW_table;

  COUNT(*)
----------
     11004
1 row selected.

更正的查询:

1005 rows updated.
Elapsed: 00:00:00.28

使用 ORA-904 查询:

1005 rows updated.
Elapsed: 00:01:48.40

我没有足够的耐心等到 71.000 行测试结束:

SELECT COUNT(*) FROM RAW_table;
  COUNT(*)
----------
     71475
1 row selected.

Corrected query
11004 rows updated.
Elapsed: 00:00:00.60

Query with ORA-904

30 分钟后取消...

解释使用 ORA-904 的查询计划:

UPDATE STATEMENT  ALL_ROWS     Cost: **2 544 985 615**  Bytes: 8 464 752  Cardinality: 4 176  
     7 UPDATE RAW_TABLE 
          6 FILTER  
               1 TABLE ACCESS FULL TABLE RAW_TABLE Cost: 54  Bytes: 169 282 878  Cardinality: 83 514  
               5 VIEW  Cost: 30 486  Bytes: 2 087 850  Cardinality: 83 514  
                    4 WINDOW SORT  Cost: 30 486  Bytes: 169 282 878  Cardinality: 83 514  
                         3 FILTER  
                              2 TABLE ACCESS FULL TABLE RAW_TABLE Cost: 54  Bytes: 169 282 878  Cardinality: 83 514  

解释更正查询的计划:

UPDATE STATEMENT  ALL_ROWS     Cost: **36 637**  Bytes: 3 374 235  Cardinality: 835  
     7 UPDATE RAW_TABLE 
          6 HASH JOIN RIGHT SEMI  Cost: 36 637  Bytes: 3 374 235  Cardinality: 835  
               4 VIEW VIEW SYS.VW_NSO_1 Cost: 30 486  Bytes: 168 197 196  Cardinality: 83 514  
                    3 VIEW  Cost: 30 486  Bytes: 169 282 878  Cardinality: 83 514  
                         2 WINDOW SORT  Cost: 30 486  Bytes: 169 282 878  Cardinality: 83 514  
                              1 TABLE ACCESS FULL TABLE RAW_TABLE Cost: 54  Bytes: 169 282 878  Cardinality: 83 514  
               5 TABLE ACCESS FULL TABLE RAW_TABLE Cost: 54  Bytes: 169 282 878  Cardinality: 83 514  

分析该表后，计划的成本是相同的。使用 ORA-904 解释查询计划:

UPDATE STATEMENT  ALL_ROWS     Cost: **29 381 690**  Bytes: 38  Cardinality: 2
     7 UPDATE RAW_TABLE
          6 FILTER
               1 TABLE ACCESS FULL TABLE RAW_TABLE Cost: 54  Bytes: 1 358 025  Cardinality: 71 475
               5 VIEW  Cost: 427  Bytes: 1 786 875  Cardinality: 71 475
                    4 WINDOW SORT  Cost: 427  Bytes: 1 358 025  Cardinality: 71 475
                         3 FILTER
                              2 TABLE ACCESS FULL TABLE RAW_TABLE Cost: 54  Bytes: 1 358 025  Cardinality: 71 475

解释更正查询的计划:

UPDATE STATEMENT  ALL_ROWS     Cost: **3 123**  Bytes: 1 453 595  Cardinality: 715
     7 UPDATE RAW_TABLE
          6 HASH JOIN SEMI  Cost: 3 123  Bytes: 1 453 595  Cardinality: 715
               5 VIEW VIEW SYS.VW_NSO_1 Cost: 427  Bytes: 143 950 650  Cardinality: 71 475
                    4 VIEW  Cost: 427  Bytes: 144 879 825  Cardinality: 71 475
                         3 WINDOW SORT  Cost: 427  Bytes: 1 358 025  Cardinality: 71 475
                              2 TABLE ACCESS FULL TABLE RAW_TABLE Cost: 54  Bytes: 1 358 025  Cardinality: 71 475
               1 TABLE ACCESS FULL TABLE RAW_TABLE Cost: 54  Bytes: 1 358 025  Cardinality: 71 475

解释一下计划成本说明了一切，但为什么差异如此之大？

在计算表上的统计信息后，我刚刚再次触发了 71.000 行测试，但它已经运行了几分钟......

这一切都在 Oracle Database 12c 企业版版本 12.1.0.2.0 - 64 位上。

Answer 1

您的 SELECT 失败，因为子查询中没有名为 ATTRIBUTE_1 的列。您需要指定名称:

SELECT UPPER(ATTRIBUTE_1), rid
  FROM ( SELECT UPPER(ATTRIBUTE_1) as ATTRIBUTE_1, 
                ROWID AS rid,
                ROW_NUMBER() OVER (PARTITION BY UPPER(ATTRIBUTE_1) ORDER BY RAW_DATA_ROW_ID) AS RN
         FROM RAW_table
       )
 WHERE RN > 1;

UPDATE 不会生成错误，因为它从外部查询中提取值:

UPDATE RAW_table
-------^
|   SET ERROR_LEVEL   = 4
| WHERE (UPPER (ATTRIBUTE_1), ROWID) IN 
|         (SELECT checked_column, rid
|          FROM (SELECT UPPER(ATTRIBUTE_1) AS checked_column, ROWID AS rid,
------------------------------^  This is interpreted as RAW_table.ATTRIBUTE_1
                        ROW_NUMBER() OVER (PARTITION BY UPPER(ATTRIBUTE_1) ORDER BY RAW_DATA_ROW_ID) AS RN
                 FROM RAW_table
                )
           WHERE RN > 1
          )

这种关联可能不是您想要的，也是我建议列名始终经过限定的原因之一(即包含表别名)。