StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There is a problem with Richard's model clause query. It is doing 1000 iterations without an UNTIL clause. After four iterations the end result is achieved already. The next 996 iterations consume CPU power, but do nothing.</p> <p>Here you can see that the query is done processing after 4 iterations with the current data set:</p> <pre><code>SQL> select order_id 2 , volume 3 , price 4 , total_vol 5 , total_costs 6 , unit_costs 7 from ( select order_id 8 , volume 9 , price 10 , volume total_vol 11 , 0.0 total_costs 12 , 0.0 unit_costs 13 , row_number() over (order by order_id) rn 14 from costs 15 order by order_id 16 ) 17 model 18 dimension by (order_id) 19 measures (volume, price, total_vol, total_costs, unit_costs) 20 rules iterate (4) 21 ( total_vol[any] = volume[cv()] + nvl(total_vol[cv()-1],0.0) 22 , total_costs[any] 23 = case SIGN(volume[cv()]) 24 when -1 then total_vol[cv()] * nvl(unit_costs[cv()-1],0.0) 25 else volume[cv()] * price[cv()] + nvl(total_costs[cv()-1],0.0) 26 end 27 , unit_costs[any] = total_costs[cv()] / total_vol[cv()] 28 ) 29 order by order_id 30 / ORDER_ID VOLUME PRICE TOTAL_VOL TOTAL_COSTS UNIT_COSTS ---------- ---------- ---------- ---------- ----------- ---------- 1 1000 100 1000 100000 100 2 -500 110 500 50000 100 3 1500 80 2000 170000 85 4 -100 150 1900 161500 85 5 -600 110 1300 110500 85 6 700 105 2000 184000 92 6 rows selected. </code></pre> <p>It needs 4 iterations and not 6, because automatic order is used, and each iteration tries to adjust all 6 rows.</p> <p>You are far more performant if you use just as many iterations as there are rows and each iteration adjusts just one row. You can also skip the subquery and then the final query becomes:</p> <pre><code>SQL> select order_id 2 , volume 3 , price 4 , total_vol 5 , total_costs 6 , unit_costs 7 from costs 8 model 9 dimension by (row_number() over (order by order_id) rn) 10 measures (order_id, volume, price, type, 0 total_vol, 0 total_costs, 0 unit_costs) 11 rules iterate (1000) until (order_id[iteration_number+2] is null) 12 ( total_vol[iteration_number+1] 13 = nvl(total_vol[iteration_number],0) + volume[iteration_number+1] 14 , total_costs[iteration_number+1] 15 = case type[iteration_number+1] 16 when 'B' then volume[iteration_number+1] * price[iteration_number+1] + nvl(total_costs[iteration_number],0) 17 when 'S' then total_vol[iteration_number+1] * nvl(unit_costs[iteration_number],0) 18 end 19 , unit_costs[iteration_number+1] 20 = total_costs[iteration_number+1] / total_vol[iteration_number+1] 21 ) 22 order by order_id 23 / ORDER_ID VOLUME PRICE TOTAL_VOL TOTAL_COSTS UNIT_COSTS ---------- ---------- ---------- ---------- ----------- ---------- 1 1000 100 1000 100000 100 2 -500 110 500 50000 100 3 1500 80 2000 170000 85 4 -100 150 1900 161500 85 5 -600 110 1300 110500 85 6 700 105 2000 184000 92 6 rows selected. </code></pre> <p>Hope this helps.</p> <p>Regards,<br> Rob.</p> <p>EDIT Some proof to backup my claim:</p> <pre><code>SQL> create procedure p1 (p_number_of_iterations in number) 2 is 3 begin 4 for x in 1 .. p_number_of_iterations 5 loop 6 for r in 7 ( select order_id 8 , volume 9 , price 10 , total_vol 11 , total_costs 12 , unit_costs 13 from ( select order_id 14 , volume 15 , price 16 , volume total_vol 17 , 0.0 total_costs 18 , 0.0 unit_costs 19 , row_number() over (order by order_id) rn 20 from costs 21 order by order_id 22 ) 23 model 24 dimension by (order_id) 25 measures (volume, price, total_vol, total_costs, unit_costs) 26 rules iterate (4) 27 ( total_vol[any] = volume[cv()] + nvl(total_vol[cv()-1],0.0) 28 , total_costs[any] 29 = case SIGN(volume[cv()]) 30 when -1 then total_vol[cv()] * nvl(unit_costs[cv()-1],0.0) 31 else volume[cv()] * price[cv()] + nvl(total_costs[cv()-1],0.0) 32 end 33 , unit_costs[any] = total_costs[cv()] / total_vol[cv()] 34 ) 35 order by order_id 36 ) 37 loop 38 null; 39 end loop; 40 end loop; 41 end p1; 42 / Procedure created. SQL> create procedure p2 (p_number_of_iterations in number) 2 is 3 begin 4 for x in 1 .. p_number_of_iterations 5 loop 6 for r in 7 ( select order_id 8 , volume 9 , price 10 , total_vol 11 , total_costs 12 , unit_costs 13 from costs 14 model 15 dimension by (row_number() over (order by order_id) rn) 16 measures (order_id, volume, price, type, 0 total_vol, 0 total_costs, 0 unit_costs) 17 rules iterate (1000) until (order_id[iteration_number+2] is null) 18 ( total_vol[iteration_number+1] 19 = nvl(total_vol[iteration_number],0) + volume[iteration_number+1] 20 , total_costs[iteration_number+1] 21 = case type[iteration_number+1] 22 when 'B' then volume[iteration_number+1] * price[iteration_number+1] + nvl(total_costs[iteration_number],0) 23 when 'S' then total_vol[iteration_number+1] * nvl(unit_costs[iteration_number],0) 24 end 25 , unit_costs[iteration_number+1] 26 = total_costs[iteration_number+1] / total_vol[iteration_number+1] 27 ) 28 order by order_id 29 ) 30 loop 31 null; 32 end loop; 33 end loop; 34 end p2; 35 / Procedure created. SQL> set timing on SQL> exec p1(1000) PL/SQL procedure successfully completed. Elapsed: 00:00:01.32 SQL> exec p2(1000) PL/SQL procedure successfully completed. Elapsed: 00:00:00.45 SQL> exec p1(1000) PL/SQL procedure successfully completed. Elapsed: 00:00:01.28 SQL> exec p2(1000) PL/SQL procedure successfully completed. Elapsed: 00:00:00.43 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload