How to reduce the number of multiple left join on the same two tables-CodePudding

Query is taking to long to execute

I am trying to reduce the number of left join used since I am joining the same two tables but it takes a lot of time to execute. I need a hint or a solution for this problem. Since I am comparing dates from both tables i think I cannot do a group by clause. Thank u in advance ------------------------------------------------------------------------

 select t1.comment
        from comments_aux t1
        where event_comment between  
to_date('20220205000000','yyyymmddhh24miss')  AND (to_date('20220202000000','yyyymmddhh24miss')    1)

CodePudding user response：

You can try using CASE statements.

select t1.c_instalation
         , t1.N_cpe
         , t1.EQuipment
         , t1.grupos
         , t1.D_EVENT_START
         , t1.D_EVENT_END
         , Z_EVENT_START
         , Z_EVENT_END
         , DURATION
         ,(CASE WHEN tz.z_ts_read < t1.Z_EVENT_START - (1/1440*30) AND tz.z_ts_read >= t1.Z_EVENT_START - (1/1440*45) THEN tz.m_read END) reading_45_before
         ,(CASE WHEN tz.z_ts_read < t1.Z_EVENT_START - (1/1440*15) AND tz.z_ts_read >= t1.Z_EVENT_START - (1/1440*30) THEN tz.m_read END) reading_30_before
         ,(CASE WHEN tz.z_ts_read < t1.Z_EVENT_START AND tz.z_ts_read >= t1.Z_EVENT_START - (1/1440*15) THEN tz.m_read END) reading_15_before
         ,(CASE WHEN tz.z_ts_read > t1.Z_EVENT_START AND tz.z_ts_read <= t1.Z_EVENT_START   (1/1440*15) THEN tz.m_read END) reading_15_after
         ,(CASE WHEN tz.z_ts_read > t1.Z_EVENT_START   (1/1440*15) AND tz.z_ts_read <= t1.Z_EVENT_START   (1/1440*30) THEN tz.m_read END) reading_30_after
         ,(CASE WHEN tz.z_ts_read > t1.Z_EVENT_START   (1/1440*30) AND tz.z_ts_read <= t1.Z_EVENT_START   (1/1440*45) THEN tz.m_read END) reading_45_after
         ,(CASE WHEN tz.z_ts_read > t1.Z_EVENT_START   (1/1440*45) AND tz.z_ts_read <= t1.Z_EVENT_START   (1/1440*60) THEN tz.m_read END) reading_60_after
         ,(CASE WHEN tz.z_ts_read > t1.Z_EVENT_START   (1/1440*60) AND tz.z_ts_read <= t1.Z_EVENT_START   (1/1440*75) THEN tz.m_read END) reading_75_after
         ,(CASE WHEN tz.z_ts_read > t1.Z_EVENT_START   (1/1440*75) AND tz.z_ts_read <= t1.Z_EVENT_START   (1/1440*90) THEN tz.m_read END) reading_90_after
         ,(CASE WHEN tz.z_ts_read > t1.Z_EVENT_START   (1/1440*90) AND tz.z_ts_read <= t1.Z_EVENT_START   (1/1440*105) THEN tz.m_read END) reading_105_after
       from pares_aux t1
       left join  DC tz
       on t1.n_cpe = tz.c_instalation
       and t1.n_equipment=tz.n_equipment
       where Z_EVENT_START between  
to_date('20201002000000','yyyymmddhh24miss')  AND (to_date('20201002000000','yyyymmddhh24miss')    1)