Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
TIME_SLICE causing data re-segmentation during query execution
I am using the TIME_SLICE function to roll up data to a wider time interval than present in the table. However, the query gets very slow when I use TIME_SLICE. Upon analyzing the EXPLAIN plan, data is being resegmented as part of the query execution when using TIME_SLICE, but the same query executes without a RESEGMENT if the TIME_SLICE is not used. This table has a segmented projection with segmentation on monitor_id, threshold_id,managed_object_id and execution time( same as the group by clause). The two explain plans, with and without the time_slice, are attached. Here's the query. Is there a way to avoid data resegmentation and get better performance while using TIME_SLICE? SELECT m.monitor_id , m.threshold_id , managed_object_id , 900 AS execution_interval , time_slice(to_timestamp(execution_time) ,900 ,'SECOND' ,'END') AS execution_time , MAX(threshold_level) AS threshold_level , MAX(alarm_level) AS alarm_level FROM monitor_data m GROUP BY m.monitor_id , m.threshold_id , managed_object_id , time_slice(to_timestamp(execution_time) ,900 ,'SECOND' ,'END')