-1
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.
MT0
  • 143,790
  • 11
  • 59
  • 117
LittleBitDeveloper
  • 17
  • 4
  • 1
    *Cost* only is IMO not the best starting point for tuning. Show the complete [execution plan](https://stackoverflow.com/a/34975420/4808122) and other information about your table (if it is a table and not a view) so that more focused answer can be provided; not a poor guessing. – Marmite Bomber Dec 09 '21 at 09:45
  • We don't know how selective your criteria is. `trunc(creation_date) < trunc(sysdate) - 2` can be optimized to `creation_date < trunc(sysdate) - 2`. And it seems that this will affect almost all rows in the table (all rows except the most current three days). How many days are usually in the table? Then, you only consider file IDs CBS1330 and CCI1330. How many different file IDs are in the table? At last, what percentage of the rows has a status PROCESSED? How many rows des the table have? How many rows do you expect the query to return? – Thorsten Kettner Dec 09 '21 at 09:53
  • 1
    The 'cost' is just an artificial number that the optimizer uses to compare the expected performance of several possible access plans for a given statement. If you query is not running as fast as you'd like, forget about looking at 'cost' and focus on EXPLAIN PLAN, and possibly sql traces to see where the time is going. And consider just how much it is even possible to squeeze out. How much effort is justified to reduce the time by 50% -- if the query is already runnning sub-second? – EdStevens Dec 09 '21 at 14:43

3 Answers3

1

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • after using above query - explain plan showing high cardinality and cost almost same but time will reduce by 2 sec. – LittleBitDeveloper Dec 09 '21 at 11:21
  • @W_O_L_F The SQL engine will almost certainly treat both equivalently; you can use `EXPLAIN PLAN` to check if there is any difference. – MT0 Dec 09 '21 at 13:49
0

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)
Slavian Petrov
  • 612
  • 4
  • 6
  • adding index for file_id, request_id, creation_date and status column has already index is created. After creating index has execute explain plan but cost will not reduce. – LittleBitDeveloper Dec 09 '21 at 11:33
0
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;
Farshid Shekari
  • 2,391
  • 4
  • 27
  • 47