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' ) )

• explain plan has showing cost = 37061
• I am tried to create index on column which is used in where clause but not working.

CodePudding user response：

You can:

• Use an analytic function instead of sub-queries.
• Remove the TRUNC from around TRUNC(creation_date) as if the truncated date is less than TRUNC(SYSDATE) - 2 then it will also always be true without truncating. This would let the optimizer use an index on the creation_date column (rather than requiring a function-based index on TRUNC(creation_date)).

Which would give you:

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)
       );

Then you can consider adding indexes on the columns you are using.

CodePudding user response：

You can try adding this index (if adding an index is an option in the first place):

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

CodePudding user response：

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;