TIME_SLICE causing data re-segmentation during query execution

Sajan_Sangraula

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')

Sharon_Cutter

It's not so much that the RESEGMENT is slowing the query down, but rather that the on-the-fly computation of the time slice forces a GROUP BY HASH instead of the optimized GROUP BY PIPELINE. If you have a small number of typical time slices that you use, you could precompute those values and then create additional projections to optimize them. If these are adhoc time slice selections that can't be backed by an optimized projection then you are going to get GROUP BY HASH regardless. --Sharon

Sajan_Sangraula

Thanks Sharon. I looked at the execution engine profile and the group by hash indeed seems to be the time consuming block. I do have a small number of time_slices, around 5. When you say precompute those values and create additional projections, do you mean create projection on the table with the time_slice as part of the segmentation clause? I couldn't think of any other way to precompute as Time_slice function, or any other function for that matter, is not permitted in the Select or the Order by clause. Thanks Sajan

Sharon_Cutter

Hi Sajan, I mean create additional columns in the table with the precomputed time slice values - compute the values during the load process. Then create projections with the GROUP BY columns (each including one of the precomputed time slices) first and also the threshold_level and alarm_level values, but no need for the other columns in the table if there are other columns. --Sharon