database - SQL: My very short code times out; REGR_SLOPE is being super slow -

i'm pulling (read-only) information database has couple thousand rows in 2 different tables, 1 5 columns , 1 3 columns. here's code:

select distinct q1.machine_id, q1.signal_id, round(86400000*(regr_slope(ts.value, ts.epoch) on (partition ts.machine_signal_id))) rate, q1.last_value  tsd_sub ts,  ( select ms.machine_signal_id, ms.last_timestamp machine_signal ms   (ms.machine_id 'cv%' or ms.machine_id 'mt%')   , (ms.signal_id = any('wfrcount','wfrcntr') or ms.signal_id '%waferct%')) q1  ts.machine_signal_id = q1.machine_signal_id   , ts.epoch > (q1.last_timestamp - 604800000) 

now i'm not sure why should take long, code runs on fifteen minutes until times out. inner bit,

select * machine_signal       (machine_id 'cv%' or machine_id 'mt%')       , (signal_id = any('wfrcount','wfrcntr') or signal_id '%waferct%') 

runs quickly, pulling 5 columns , 3 hundred rows. full code using (relatively small) table along larger table 2 columns , few thousand rows.

this database oracle database, , running regr_slope works fine. additionally, if try run outer code little @ time, works fine:

select distinct round(86400000*(regr_slope(ts.value, ts.epoch) on (partition ts.machine_signal_id))) rate  tsd_sub ts  ts.machine_signal_id = '366625' -- taken machine_signal_id of first row of previous code   , ts.epoch > (1436855226000 - 604800000) -- last_timestamp of first row previous code 

runs in less 2 seconds , gives me one-cell result. want every entry in inner table, not 1 entry @ time. , that's not running @ all.

i know it's possibly taking long because i'm pulling many entries, i'm asking 2 things don't understand why it's not running @ all. it's it's going infinite loop, except don't see why that.

thank time , help!

---

edit:

the table machine_signal has variable machine_signal_id appears once in machine_signal, appears many times in tsd_sub. i'm trying calculate regr_slope of multiple rows in tsd_sub correspond 1 row in machine_signal. yeah, can't join tables, because tsd_sub has many rows each value of machine_signal_id in machine_signal.

---

the following takes far long run:

select machine_signal_id,         round(86400000*(regr_slope(ts.value, ts.epoch))) rate  tsd_sub ts,       (select distinct (max(ts0.epoch) - 604800000) yesterday       tsd_sub ts0       rownum < 100) ts1  ts.epoch > ts1.yesterday  group machine_signal_id 

this means joining machine_signal won't work @ all, unless can restrict machine_signal_id values in machine_signal, can't values without looking @ machine_signal first. feels catch-22 problem.

---

the following code work, hurray thank help.

select ms.machine_id, ms.signal_id, ms.machine_signal_id, ms.last_value, ts.epoch, ts.value  machine_signal ms join      (select ts.machine_signal_id, ts.epoch, ts.value tsd_sub ts,                      (select (max(ts0.epoch) - 604800000) yesterday                       tsd_sub ts0                       rownum < 1000) ts1                ts.epoch > ts1.yesterday) ts      on ts.machine_signal_id = ms.machine_signal_id  (ms.machine_id 'cv%' or ms.machine_id 'mt%') ,       (ms.signal_id in ('wfrcount', 'wfrcntr') or signal_id '%waferct%') 

now problem figuring out how regr_slope new table per each machine_signal_id rather getting entire table. possibly new problem, on that.

---

yeah reason though code above joining tables works, surrounding bit calculating regr_slope still timing out. below full code, parts work marked comments.

select mytable.machine_id,         mytable.signal_id,         round(86400000*regr_slope(mytable.value, mytable.epoch)) rate,         mytable.last_value  -- part works -- (select ms.machine_id, ms.signal_id, ms.machine_signal_id, ms.last_value, ts.epoch, ts.value  machine_signal ms join      (select ts.machine_signal_id, ts.epoch, ts.value tsd_sub ts,                      (select (max(ts0.epoch) - 604800000) yesterday                       tsd_sub ts0                       rownum < 1000) ts1                ts.epoch > ts1.yesterday) ts      on ts.machine_signal_id = ms.machine_signal_id  (ms.machine_id 'cv%' or ms.machine_id 'mt%') ,       (ms.signal_id in ('wfrcount', 'wfrcntr') or signal_id '%waferct%')) mytable -- here valid table runs in less second --  group mytable.machine_signal_id, mytable.machine_id, mytable.signal_id, mytable.last_value 

the things of substance here added regr_slope , group when regr_slope has partition inside of (as had in beginning) still times out. implies regr_slope function doing i'm not expecting that's making terribly slow. if it's going through every row, how make stop, , if it's not, why being slow?

thank you!

i don't know if help, perhaps writing query more sensibly optimizer. guessing equivalent query:

select ms.machine_id, ms.signal_id,        round(86400000*(regr_slope(ts.value, ts.epoch))) rate,        ms.last_value machine_signal join      tsd_sub ts      on ts.machine_signal_id = ms.machine_signal_id (ms.machine_id 'cv%' or ms.machine_id 'mt%') ,       (ms.signal_id in ('wfrcount', 'wfrcntr') or signal_id '%waferct%') ,       ts.epoch > (q1.last_timestamp - 604800000) group ms.machine_id, ms.signal_id, ms.last_value; 

this assumes signal_id identifying machine_id , last_value. replacing analytic function aggregation function.

your version of query runs regression every row produced join and then distinct. use group by.