SQL Query simplification (EXPLAIN COST 58)

Question

Can someone help in simplifying below query. Cost of it shows as 58.

b.dueDate and b.tID are composite key. a.tID is primary key and foreign key between table 1 and 2.

SELECT test.tID, test.sor_acct_id, test.pmt, test.status FROM   ct.tab1 a, 
   (SELECT a.tID, a.sor_acct_id, b.dueDate, b.amt, b.status, a.pmt, 
           Row_number() OVER ( partition BY a.tID ORDER BY b.dueDate DESC) AS rn 
    FROM   ct.tab1 a 
           INNER JOIN ct.tab2 b 
                   ON a.tID = b.tID 
    WHERE  a.tID IN (SELECT a.tID 
                              FROM   ct.tab1 a 
                                     INNER JOIN 
                                     ct.tab2 b 
                                             ON a.tID = 
                                                b.tID 
                              WHERE  a.status = 'E' 
                                     AND a.pmt IS NOT NULL 
                                     AND a.pmt <> '{}' 
                                     AND b.dueDate > CURRENT_DATE - 4 
                                     AND b.dueDate < CURRENT_DATE) 
           AND b.dueDate > CURRENT_DATE - 1 
   ) test WHERE  rn = 1 
   AND test.status IN ( 'X', 'Z' ) 
   AND a.tID = test.tID

Why aren't the condition in the `IN (SELECT ...)`'s WHERE just part of the WHERE the IN is a part of? And why mix implicit and explicit join notation? — Uueerdo, Jun 01 '18 at 18:02
_Bug:_ `CURRENT_DATE - 4` is nonsense. Change to `CURRENT_DATE - INTERVAL 4 DAY`. See for yourself by running `SELECT CURRENT_DATE, CURRENT_DATE - 4;`. — Rick James, Jun 02 '18 at 03:11
What indexes do you have? (Please provide `SHOW CREATE TABLE`.) — Rick James, Jun 02 '18 at 03:13

score 0 · Answer 1 · answered Jun 01 '18 at 18:01

0

Maybe you would change:

WHERE  a.tID IN (SELECT a.tID ....

into:

join ((SELECT a.tID FROM   ct.tab1 a ....) t on t.tID=a.tID

answered Jun 01 '18 at 18:01

sorineatza

106
7

score 0 · Answer 2 · answered Jun 01 '18 at 20:02

tID is tab1's primary key. So when you say you are looking for tab1 records the tID of which is found in a set of tab1 records with status E, you could just as well simply say: I'm looking for tab1 records with status E.

What your query does is: Show all tab1 records with their final tab2 status provided ...

the tab1 pmt is not null and not '{}'
the tab1 status is E
the final tab2 status is X or Z
the final tab2 status is due today or in the future
there exists at least one tab2 record due in the last three days for the tab1 record

The query:

SELECT
  t1.tID,
  t1.sor_acct_id,
  t1.pmt,
  t2.status
FROM ct.tab1 t1
join
(
  SELECT
    tID,
    status,
    ROW_NUMBER() OVER (PARTITION BY tID ORDER BY dueDate DESC) AS rn
  FROM ct.tab2
  WHERE dueDate > CURRENT_DATE - 1
) t2 ON  t2.tID = tab1.tID AND t2.rn = 1 AND t2.status IN ('X', 'Z') 
WHERE t1.status = 'E'
  AND t1.pmt IS NOT NULL
  AND t1.pmt <> '{}'
  and t1.tID IN
  (
    SELECT tID
    FROM ct.tab2
    WHERE dueDate > CURRENT_DATE - 4
      AND dueDate < CURRENT_DATE
  );

SQL Query simplification (EXPLAIN COST 58)

2 Answers2