CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Gangs165700
Gangs165700

Reputation: 131

Oracle group by and OR query optimization

SELECT BATCH_ID,
  BU_CODE,
  BU_TYPE,
  BATCH_TYPE,
  BATCH_GROUP_ID,
  STATUS,
  UPD_DTIME
FROM BATCH_T
WHERE (BU_CODE, BU_TYPE, BATCH_TYPE, UPD_DTIME) IN
  (SELECT BU_CODE,
    BU_TYPE,
    BATCH_TYPE,
    MAX(UPD_DTIME)
  FROM BATCH_T
  WHERE STATUS = 'CLOSED'
  GROUP BY BU_CODE,
    BU_TYPE,
    BATCH_TYPE
  )
OR UPD_DTIME >= SYSDATE - 5;

As in above Oracle Query with out OR condition its giving quick response, But with OR condition its taking so much time. Can you please help how to improve the response time of above query.

Upvotes: 0

Views: 137

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You want the last closed record and everything from the last five days. Use window functions!

SELECT b.*
FROM (SELECT b.*,
             ROW_NUMBER() OVER (PARTITION BY BU_CODE, BU_TYPE, BATCH_TYPE, STATUS ORDER BY UPD_DTIME DESC) as seqnum
      FROM BATCH_T b
     ) b
WHERE UPD_DTIME >= SYSDATE - 5 OR
      (STATUS = 'CLOSED' AND seqnum = 1);

This should be faster than any method using IN/EXISTS and aggregation. It is also simpler to write.

Upvotes: 2

hoangnh
hoangnh

Reputation: 249

You can Create index for UPD_DTIME

Upvotes: -1

Rams
Rams

Reputation: 2169

Split the query in such a way that, there should be AND condition in it to get the performance gain as like below.

 SELECT BATCH_ID,
  BU_CODE,
  BU_TYPE,
  BATCH_TYPE,
  BATCH_GROUP_ID,
  STATUS,
  UPD_DTIME
FROM BATCH_T
WHERE UPD_DTIME >= SYSDATE - 5

UNION ALL

SELECT BATCH_ID,
  BU_CODE,
  BU_TYPE,
  BATCH_TYPE,
  BATCH_GROUP_ID,
  STATUS,
  UPD_DTIME
FROM BATCH_T
WHERE (BU_CODE, BU_TYPE, BATCH_TYPE, UPD_DTIME) IN
  (SELECT BU_CODE,
    BU_TYPE,
    BATCH_TYPE,
    MAX(UPD_DTIME)
  FROM BATCH_T
  WHERE STATUS = 'CLOSED'
  GROUP BY BU_CODE,
    BU_TYPE,
    BATCH_TYPE
  ) AND NOT UPD_DTIME >= SYSDATE - 5;

Upvotes: 1

Related Questions