create or replace package sqlru_workers authid definer
is
 
-- booby 22.12.2021

 Type T_Worker_Spc Is Record( speed number -- время обработки одной коробки в минутах
                            , capacity number -- число коробок обрабатываемых без перерыва
                            , delay number -- перерыв в минутах после обработки capacity коробок
                            );

 --------------------                               
 TYPE T_WRK_CURSOR IS REF CURSOR RETURN T_Worker_Spc;
 -----
/*
  вернет отрицательный результат, если для заказанного положительного объема pN 
  не найдется работников в курсоре pcrs вообще
  пример вызова:
select sqlru_workers.get_min_wrk_time(pcrs => CURSOR(
                                                      select speed, capacity, delay 
                                                      From 
                                                      (select 1 as speed, 2 as capacity, 3 as delay from dual
                                                      union all select 3, 10, 3 from dual
                                                      union all select 2, 4, 3 from dual) w
                                                       )
                         , pN => 10        -- число коробок
                          )
from dual 
*/
 function get_min_wrk_time(pcrs in T_WRK_CURSOR -- курсор с работничками
                         , pN in Number        -- число коробок
                          ) Return Number; 

end sqlru_workers;
/
create or replace package body sqlru_workers is

 TYPE T_WORKERS_PLDICT is table of T_Worker_Spc index by pls_integer; 

 Type T_Worker_State Is Record( inum pls_integer -- номер работника в списке работников
                              , current_capacity Number -- текущее число обработанных коробок работником
                              , next_avalible Number -- следующее время доступности работника
                              );
 TYPE T_wrk_states is table of T_Worker_State Index by pls_integer;
 
 -- вернет отрицательный результат, если для заказанного положительного объема pN 
 -- не найдется работников в курсоре pcrs вообще
 function get_min_wrk_time(pcrs in T_WRK_CURSOR -- курсор с работничками
                         , pN in Number        -- число коробок
                          ) Return Number
 Is
   nstop_time Number := 0.0;
   N Number := Coalesce(pN, 0.0);
   a_workers T_WORKERS_PLDICT; -- работнички
   a_wrkstate T_wrk_states;     -- их рабочее состояние
   --
   ncur_time Number := 0.0; -- текущее время
   r_worker T_Worker_State;

  ------------------------------------------------------------------------------
  -- процедуры работы с очередью занятых работников
  -------------------------------------------
  Function Is_Empty(pa_wrk_states in T_wrk_states) return Boolean
    is
  Begin
    Return pa_wrk_states.COUNT = 0;
  End;
  -- получение индекса родителя
  function get_parent_idx(p_idx_in in Pls_integer) return Pls_integer Deterministic
    is
  begin
    return Floor(0.5*(p_idx_in));
  end;  
  -- копирование элемента 
  Procedure Copy_ItemTo(pa_array in out nocopy T_wrk_states
                      , pi_from in Pls_integer -- индекс с которого копируется элемент
                      , pi_to in Pls_Integer   -- индекс в который копируется элемент
                      )
  is
  Begin
    pa_array(pi_to) := pa_array(pi_from);
  End;   
  -- сравнение элементов по значению
  Function Less(pr_parent in T_Worker_State
              , pr_child in T_Worker_State
               ) Return Boolean
  Is
  Begin          
    Return Case 
             When  pr_child.next_avalible < pr_parent.next_avalible 
               Then True
             When pr_child.next_avalible = pr_parent.next_avalible       
               Then (a_workers(pr_child.inum).speed < a_workers(pr_parent.inum).speed)
           Else False
           End;
  End;  
  -- всплытие с указанной позиции
  Procedure Swim( pa_array in out nocopy T_wrk_states
                , pi_from in Pls_integer
                )
  is
    i_flow Pls_integer;
    i_parent Pls_integer;
    r_temp T_Worker_State;
  Begin
    i_flow := pi_from;
    i_parent :=  get_parent_idx(i_flow);
    r_temp := pa_array(pi_from);    
    
    While i_flow > 1
        And Less( pr_parent => pa_array(i_parent)
               ,  pr_child => r_temp 
                )   
    Loop
      pa_array(i_flow) := pa_array(i_parent); 
      i_flow := i_parent;
      i_parent := get_parent_idx(i_flow);
    End Loop;
    
    If i_flow != pi_from
      Then
      pa_array(i_flow) := r_temp; 
    End if;      
    
  End;    
  -- погружение с указанной позиции  
  Procedure Sink(pa_array in out nocopy T_wrk_states
                , pi_from in Pls_integer
                 )
  Is
    i_flow Pls_integer;
    i_child Pls_integer;
    i_lastPos Pls_integer;

    r_temp T_Worker_State;
  Begin
    i_lastPos := pa_array.COUNT;
    i_flow := pi_from;   
    If i_flow <= i_lastPos
      Then
      r_temp := pa_array(pi_from);
      While (i_flow + i_flow ) <= i_lastPos
      Loop
        i_child := (i_flow + i_flow); 

        If i_child < i_lastPos
            And Less( pr_parent => pa_array(i_child)
                    , pr_child =>  pa_array(i_child+1)
                    )
          Then
            i_Child := i_child + 1;
        End If;
        
        Exit When Not Less( pr_parent => r_temp
                         ,  pr_child => pa_array(i_Child)
                          ); 
                          
        Copy_ItemTo(pa_array => pa_array, pi_from => i_child, pi_to => i_flow);
        i_flow := i_child;

      End Loop;

      If i_flow != pi_from
        Then
        pa_array(i_flow) := r_temp; 
      End If;   
    End If; 
    --
  End;
  --------------------------------------------------  
  -- удаление и возврат максимального элемента
  Procedure delMax(pa_array in out nocopy T_wrk_states
                 , pr_Key_out OUT NOCOPY T_Worker_State
                   )
  Is
    i_rootPos Pls_integer := 1; --- индекс корня
    ilastPos Pls_integer; -- индекс последнего элемента
  Begin

    ilastPos := pa_array.COUNT;
    -- возвращаемое (снимаемое) значение
    pr_Key_out :=  pa_array(i_rootPos);

    Copy_ItemTo(pa_array => pa_array, pi_from => ilastPos, pi_to => i_rootPos); 
    pa_array.Delete(ilastPos);
    Sink(pa_array, i_rootPos); 
    Return;
  End;  

  -- постановка работы в очередь ожидания завершения
  Procedure Insert_Q(pa_array in out nocopy  T_wrk_states, pr_Key in T_Worker_State)
  Is
    ilastPos Pls_integer;
  Begin
    ilastpos := Coalesce(pa_array.Last + 1, 1 );
    pa_array(ilastpos) := pr_Key;
    Swim( pa_array => pa_array, pi_from => ilastpos);
    Return;
  End;

   -- получение следующего освободившегося работника
  Function get_Next_Event( pa_evt In OUT Nocopy T_wrk_states ) return T_Worker_State
    Is
    r_ret T_Worker_State; 
  Begin    
    If Not Is_Empty(pa_evt) Then
       delMax(pa_evt, r_ret);
    End If;
    Return r_ret;
  End;
 ---------------------------------------------------
   -- подготовка состояния работников 
   -- с одновременной загрузкой их начальной работой
   procedure prepare_workers
   Is
    r_worker T_Worker_State;
   Begin  
     nstop_time := -1.0;
     fetch pcrs Bulk Collect Into a_workers;
     If a_workers.count > 0 Then
       nstop_time := 0.0;       
       For i in 1..a_workers.count
       Loop
         r_worker.inum := i;
         r_worker.current_capacity := 1;
         r_worker.next_avalible := a_workers(i).speed;
         
         Insert_Q(pa_array => a_wrkstate, pr_Key => r_worker);       
         -- nstop_time := Greatest(nstop_time, r_worker.next_avalible);         
         N := N-1;         
         Exit When N <= 0;
       End Loop;  
     End If;       
   End;  
  
 Begin -- основное тело 
   If N > 0.0 Then
     prepare_workers;
     If a_workers.count > 0
       Then        
        Loop
          r_worker := get_Next_Event(a_wrkstate);
          ncur_time := r_worker.next_avalible;
          
          If r_worker.current_capacity < a_workers(r_worker.inum).capacity
            Then
              r_worker.current_capacity := r_worker.current_capacity + 1;
              r_worker.next_avalible := r_worker.next_avalible + a_workers(r_worker.inum).speed;
              N := N-1;
          Else
             r_worker.current_capacity := 0; 
             r_worker.next_avalible := r_worker.next_avalible + a_workers(r_worker.inum).delay;              
          End If;     
          -- поставить обратно в очередь ожидания выполнения работ
          Insert_Q(pa_array => a_wrkstate, pr_key => r_worker );
          Exit When N <=0;
        End Loop;
        -- цикл съема результата 
        While Not Is_Empty(a_wrkstate)
        Loop
          r_worker := get_Next_Event(a_wrkstate);
          If r_worker.current_capacity > 0
            Then
            nstop_time := Greatest(nstop_time, r_worker.next_avalible);         
          End If;
        End Loop;    
     End If;    
   End If;
   Return nstop_time;
 End;  

end sqlru_workers;
/

create or replace package sqlru_workers authid definer
is

-- booby 26.12.2021

 Type T_Worker_Spc Is Record( speed number -- время обработки одной коробки в минутах
                            , capacity number -- число коробок обрабатываемых без перерыва
                            , delay number -- перерыв в минутах после обработки capacity коробок
                            );

 --------------------
 TYPE T_WRK_CURSOR IS REF CURSOR RETURN T_Worker_Spc;
 -----
/*
  вернет отрицательный результат, если для заказанного положительного объема pN
  не найдется работников в курсоре pcrs вообще
  пример вызова
select sqlru_workers.get_min_wrk_time(pcrs => CURSOR(
                                                      select speed, capacity, delay
                                                      From
                                                      (select 1 as speed, 2 as capacity, 3 as delay from dual
                                                      union all select 3, 10, 3 from dual
                                                      union all select 2, 4, 3 from dual) w
                                                       )
                         , pN => 10        -- число коробок
                         , do_output => 0
                          )
from dual
*/
 function get_min_wrk_time(pcrs in T_WRK_CURSOR -- курсор с работничками, ожидается на вход сортированным по speed
                         , pN in Number        -- число коробок
                         , do_output in integer default 0 -- 1 - формировать dbms_output
                          ) Return Number;

end sqlru_workers;
/
create or replace package body sqlru_workers is

 TYPE T_WRKCURSOR_PLDICT is table of T_Worker_Spc index by pls_integer;
 --------------------------------------------------------------------
 Type T_Worker_State Is Record( 
                                speed number -- время обработки одной коробки в минутах
                              , capacity number -- число коробок обрабатываемых без перерыва
                              , delay number -- перерыв в минутах после обработки capacity коробок
                              , total_processed Number -- всего назначено коробок
                              );
 TYPE T_WORKERS_PLDICT is table of T_Worker_State index by pls_integer;
 ------------------------------------------------------------------
 Type T_wrk_queue is Table of pls_integer index by pls_integer;

 -- вернет отрицательный результат, если для заказанного положительного объема pN
 -- не найдется работников в курсоре pcrs вообще
 function get_min_wrk_time(pcrs in T_WRK_CURSOR -- курсор с работничками, ожидается на вход сортированным по speed
                         , pN in Number        -- число коробок
                         , do_output in integer default 0 -- 1 - формировать dbms_output
                          ) Return Number
 Is
   nstop_time Number := 0.0;
   N Number := Coalesce(pN, 0.0);
   a_workers T_WORKERS_PLDICT; -- работнички
   a_wrkstate T_wrk_queue;     -- очередь доступности
   i_worker Pls_integer; 

   isDbms Boolean := (Coalesce(do_output,0) != 0);
  ------------------------------------------------------------------------------
  -- процедуры работы с очередью работников
  -------------------------------------------
  Function Is_Empty(pa_wrk_states in T_wrk_queue) return Boolean
    is
  Begin
    Return pa_wrk_states.COUNT = 0;
  End;
  -- получение индекса родителя
  function get_parent_idx(p_idx_in in Pls_integer) return Pls_integer Deterministic
    is
  begin
    return Floor(0.5*(p_idx_in));
  end;
  --
  Function get_result_cost(pj in pls_integer) return Number
  Is
  Begin

    Return a_workers(pj).speed * (a_workers(pj).total_processed) 
           + floor((a_workers(pj).total_processed-1)/a_workers(pj).capacity)*a_workers(pj).delay
           ; 
  End;    
  --
  Function get_total_cost(pj in pls_integer) return Number
  Is
    pp Number;
  Begin
    pp := a_workers(pj).total_processed;
    Return a_workers(pj).speed * (pp + 1) 
           + floor(pp/a_workers(pj).capacity)*a_workers(pj).delay          
           ; 
  End;    
  -- сравнение элементов
  Function Less(pi_parent in pls_integer  --in T_Worker_State
              , pi_child in pls_integer --in T_Worker_State
               ) Return Boolean
  Is
  child_cost Number;
  parent_cost Number;  
  Begin
    child_cost := get_total_cost(pi_child);
    parent_cost := get_total_cost(pi_parent);
    
   -- Return child_cost < parent_cost;    
     Return Case when child_cost < parent_cost 
                  Then True
                when child_cost = parent_cost  
                  Then a_workers(pi_child).speed < a_workers(pi_parent).speed
                Else False
            End;       

  End;
  -- всплытие 
  Procedure Swim( pa_array in out nocopy T_wrk_queue
                , pi_from in Pls_integer
                )
  is
    i_flow Pls_integer;
    i_parent Pls_integer;
    r_temp Pls_integer; 
  Begin
    i_flow := pi_from;
    i_parent :=  get_parent_idx(i_flow);
    r_temp := pa_array(pi_from);

    While i_flow > 1
        And Less( pi_parent => pa_array(i_parent)
               ,  pi_child => r_temp
                )
    Loop
      pa_array(i_flow) := pa_array(i_parent);
      i_flow := i_parent;
      i_parent := get_parent_idx(i_flow);
    End Loop;

    If i_flow != pi_from
      Then
      pa_array(i_flow) := r_temp;
    End if;

  End;
  -- погружение 
  Procedure Sink(pa_array in out nocopy T_wrk_queue
                , pi_from in Pls_integer
                 )
  Is
    i_flow Pls_integer;
    i_child Pls_integer;
    i_lastPos Pls_integer;

    r_temp Pls_integer; 
  Begin
    i_lastPos := pa_array.COUNT;
    i_flow := pi_from;
    If i_flow <= i_lastPos
      Then
      r_temp := pa_array(pi_from);
      While (i_flow + i_flow ) <= i_lastPos
      Loop
        i_child := (i_flow + i_flow);
        If i_child < i_lastPos
            And Less( pi_parent => pa_array(i_child)
                    , pi_child =>  pa_array(i_child+1)
                    )
          Then
            i_Child := i_child + 1;
        End If;

        Exit When Not Less( pi_parent => r_temp
                         ,  pi_child => pa_array(i_Child)
                          );
        pa_array(i_flow) := pa_array(i_child);
        i_flow := i_child;

      End Loop;

      If i_flow != pi_from
        Then
        pa_array(i_flow) := r_temp;
      End If;
    End If;
    --
  End;
  --------------------------------------------------
  -- удаление и возврат максимального (минимального) элемента
  Procedure delMax(pa_array in out nocopy T_wrk_queue --T_wrk_states
                 , pr_Key_out OUT NOCOPY Pls_integer --T_Worker_State
                   )
  Is
    i_rootPos Pls_integer := 1; --- индекс корня
    ilastPos Pls_integer; -- индекс последнего элемента
  Begin

    ilastPos := pa_array.COUNT;    
    pr_Key_out :=  pa_array(i_rootPos);
    pa_array(i_rootPos) := pa_array(ilastPos);
    pa_array.Delete(ilastPos);
    Sink(pa_array, i_rootPos);
    Return;
  End;

  -- постановка работы в очередь ожидания завершения
  Procedure Insert_Q(pa_array in out nocopy  T_wrk_queue, pr_Key in pls_integer) --T_Worker_State)
  Is
    ilastPos Pls_integer;
  Begin
    ilastpos := Coalesce(pa_array.Last + 1, 1 );
    pa_array(ilastpos) := pr_Key;
    Swim( pa_array => pa_array, pi_from => ilastpos);
    Return;
  End;
  -- подсматриваем в текущего первого
  Function Peek_Next_Worker( pa_wrk_state In T_wrk_queue ) return Pls_integer --T_Worker_State
  Is
    r_ret Pls_integer; --T_Worker_State;
  Begin    
    r_ret :=  pa_wrk_state(1);
    Return r_ret;
  End;    
 ---------------------------------------------------
   -- подготовка начального состояния работников 
   -- с присвоением им начальной стоимости равной скорости обработки одной коробки
   procedure prepare_workers
   Is
    i pls_integer := 0;
    a_wrks T_WRKCURSOR_PLDICT;
   Begin
     nstop_time := -1.0;
     If isDBMS Then
       dbms_output.put_line(' Workers:');
     End If;

     Loop
       Fetch pcrs 
       Bulk Collect into a_wrks Limit 1000;
       Exit When a_wrks.Count = 0;
       For j in 1..a_wrks.Count
       Loop
       i := i + 1;
       a_workers(i).speed := a_wrks(j).speed;
       a_workers(i).capacity := a_wrks(j).capacity;
       a_workers(i).delay := a_wrks(j).delay;
       a_workers(i).total_processed := 0.0;
       Insert_Q(pa_array => a_wrkstate, pr_Key => i);
       End Loop;
     End Loop;           
     If isDbms And a_workers.count > 0 Then
       For j in 1..a_workers.count
       Loop  
         dbms_output.put_line( 
                              ' id='||j
                            ||' speed='||a_workers(j).speed
                            ||' capacity='|| a_workers(j).capacity
                            ||' delay='||a_workers(j).delay
                            ||' total_processed='||a_workers(j).total_processed
                            );
       End Loop;
     End If;
   End;

 Begin -- основное тело
   If N > 0.0 Then
     prepare_workers;
     If a_workers.count > 0
       Then
        LOOP
          ---------------------------------------------
          i_worker := Peek_Next_Worker(a_wrkstate);
          a_workers(i_worker).total_processed := a_workers(i_worker).total_processed + 1;
          ----
          a_wrkstate(1) := a_wrkstate(a_wrkstate.Count);
          a_wrkstate(a_wrkstate.Count) := i_worker;
          Sink(pa_array => a_wrkstate, pi_from => 1);          ----
          N := N - 1;
          ---------------------------------------------
         If isDbms Then
           dbms_output.put_line( 
                              'i_worker='||i_worker
                              ||' N='||(pN - N)
                              ||' total_processed='||a_workers(i_worker).total_processed
                              ||' current_cost='||get_total_cost(i_worker) 
                              ||' result_cost='||get_result_cost(i_worker)                               
                              );          
         End If;
         Exit When N <=0;
        END LOOP;
        -- цикл съема результата
        If isDbms Then
         dbms_output.put_line( 
                            ' final result:-------------'
                            );            
        End If;          
        For i in 1..a_wrkstate.Count

        Loop
          If isDbms Then
           dbms_output.put_line( 
                              ' i='||i
                              ||' total_processed='||a_workers(i).total_processed
                              ||' result_cost='||get_result_cost(i)
                              );            
          End If;  
          nstop_time := Greatest(nstop_time, get_result_cost(i));
        End Loop;
     End If;
   End If;
   Return nstop_time;
 End;

end sqlru_workers;
/
alter package sqlru_workers compile package  PLSQL_DEBUG = False  plsql_code_type =native plsql_optimize_level = 3;

select sqlru_workers.get_min_wrk_time(pcrs => CURSOR(
                                                       select speed, capacity, delay From w0 
                                                       )
                         , pN => 2222        -- число коробок
                         , do_output => 0 -- сформировать dbms_output
                          )
from dual
;
declare 
  n Number;
  crs  sqlru_workers.T_WRK_CURSOR;
begin
  open crs for select speed, capacity, delay From w0; 
  n := sqlru_workers.get_min_wrk_time(pcrs => crs
                         , pN => 2222        -- число коробок
                         , do_output => 0 -- сформировать dbms_output
                          );
  dbms_output.put_line('n='||n);
end;
/ 

SQL> alter package sqlru_workers compile specification PLSQL_DEBUG = true plsql_code_type =interpreted plsql_optimize_level = 2;

Пакет изменен.

SQL> alter package sqlru_workers compile body PLSQL_DEBUG = true plsql_code_type =interpreted plsql_optimize_level = 2;

Тело пакета изменено.

SQL> alter function f_box compile PLSQL_DEBUG = true plsql_code_type =interpreted plsql_optimize_level = 2;

Функция изменена.

SQL> 
SQL> truncate table w;

Таблица усечена.

SQL> insert into w(speed, capacity, delay)
  2  -- select trunc(dbms_random.value(1, 100 + 1)) / 10, trunc(dbms_random.value(1, 100 + 1)), trunc(dbms_random.value(1, 10 + 1))
  3  select trunc(dbms_random.value(1, 150 + 1)) , trunc(dbms_random.value(1, 100 + 1)), trunc(dbms_random.value(1, 10 + 1))
  4  from dual
  5  connect by level <= 300;

Создано строк: 300.

SQL> 
SQL> set timing on
SQL> with r (time_next, n, time, lvl) as
  2  (select 0, 32769 --654321
  3  , 0, 1 from dual
  4   union all
  5   select r.time_next + a.shift, n - a.finished, r.time_next, r.lvl + 1
  6   from r
  7  cross apply (select min(case when tmp < speed * capacity then (trunc(tmp / speed) + 1) * speed else speed * (capacity + 1) + delay end - tmp) shift,
  8                      sum(case when tmp > 0 and tmp <= speed * capacity and mod(tmp, speed) = 0 then 1 else 0 end) finished
  9                     from (select t.*, mod(time_next, speed * capacity + delay) tmp from w t) tt) a
 10  where n > 0)
 11  cycle lvl set cycle to 1 default 0
 12  select max(time) result from r
 13  /

    RESULT                                                                      
----------                                                                      
      3301                                                                      

Затрач.время: 00:00:02.70
SQL> select f_box(32769 -- 654321
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
      3301                                                                      

Затрач.время: 00:00:06.98
SQL> select sqlru_workers.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay from w), pn => 26703 --654321
  2                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
      2693                                                                      

Затрач.время: 00:00:00.53
SQL> 
SQL> set timing off
SQL> truncate table w;

Таблица усечена.

SQL> insert into w(speed, capacity, delay)
  2  -- select trunc(dbms_random.value(1, 100 + 1)) / 10, trunc(dbms_random.value(1, 100 + 1)), trunc(dbms_random.value(1, 10 + 1))
  3  select trunc(dbms_random.value(1, 150 + 1)) , trunc(dbms_random.value(1, 100 + 1)), trunc(dbms_random.value(1, 10 + 1))
  4  from dual
  5  connect by level <= 3000;

Создано строк: 3000.

SQL> 
SQL> set timing on
SQL> with r (time_next, n, time, lvl) as
  2  (select 0, 32769 --654321
  3  , 0, 1 from dual
  4   union all
  5   select r.time_next + a.shift, n - a.finished, r.time_next, r.lvl + 1
  6   from r
  7  cross apply (select min(case when tmp < speed * capacity then (trunc(tmp / speed) + 1) * speed else speed * (capacity + 1) + delay end - tmp) shift,
  8                      sum(case when tmp > 0 and tmp <= speed * capacity and mod(tmp, speed) = 0 then 1 else 0 end) finished
  9                     from (select t.*, mod(time_next, speed * capacity + delay) tmp from w t) tt) a
 10  where n > 0)
 11  cycle lvl set cycle to 1 default 0
 12  select max(time) result from r
 13  /

    RESULT                                                                      
----------                                                                      
       309                                                                      

Затрач.время: 00:00:02.54
SQL> select f_box(32769 -- 654321
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
       309                                                                      

Затрач.время: 00:00:54.31
SQL> select sqlru_workers.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay from w), pn => 32769 --654321
  2                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
       309                                                                      

Затрач.время: 00:00:00.98
SQL> 
SQL> set timing off
SQL> alter package sqlru_workers compile specification PLSQL_DEBUG = false plsql_code_type =interpreted plsql_optimize_level = 2;

Пакет изменен.

SQL> alter package sqlru_workers compile body PLSQL_DEBUG = false plsql_code_type =interpreted plsql_optimize_level = 2;

Тело пакета изменено.

SQL> alter function f_box compile PLSQL_DEBUG = false plsql_code_type =interpreted plsql_optimize_level = 2;

Функция изменена.

SQL> set timing on
SQL> with r (time_next, n, time, lvl) as
  2  (select 0, 32769 --654321
  3  , 0, 1 from dual
  4   union all
  5   select r.time_next + a.shift, n - a.finished, r.time_next, r.lvl + 1
  6   from r
  7  cross apply (select min(case when tmp < speed * capacity then (trunc(tmp / speed) + 1) * speed else speed * (capacity + 1) + delay end - tmp) shift,
  8                      sum(case when tmp > 0 and tmp <= speed * capacity and mod(tmp, speed) = 0 then 1 else 0 end) finished
  9                     from (select t.*, mod(time_next, speed * capacity + delay) tmp from w t) tt) a
 10  where n > 0)
 11  cycle lvl set cycle to 1 default 0
 12  select max(time) result from r
 13  /

    RESULT                                                                      
----------                                                                      
       309                                                                      

Затрач.время: 00:00:02.50
SQL> select f_box(32769 -- 654321
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
       309                                                                      

Затрач.время: 00:00:00.11
SQL> select sqlru_workers.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay from w), pn => 32769 --654321
  2                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
       309                                                                      

Затрач.время: 00:00:00.78
SQL> 
SQL> 
SQL> spool off

alter package sqlru_workers compile specification PLSQL_DEBUG = true plsql_code_type =interpreted plsql_optimize_level = 2;
alter package sqlru_workers compile body PLSQL_DEBUG = true plsql_code_type =interpreted plsql_optimize_level = 2;
alter function f_box compile PLSQL_DEBUG = true plsql_code_type =interpreted plsql_optimize_level = 2;

truncate table w;
insert into w(speed, capacity, delay) 
-- select trunc(dbms_random.value(1, 100 + 1)) / 10, trunc(dbms_random.value(1, 100 + 1)), trunc(dbms_random.value(1, 10 + 1))
select trunc(dbms_random.value(1, 150 + 1)) , trunc(dbms_random.value(1, 100 + 1)), trunc(dbms_random.value(1, 10 + 1))
from dual
connect by level <= 300;

set timing on
with r (time_next, n, time, lvl) as
(select 0, 32769 --654321
, 0, 1 from dual
 union all
 select r.time_next + a.shift, n - a.finished, r.time_next, r.lvl + 1
 from r
cross apply (select min(case when tmp < speed * capacity then (trunc(tmp / speed) + 1) * speed else speed * (capacity + 1) + delay end - tmp) shift,
                    sum(case when tmp > 0 and tmp <= speed * capacity and mod(tmp, speed) = 0 then 1 else 0 end) finished
                   from (select t.*, mod(time_next, speed * capacity + delay) tmp from w t) tt) a
where n > 0)
cycle lvl set cycle to 1 default 0
select max(time) result from r
/
select f_box(32769 -- 654321
            ) result from dual;
select sqlru_workers.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay from w), pn => 26703 --654321
                                     ) result from dual;

set timing off
truncate table w;
insert into w(speed, capacity, delay) 
-- select trunc(dbms_random.value(1, 100 + 1)) / 10, trunc(dbms_random.value(1, 100 + 1)), trunc(dbms_random.value(1, 10 + 1))
select trunc(dbms_random.value(1, 150 + 1)) , trunc(dbms_random.value(1, 100 + 1)), trunc(dbms_random.value(1, 10 + 1))
from dual
connect by level <= 3000;

set timing on
with r (time_next, n, time, lvl) as
(select 0, 32769 --654321
, 0, 1 from dual
 union all
 select r.time_next + a.shift, n - a.finished, r.time_next, r.lvl + 1
 from r
cross apply (select min(case when tmp < speed * capacity then (trunc(tmp / speed) + 1) * speed else speed * (capacity + 1) + delay end - tmp) shift,
                    sum(case when tmp > 0 and tmp <= speed * capacity and mod(tmp, speed) = 0 then 1 else 0 end) finished
                   from (select t.*, mod(time_next, speed * capacity + delay) tmp from w t) tt) a
where n > 0)
cycle lvl set cycle to 1 default 0
select max(time) result from r
/
select f_box(32769 -- 654321
            ) result from dual;
select sqlru_workers.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay from w), pn => 32769 --654321
                                     ) result from dual;
                                     
set timing off                                     
alter package sqlru_workers compile specification PLSQL_DEBUG = false plsql_code_type =interpreted plsql_optimize_level = 2;
alter package sqlru_workers compile body PLSQL_DEBUG = false plsql_code_type =interpreted plsql_optimize_level = 2;
alter function f_box compile PLSQL_DEBUG = false plsql_code_type =interpreted plsql_optimize_level = 2;
set timing on
with r (time_next, n, time, lvl) as
(select 0, 32769 --654321
, 0, 1 from dual
 union all
 select r.time_next + a.shift, n - a.finished, r.time_next, r.lvl + 1
 from r
cross apply (select min(case when tmp < speed * capacity then (trunc(tmp / speed) + 1) * speed else speed * (capacity + 1) + delay end - tmp) shift,
                    sum(case when tmp > 0 and tmp <= speed * capacity and mod(tmp, speed) = 0 then 1 else 0 end) finished
                   from (select t.*, mod(time_next, speed * capacity + delay) tmp from w t) tt) a
where n > 0)
cycle lvl set cycle to 1 default 0
select max(time) result from r
/
select f_box(32769 -- 654321
            ) result from dual;
select sqlru_workers.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay from w), pn => 32769 --654321
                                     ) result from dual;