sql - 简单的删除查询。最好的指标是什么？

Question

我们正在删除表中的大量行，如所述here使用以下 SQL:

 DELETE FROM MYTABLE
               WHERE     UPDT_TIMESTMP < v_Cut_Off_Date
                     AND ROWNUM <= C_MAX_DELETE;

我注意到 UPDT_TIMESTMP 可以为 NULL。此字段存储记录在初始创建后最后更新时间的 TIMESTAMP 值。因此，如果更新时间为 NULL，我希望修改我的 SQL 以考虑创建时间。

 DELETE FROM MYTABLE
               WHERE     NVL(UPDT_TIMESTMP, CRET_TIMESTMP) < v_Cut_Off_Date
                     AND ROWNUM <= C_MAX_DELETE;

我的偏好是禁止 NULL 并将 UPDT_TIMESTMP 列的值更新为 CRET_TIMESTMP 值，但这不是一个选项。

由于表会很大，每个月大约有 2000 万条记录，而且每个月我都会删除一个月的旧数据，所以我想确保能够快速找到要删除的记录。

使用此原始 SQL，

DELETE FROM COMMRCL_CORE_CLM_DTL
      WHERE UPDT_TIMESTMP < SYSDATE AND ROWNUM <= C_MAX_DELETE;

...没有索引，这是使用 Toad for Oracle 的查询计划:

Plan
DELETE STATEMENT  ALL_ROWSCost: 2  Bytes: 41  Cardinality: 1            
    3 DELETE FIN_IT_RPT.COMMRCL_CORE_CLM_DTL        
        2 COUNT STOPKEY     
            1 TABLE ACCESS FULL TABLE FIN_IT_RPT.COMMRCL_CORE_CLM_DTL Cost: 2  Bytes: 41  Cardinality: 1  

添加了上次更新时间的索引:

CREATE INDEX FIN_IT_RPT.COMMRCL_CORE_CLM_DTL_UPDTM ON FIN_IT_RPT.COMMRCL_CORE_CLM_DTL
(UPDT_TIMESTMP)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            MAXSIZE          UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

在 Last Update Time 添加索引后的查询计划(使用索引)

Plan
DELETE STATEMENT  ALL_ROWSCost: 0  Bytes: 41  Cardinality: 1            
    3 DELETE FIN_IT_RPT.COMMRCL_CORE_CLM_DTL        
        2 COUNT STOPKEY     
            1 INDEX RANGE SCAN INDEX FIN_IT_RPT.COMMRCL_CORE_CLM_DTL_UPDTM Cost: 0  Bytes: 41  Cardinality: 1  

修改查询以在更新时间为 NULL 时使用创建日期

DELETE FROM COMMRCL_CORE_CLM_DTL
      WHERE NVL(UPDT_TIMESTMP, CRET_TIMESTMP) < SYSDATE AND ROWNUM <= C_MAX_DELETE;

在创建时间上添加了单独的索引

CREATE INDEX FIN_IT_RPT.COMMRCL_CORE_CLM_DTL_CRET ON 
FIN_IT_RPT.COMMRCL_CORE_CLM_DTL
(CRET_TIMESTMP)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            MAXSIZE          UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

添加 2 个单独的索引后检查查询计划。

DELETE STATEMENT  ALL_ROWSCost: 2  Bytes: 54  Cardinality: 1            
    3 DELETE FIN_IT_RPT.COMMRCL_CORE_CLM_DTL        
        2 COUNT STOPKEY     
            1 TABLE ACCESS FULL TABLE FIN_IT_RPT.COMMRCL_CORE_CLM_DTL Cost: 2  Bytes: 54  Cardinality: 1  

问题:为什么两个索引都没有使用？

在同一索引中添加了一个包含 LAST UPDATE 和 CREATE TIME 列的新索引

CREATE INDEX FIN_IT_RPT.COMMRCL_CORE_CLM_DTL_UPDCRT ON FIN_IT_RPT.COMMRCL_CORE_CLM_DTL
(UPDT_TIMESTMP, CRET_TIMESTMP)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            MAXSIZE          UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

仍然没有使用索引。为什么？

Plan
DELETE STATEMENT  ALL_ROWSCost: 2  Bytes: 54  Cardinality: 1            
    3 DELETE FIN_IT_RPT.COMMRCL_CORE_CLM_DTL        
        2 COUNT STOPKEY     
            1 TABLE ACCESS FULL TABLE FIN_IT_RPT.COMMRCL_CORE_CLM_DTL Cost: 2  Bytes: 54  Cardinality: 1  

我意识到表中的数据不多会影响解释计划(我的数据很少。)我必须生成数百万行才能真正了解预期的结果，或者我可以不做就得到一个大概的想法那个？

为什么上面的示例中没有使用索引，或者我误解了计划？

更新:

当我采纳 Mat 的建议将 DELETE 分解为两个更新时，第一个按 CREATION 日期:

DELETE FROM COMMRCL_CORE_CLM_DTL
      WHERE UPDT_TIMESTMP  < SYSDATE AND ROWNUM <= variable;

...第一次使用 UPDATE DATE 上的索引

Plan
DELETE STATEMENT  ALL_ROWSCost: 0  Bytes: 54  Cardinality: 1            
    3 DELETE FIN_IT_RPT.COMMRCL_CORE_CLM_DTL        
        2 COUNT STOPKEY     
            1 INDEX RANGE SCAN INDEX FIN_IT_RPT.COMMRCL_CORE_CLM_DTL_UPDCRT Cost: 0  Bytes: 54  Cardinality: 1  

对于第二个 SQL...

DELETE FROM COMMRCL_CORE_CLM_DTL
      WHERE UPDT_TIMESTMP IS NULL AND  CRET_TIMESTMP < SYSDATE AND ROWNUM <= Variable;

使用具有两列的索引:

Plan

DELETE STATEMENT  ALL_ROWSCost: 0  Bytes: 54  Cardinality: 1            
    3 DELETE FIN_IT_RPT.COMMRCL_CORE_CLM_DTL        
        2 COUNT STOPKEY     
            1 INDEX RANGE SCAN INDEX FIN_IT_RPT.COMMRCL_CORE_CLM_DTL_UPDCRT Cost: 0  Bytes: 54  Cardinality: 1  

Answer 1

对于第二种情况，只需使用不带 NVL 的单独 DELETE 语句:

DELETE FROM MYTABLE
           WHERE     UPDT_TIMESTMP IS NULL AND CRET_TIMESTMP < v_Cut_Off_Date
                 AND ROWNUM <= C_MAX_DELETE;

您可以使用 ... WHERE UPDT_TIMESTMP < v_Cut_Off_Date OR (UPDT_TIMESTMP IS NULL AND CRET_TIMESTMP < v_Cut_Off_Date) ... 将两个语句合并为一个语句

如果您只有很少的 UPDT_TIMESTMP IS NULL 记录，使用 MY_NVL(UPDT_TIMESTMP,CRET_TIMESTMP) 创建一个基于函数的索引其中函数MY_NVL为 UPDT_TIMESTMP IS NULL 返回 CRET_TIMESTMP，为 UPDT_TIMESTMP IS NOT NULL 返回 NULL，然后 where 条件看起来像 ... WHERE UPDT_TIMESTMP < v_Cut_Off_Date OR MY_NVL(UPDT_TIMESTMP,CRET_TIMESTMP) < v_Cut_Off_Date ...

您还可以使用 NVL(UPDT_TIMESTMP, CRET_TIMESTMP) 尝试基于函数的索引(正如 David 最初提议的那样 - 对不起，David，我还没有读过你的评论)