create or replace package sqlru_workers2 authid definer
is

-- booby 01.01.2022

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

 --------------------
 TYPE T_WRK_CURSOR IS REF CURSOR RETURN T_Worker_Spc;
 -----
/*
  вернет отрицательный результат, если для заказанного положительного объема pN
  не найдется работников в курсоре pcrs вообще
  пример вызова
select sqlru_workers2.get_min_wrk_time(pcrs => CURSOR(
                                                      select speed, capacity, delay, count(*) as workers
                                                      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
                                                       )
                                                      Group By  speed, capacity, delay
                                                      -- пока код от явной сортировки не зависит, но это может оказаться необходимым в последующих модификациях
                                                      Order by speed asc, capacity desc, delay asc
                         , pN => 10        -- число коробок
                          )
from dual
*/
 function get_min_wrk_time(pcrs in T_WRK_CURSOR -- курсор с работничками, ожидается на вход сортированным по speed
                         , pN in Number        -- число коробок
                         , do_output in integer default 0 -- формировать dbms_output
                          ) Return Number;

end sqlru_workers2;
/
create or replace package body sqlru_workers2 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 коробок
                              , workers pls_integer -- число работников с указанными характеристиками
                              , cur_run pls_integer -- осталось до перерыва, используется только в счете перерывов total_stops
                              , total_processed pls_integer --Number -- всего назначено коробок, обработано целой группой - total_processed * workers
                              , over_run pls_integer -- остаток, назначенный части группы < workers 
                                                     -- максимальное число работ, выполненное одним исполнителем = total_processed + case overrun > 0 Then 1 Else 0 End
                              , total_stops pls_integer -- число остановок
                              , total_cost Number       -- стоимость = здесь время завершения следующей работы 
                              );
 TYPE T_WORKERS_PLDICT is table of T_Worker_State; -- index by pls_integer;
 ------------------------------------------------------------------
 SUBTYPE T_QUEUE_ITEM is pls_integer;
 Type T_wrk_queue is Table of T_QUEUE_ITEM 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 := T_WORKERS_PLDICT(); -- работнички
   a_wrkstate T_wrk_queue;                           -- очередь доступности
   i_worker pls_integer;

   isDbms Boolean := (Coalesce(do_output,0) != 0);
   i_cnt simple_integer := 0;
   work_batch Number;
  ------------------------------------------------------------------------------
  -- процедуры работы с очередью работников
  -------------------------------------------
  /*
  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 + case when a_workers(pj).over_run > 0 Then 1 Else 0 End)           
--           + floor((a_workers(pj).total_processed-1)/a_workers(pj).capacity)*a_workers(pj).delay
           + floor((a_workers(pj).total_processed + case when a_workers(pj).over_run > 0 Then 1 Else 0 End - 1)/a_workers(pj).capacity)*a_workers(pj).delay
           
           ;
  End;
  --
  -- сравнение элементов по значению
  Function Less(pi_parent in T_QUEUE_ITEM 
              , pi_child in T_QUEUE_ITEM 
               ) Return Boolean
  Is
    child_cost Number := a_workers(pi_child).total_cost;
    parent_cost Number := a_workers(pi_parent).total_cost;
  Begin

     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 := pi_from;
    i_parent Pls_integer;
    r_temp T_QUEUE_ITEM;
  Begin

    PRAGMA INLINE(get_parent_idx,'YES');
    i_parent :=  get_parent_idx(i_flow);
    r_temp := pa_array(pi_from);

    PRAGMA INLINE(Less,'YES');
    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;
      PRAGMA INLINE(get_parent_idx,'YES');
      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 := pi_from;
    i_child Pls_integer := 0;
    i_lastPos Pls_integer := pa_array.COUNT;
    --
    r_temp T_QUEUE_ITEM; --Pls_integer;
  Begin
    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);

        PRAGMA INLINE(Less,'YES');
        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;

        PRAGMA INLINE(Less,'YES');
        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 T_QUEUE_ITEM --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);

    PRAGMA INLINE(Sink,'YES');
    Sink(pa_array, i_rootPos);
    Return;
  End;
*/
  -- постановка работы в очередь ожидания завершения
  Procedure Insert_Q(pa_array in out nocopy  T_wrk_queue, pr_Key in T_QUEUE_ITEM) -- pls_integer) --T_Worker_State)
  Is
    ilastPos Pls_integer;
  Begin
   ilastpos := Coalesce(pa_array.Last + 1, 1 );
    pa_array(ilastpos) := pr_Key;
    PRAGMA INLINE(Swim,'YES');
    Swim( pa_array => pa_array, pi_from => ilastpos);
    Return;
  End;
  -- подсматриваем в текущего первого
  Function Peek_Next_Worker( pa_wrk_state In T_wrk_queue ) return T_QUEUE_ITEM --Pls_integer --T_Worker_State
  Is
  Begin
    Return pa_wrk_state(1);
  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;
       a_workers.Extend(a_wrks.Count);
       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).workers := a_wrks(j).workers;
         a_workers(i).cur_run := a_wrks(j).capacity;
         a_workers(i).total_processed := 0.0;
         a_workers(i).over_run := 0.0;
         a_workers(i).total_stops := 0.0;
         a_workers(i).total_cost := a_wrks(j).speed;

         PRAGMA INLINE(Insert_Q,'YES');
         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;
     Exception
       When Others THen
               dbms_output.put_line(
                            sqlcode||'-'||sqlerrm
                            ||' i='|| i
                            ||' a_wrkstate.Count='||a_wrkstate.Count
                            ||' a_wrkstate.First='||a_wrkstate.First
                            ||' a_wrkstate.Last='||a_wrkstate.Last
                            );
                            raise;
   End;
  -- обработка единственной группы  
  Procedure process_singular_group
  Is
  Begin
    
    i_worker := Peek_Next_Worker(a_wrkstate);
    work_batch := Floor(N/a_workers(i_worker).workers);
    a_workers(i_worker).total_processed := work_batch;
    -----------------------------------------------    
    a_workers(i_worker).over_run :=  N - work_batch;
    a_workers(i_worker).total_stops := Floor(work_batch / a_workers(i_worker).capacity);
    a_workers(i_worker).cur_run := N - a_workers(i_worker).total_stops * a_workers(i_worker).capacity
                                     - case when a_workers(i_worker).over_run > 0 Then 1 Else 0 End;
    
    ------------------------------------------------
    If a_workers(i_worker).cur_run = 0
      Then
        a_workers(i_worker).total_stops := a_workers(i_worker).total_stops + 1;
        a_workers(i_worker).cur_run := a_workers(i_worker).capacity;
    End IF;
    a_workers(i_worker).total_cost := a_workers(i_worker).speed * (a_workers(i_worker).total_processed 
                                    + 1 + case when a_workers(i_worker).over_run > 0 Then 1 Else 0 End)
                                    + a_workers(i_worker).total_stops*a_workers(i_worker).delay;    
    N := 0;
  End;    
 Begin -- основное тело
   If N > 0.0 Then
     PRAGMA INLINE(prepare_workers,'YES');
     prepare_workers;
     If a_workers.count > 0
       Then
       If a_workers.count > 1 
         Then        
        LOOP
          ---------------------------------------------
          PRAGMA INLINE(Peek_Next_Worker,'YES');
          i_worker := Peek_Next_Worker(a_wrkstate);
          work_batch := Least(N, a_workers(i_worker).workers);
          N := N - work_batch;                       

          If work_batch = a_workers(i_worker).workers
            Then
            i_cnt := a_workers(i_worker).total_processed+1;
            a_workers(i_worker).total_processed := a_workers(i_worker).total_processed+1;            
          Else 
            a_workers(i_worker).over_run := work_batch;
          End IF;

          a_workers(i_worker).cur_run := a_workers(i_worker).cur_run - 1;
          If a_workers(i_worker).cur_run = 0 
            Then
              a_workers(i_worker).total_stops := a_workers(i_worker).total_stops + 1;
              a_workers(i_worker).cur_run := a_workers(i_worker).capacity;
          End IF;
          a_workers(i_worker).total_cost := a_workers(i_worker).speed 
                                                  * (a_workers(i_worker).total_processed 
                                                      + 1 + case when a_workers(i_worker).over_run > 0 Then 1 Else 0 End
                                                     )
                                          + a_workers(i_worker).total_stops*a_workers(i_worker).delay;

  ------------------------------------------------------------------------
          PRAGMA INLINE(Sink,'YES');
          Sink(pa_array => a_wrkstate, pi_from => 1);
         Exit When N <=0;
        END LOOP;
      
       Else
         PRAGMA INLINE(process_singular_group,'YES');
         process_singular_group;  
       End If;   
        -- цикл съема результата
        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
                              ||' over_run='||a_workers(i).over_run
                              ||' total_stops='||a_workers(i).total_stops
                              ||' result_cost='||get_result_cost(i)
                              );
          End If;

          PRAGMA INLINE(get_result_cost,'YES');
          nstop_time := Greatest(nstop_time, get_result_cost(i));
        End Loop;
     End If;
   End If;
   Return nstop_time;
 End;

end sqlru_workers2;
/

alter package sqlru_workers2 compile package PLSQL_DEBUG = false plsql_code_type = native plsql_optimize_level = 3;
alter function f_box compile PLSQL_DEBUG = false plsql_code_type = native plsql_optimize_level = 3;

-- мало исполнителей, 2 группы
truncate table w;
insert into w(speed, capacity, delay)
(select 5, 2, .1 
from dual connect by level <= 2 
union all
select 1.5, 8, 5.1 
from dual connect by level <= 3
)
/
commit;
set timing on
-- это считается
select f_box(2878787
            ) result from dual;            
select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
                                                     from w
                                                     group by speed, capacity, delay
                                                     order by speed asc, capacity desc, delay asc
                                                     )
                                    , pn => 2878787
                                    , do_output => 0                                    
                                     ) result from dual;

-- демонстрация переполнения
select f_box(3878787
            ) result from dual;            
select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
                                                     from w
                                                     group by speed, capacity, delay
                                                     order by speed asc, capacity desc, delay asc
                                                     )
                                    , pn => 3878787
                                    , do_output => 0                                    
                                     ) result from dual;
-- случайное зполнение-1, не очень много работников
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, 10 + 1)), trunc(dbms_random.value(1, 40 + 1))
    from dual
    connect by level <= 400;
commit;
set timing on
select f_box(777777 
            ) result from dual;            
select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
                                                     from w
                                                     group by speed, capacity, delay
                                                     order by speed asc, capacity desc, delay asc
                                                     )
                                    , pn => 777777 
                                     ) result from dual;
-- что там с зависимостями от N
select f_box(9876543
            ) result from dual;            
select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
                                                     from w
                                                     group by speed, capacity, delay
                                                     order by speed asc, capacity desc, delay asc
                                                     )
                                    , pn => 9876543
                                     ) result from dual;

-- случайное заполнение-2
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, 20 + 1)), trunc(dbms_random.value(1, 20 + 1))
    from dual
    connect by level <= 40000;
commit;
set timing on
select f_box(777777 
            ) result from dual;            
select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
                                                     from w
                                                     group by speed, capacity, delay
                                                     order by speed asc, capacity desc, delay asc
                                                     )
                                    , pn => 777777 
                                     ) result from dual;

-- все одинаковые
set timing off
truncate table w;
insert into w(speed, capacity, delay)
select 0.5, 7, 3.1 
from dual connect by level <= 10e3
/
commit;
set timing on
select f_box(7777777 
            ) result from dual;            
select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
                                                     from w
                                                     group by speed, capacity, delay
                                                     order by speed asc, capacity desc, delay asc
                                                     )
                                    , pn => 7777777 
                                     ) result from dual;

-- малое число одинаковых групп
set timing off
truncate table w;
insert into w(speed, capacity, delay)                                     
(
select 0.5 speed, 7 capacity, 3 delay
from dual connect by level <= 3e3
union all 
select 0.3, 8, 4.8
from dual connect by level <= 3e3
union all 
select 5.8, 15, 11.4
from dual connect by level <= 3e3
union all
select 2.8, 13, 6.6
from dual connect by level <= 500
union all
select 5.8 speed, 15, 15.5
from dual connect by level <= 500
)
/
commit;
set timing on
--
select f_box(777777 
            ) result from dual;            
select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
                                                     from w
                                                     group by speed, capacity, delay
                                                     order by speed asc, capacity desc, delay asc
                                                     )
                                    , pn => 777777
                                     ) result from dual;


-- в общем, бяка
set timing off
truncate table w;
insert into w(speed, capacity, delay)                                     
(
select 0.5 speed, 7 capacity, 3 delay
from dual connect by level <= 3e3
union all 
select 0.3, 8, 4.8
from dual connect by level <= 2e3
union all 
select trunc(dbms_random.value(1, 100 + 1)) / 10, trunc(dbms_random.value(1, 20 + 1)), trunc(dbms_random.value(1, 20 + 1))
    from dual
    connect by level <= 6000
)
/
commit;
set timing on
--
select f_box(876543
            ) result from dual;            
select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
                                                     from w
                                                     group by speed, capacity, delay
                                                     order by speed asc, capacity desc, delay asc
                                                     )
                                    , pn => 876543
                                     ) result from dual;
-- что там с зависимостями от N
select f_box(9876543
            ) result from dual;            
select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
                                                     from w
                                                     group by speed, capacity, delay
                                                     order by speed asc, capacity desc, delay asc
                                                     )
                                    , pn => 9876543
                                     ) result from dual;

SQL> alter package sqlru_workers2 compile package PLSQL_DEBUG = false plsql_code_type = native plsql_optimize_level = 3;

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

SQL> alter function f_box compile PLSQL_DEBUG = false plsql_code_type = native plsql_optimize_level = 3;

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

SQL> 
SQL> -- мало разномастных исполнителей
SQL> truncate table w;

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

SQL> insert into w(speed, capacity, delay)
  2  (select 5, 2, .1
  3  from dual connect by level <= 2 
  4  union all
  5  select 1.5, 8, 5.1
  6  from dual connect by level <= 3
  7  )
  8  /

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

SQL> commit;

Фиксация обновлений завершена.

SQL> set timing on
SQL> -- это считается
SQL> select f_box(2878787
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
 1599726,6                                                                      

Затрач.время: 00:00:05.55
SQL> select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
  2                                                       from w
  3                                                       group by speed, capacity, delay
  4                                                       order by speed asc, capacity desc, delay asc
  5                                                       )
  6                                      , pn => 2878787
  7                                      , do_output => 0
  8                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
 1599726,6                                                                      

Затрач.время: 00:00:00.40
SQL> 
SQL> -- демонстрация переполнения
SQL> select f_box(3878787
  2              ) result from dual;
select f_box(3878787
       *
ошибка в строке 1:
ORA-01426: переполнение числа 
ORA-06512: на  "SCOTT.F_BOX", line 33 


Затрач.время: 00:00:07.30
SQL> select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
  2                                                       from w
  3                                                       group by speed, capacity, delay
  4                                                       order by speed asc, capacity desc, delay asc
  5                                                       )
  6                                      , pn => 3878787
  7                                      , do_output => 0
  8                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
 2155422,3                                                                      

Затрач.время: 00:00:00.56
SQL> -- случайное зполнение-1, не очень много работников
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, 10 + 1)), trunc(dbms_random.value(1, 40 + 1))
  3      from dual
  4      connect by level <= 400;

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

SQL> commit;

Фиксация обновлений завершена.

SQL> set timing on
SQL> select f_box(777777
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
     11765                                                                      

Затрач.время: 00:00:01.50
SQL> select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
  2                                                       from w
  3                                                       group by speed, capacity, delay
  4                                                       order by speed asc, capacity desc, delay asc
  5                                                       )
  6                                      , pn => 777777
  7                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
     11765                                                                      

Затрач.время: 00:00:01.34
SQL> -- что там с зависимостями от N
SQL> select f_box(9876543
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
    149458                                                                      

Затрач.время: 00:00:18.36
SQL> select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
  2                                                       from w
  3                                                       group by speed, capacity, delay
  4                                                       order by speed asc, capacity desc, delay asc
  5                                                       )
  6                                      , pn => 9876543
  7                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
    149458                                                                      

Затрач.время: 00:00:16.62
SQL> 
SQL> -- случайное заполнение-2
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, 20 + 1)), trunc(dbms_random.value(1, 20 + 1))
  3      from dual
  4      connect by level <= 40000;

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

SQL> commit;

Фиксация обновлений завершена.

SQL> set timing on
SQL> select f_box(777777
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
      78,1                                                                      

Затрач.время: 00:00:01.81
SQL> select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
  2                                                       from w
  3                                                       group by speed, capacity, delay
  4                                                       order by speed asc, capacity desc, delay asc
  5                                                       )
  6                                      , pn => 777777
  7                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
      78,1                                                                      

Затрач.время: 00:00:01.61
SQL> 
SQL> -- все одинаковые
SQL> set timing off
SQL> truncate table w;

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

SQL> insert into w(speed, capacity, delay)
  2  select 0.5, 7, 3.1
  3  from dual connect by level <= 10e3
  4  /

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

SQL> commit;

Фиксация обновлений завершена.

SQL> set timing on
SQL> select f_box(7777777
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
     733,1                                                                      

Затрач.время: 00:00:10.99
SQL> select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
  2                                                       from w
  3                                                       group by speed, capacity, delay
  4                                                       order by speed asc, capacity desc, delay asc
  5                                                       )
  6                                      , pn => 7777777
  7                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
     733,1                                                                      

Затрач.время: 00:00:00.02
SQL> 
SQL> -- малое число одинаковых групп
SQL> set timing off
SQL> truncate table w;

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

SQL> insert into w(speed, capacity, delay)
  2  (
  3  select 0.5 speed, 7 capacity, 3 delay
  4  from dual connect by level <= 3e3
  5  union all
  6  select 0.3, 8, 4.8
  7  from dual connect by level <= 3e3
  8  union all
  9  select 5.8, 15, 11.4
 10  from dual connect by level <= 3e3
 11  union all
 12  select 2.8, 13, 6.6
 13  from dual connect by level <= 500
 14  union all
 15  select 5.8 speed, 15, 15.5
 16  from dual connect by level <= 500
 17  )
 18  /

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

SQL> commit;

Фиксация обновлений завершена.

SQL> set timing on
SQL> --
SQL> select f_box(777777
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
       106                                                                      

Затрач.время: 00:00:01.09
SQL> select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
  2                                                       from w
  3                                                       group by speed, capacity, delay
  4                                                       order by speed asc, capacity desc, delay asc
  5                                                       )
  6                                      , pn => 777777
  7                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
       106                                                                      

Затрач.время: 00:00:00.01
SQL> 
SQL> 
SQL> -- в общем, бяка
SQL> set timing off
SQL> truncate table w;

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

SQL> insert into w(speed, capacity, delay)
  2  (
  3  select 0.5 speed, 7 capacity, 3 delay
  4  from dual connect by level <= 3e3
  5  union all
  6  select 0.3, 8, 4.8
  7  from dual connect by level <= 2e3
  8  union all
  9  select trunc(dbms_random.value(1, 100 + 1)) / 10, trunc(dbms_random.value(1, 20 + 1)), trunc(dbms_random.value(1, 20 + 1))
 10      from dual
 11      connect by level <= 6000
 12  )
 13  /

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

SQL> commit;

Фиксация обновлений завершена.

SQL> set timing on
SQL> --
SQL> select f_box(876543
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
       125                                                                      

Затрач.время: 00:00:01.40
SQL> select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
  2                                                       from w
  3                                                       group by speed, capacity, delay
  4                                                       order by speed asc, capacity desc, delay asc
  5                                                       )
  6                                      , pn => 876543
  7                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
       125                                                                      

Затрач.время: 00:00:00.48
SQL> -- что там с зависимостями от N
SQL> select f_box(9876543
  2              ) result from dual;

    RESULT                                                                      
----------                                                                      
    1433,4                                                                      

Затрач.время: 00:00:15.03
SQL> select sqlru_workers2.get_min_wrk_time(pcrs => cursor(select speed, capacity, delay, count(*) as workers
  2                                                       from w
  3                                                       group by speed, capacity, delay
  4                                                       order by speed asc, capacity desc, delay asc
  5                                                       )
  6                                      , pn => 9876543
  7                                       ) result from dual;

    RESULT                                                                      
----------                                                                      
    1433,4                                                                      

Затрач.время: 00:00:05.26
SQL> 
SQL>