如何降低以下查询的成本

Question

SELECT
    *
FROM
    xxcpb_i45_06_interface_r
WHERE
    trunc(creation_date) < trunc(sysdate) - 2
    AND ( ( request_id < (
        SELECT
            MAX(xcs_sub.request_id)
        FROM
            xxcpb_i45_06_interface_r xcs_sub
        WHERE
            xcs_sub.status = 'PROCESSED'
            AND xcs_sub.file_id = 'CBS1330'
    )
            AND file_id = 'CBS1330' )
          OR ( request_id < (
        SELECT
            MAX(xcs_sub.request_id)
        FROM
            xxcpb_i45_06_interface_r xcs_sub
        WHERE
            xcs_sub.status = 'PROCESSED'
            AND xcs_sub.file_id = 'CCI1330'
    )
               AND file_id = 'CCI1330' ) )

• 解释计划显示了成本=37061
• ，我试图在where子句中使用但不起作用的列上创建索引。

Answer 1

您可以：

sub-queries.

• Remove
• 使用一个解析函数，而不是TRUNC(creation_date)周围的TRUNC，就好像截断的日期小于TRUNC(SYSDATE) - 2一样，它在不截断的情况下也始终是真的。这将使优化器在TRUNC(creation_date)).

列上使用索引(而不是要求基于函数的索引)。

这会让你：

SELECT *
FROM   (
  SELECT x.*,
         MAX(
           CASE
           WHEN status = 'PROCESSED'
           AND  file_id = 'CBS1330'
           THEN request_id
           END
         ) OVER () AS max_processed_cbs,
         MAX(
           CASE
           WHEN status = 'PROCESSED'
           AND  file_id = 'CCI1330'
           THEN request_id
           END
         ) OVER () AS max_processed_cci
  FROM   xxcpb_i45_06_interface_r x
  WHERE  file_id IN ('CBS1330', 'CCI1330')
)
WHERE  creation_date < TRUNC(SYSDATE) - 2
AND    (  (file_id = 'CBS1330' AND request_id < max_processed_cbs)
       OR (file_id = 'CCI1330' AND request_id < max_processed_cci)
       );

然后，可以考虑在所使用的列上添加索引。

Answer 2

您可以尝试添加此索引(如果添加索引首先是一个选项)：

create index test_index on xxcpb_i45_06_interface_r(file_id, request_id, status)

Answer 3

SELECT
        tr.* 
FROM
    xxcpb_i45_06_interface_r tr
join (
        SELECT
            xcs_sub.file_id, MAX(xcs_sub.request_id) request_id 
        FROM
            xxcpb_i45_06_interface_r xcs_sub
        WHERE
              xcs_sub.status = 'PROCESSED'
          AND xcs_sub.file_id in ('CCI1330', 'CBS1330')
        group by xcs_sub.file_id

    )t on t.request_id > tr.request_id 
      and tr.file_id =  t.file_id
where 
      creation_date < trunc(sysdate) - 2;