Aggregation query takes more than hour Oracle-CodePudding

SELECT
  subs_key,
  sum(ROUNDED_DATA_VOLUME) AS RDV_SUM, 
  CASE WHEN to_char(CALL_START_TIME , 'HH24:MI:SS') >= '00:00:00'
        AND to_char(CALL_START_TIME , 'HH24:MI:SS') <= '07:00:00' THEN 'Night'
       WHEN to_char(CALL_START_TIME , 'HH24:MI:SS') >  '07:00:00'
        AND to_char(CALL_START_TIME , 'HH24:MI:SS') <= '23:59:59' THEN 'Day' 
  END AS Tariff_flag
FROM DWH.FCT_USAGE_PREP_OGPRS_N
WHERE CALL_START_TIME >= to_date('2021-11-01', 'YYYY-MM-DD') 
  AND CALL_START_TIME <= to_date('2021-11-30', 'YYYY-MM-DD')
GROUP BY
  SUBS_KEY,
  CASE WHEN (to_char(CALL_START_TIME , 'HH24:MI:SS') >= '00:00:00'
    AND to_char(CALL_START_TIME, 'HH24:MI:SS') <= '07:00:00') THEN 'Night'
       WHEN (to_char(CALL_START_TIME , 'HH24:MI:SS') > '07:00:00'
    AND to_char(CALL_START_TIME, 'HH24:MI:SS') <= '23:59:59') THEN 'Day' 
  END

My query takes more than hour and still running. Is there any way to optimize it?

UPD:

Execution Plan

Is that what Ankit asked?

CodePudding user response：

You are grouping by a relatively complex function:

CASE WHEN (to_char(CALL_START_TIME , 'HH24:MI:SS') >= '00:00:00' AND to_char(CALL_START_TIME , 'HH24:MI:SS') <= '07:00:00') THEN 'Night'
WHEN (to_char(CALL_START_TIME , 'HH24:MI:SS') > '07:00:00' AND to_char(CALL_START_TIME , 'HH24:MI:SS') <= '23:59:59') THEN 'Day' END

If this particular query is important enough you could index this whole thing, but I suspect you would be better off getting the hour using extract.

CASE WHEN extract(hour from CALL_START_TIME) > 7 then 'Night' --midnight (inclusive) - 7am (exclusive)
     else 'Day' --7am (inclusive) - midnight (exclusive)
END

For your where clause I would I have no way to tell whether this will speed up your query. Or speed it up sufficiently for your use case. But you can try out the two queries and see if one of them is significantly faster than the other. Also, if you're frequently using times, it might make sense to index extract(hour from CALL_START_TIME), whereas an index on your entire case statement is only likely to get used in this one query.

Your question is how to speed up the query but when I have a query that takes hours to run I will often not bother to optimize it. It's long enough that you're unlikely to get the kind of speed-ups you need to have an application executing the query based on a user's request, and if a query takes 1 hour or 12, you likely need to plan when you run it.