By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TABLE_NAME ( job ) AS
SELECT 4 FROM DUAL UNION ALL
SELECT 60 FROM DUAL UNION ALL
SELECT 50 FROM DUAL UNION ALL
SELECT 1 FROM DUAL UNION ALL
SELECT 100 FROM DUAL UNION ALL
SELECT 6 FROM DUAL;
6 rows affected
CREATE TYPE job_processor AS OBJECT(
job NUMBER,
processor NUMBER
);
CREATE TYPE job_processor_list AS TABLE OF job_processor;
CREATE FUNCTION partition_jobs (
num_processors IN PLS_INTEGER
) RETURN job_processor_list PIPELINED
IS
processor_time SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();
processor_id PLS_INTEGER;
min_processor_time TABLE_NAME.JOB%TYPE;
BEGIN
processor_time.EXTEND( num_processors );
FOR i IN 1 .. num_processors LOOP
processor_time(i) := 0;
END LOOP;
FOR j IN ( SELECT job FROM table_name ORDER BY job DESC ) LOOP
processor_id := 1;
min_processor_time := processor_time( processor_id );
FOR i IN 2 .. num_processors LOOP
IF processor_time(i) < min_processor_time THEN
processor_id := i;
min_processor_time := processor_time( processor_id );
END IF;
END LOOP;
PIPE ROW ( job_processor( j.job, processor_id ) );
processor_time( processor_id ) := processor_time( processor_id ) + j.job;
END LOOP;
END;
/
SELECT *
FROM TABLE( partition_jobs( 2 ) );
| JOB | PROCESSOR |
|---|---|
| 100 | 1 |
| 60 | 2 |
| 50 | 2 |
| 6 | 1 |
| 4 | 1 |
| 1 | 1 |
SELECT *
FROM TABLE( partition_jobs( 3 ) );
| JOB | PROCESSOR |
|---|---|
| 100 | 1 |
| 60 | 2 |
| 50 | 3 |
| 6 | 3 |
| 4 | 3 |
| 1 | 2 |