简单的删除查询.什么是最佳指数

Question

我们将使用以下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值，但这不是一个选项。

由于该表很大，每月大约 20M 条记录，每个月我都会删除一个月的旧数据，因此我想确保我可以快速找到要删除的记录。

使用此原始 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;

在上次更新时间添加索引后的查询计划(使用索引(

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  

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

添加了在同一索引中同时包含"上次更新"和"创建时间"列的新索引

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 分解为两个更新时，第一个按创建日期：

DELETE FROM COMMRCL_CORE_CLM_DTL
      WHERE UPDT_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  

对于第二个 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

在第两种情况下只使用单独的 DELETE 语句而不使用 NVL：

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的函数为 NULL 的 CRET_TIMESTMP，而 NULL 的 NULL(UPDT_TIMESTMP 的 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) 使用基于函数的索引(正如大卫最初提议的那样 - 对不起大卫，我还没有阅读您的评论(