One approach which is worth a try is to determine the highest number of rows you may need for any input value, then to generate rows (with connect by
or by any other means) just once, and then use a join to this "helper" table to generate the rows. This approach reads the base data twice, but I assume that's not the big bottleneck in this problem. In any case - worth a try.
Note that the solution also minimizes the number of arithmetic operations - it compares "row number" to the ratio of value to divisor for each row, but otherwise the output value is just the divisor in most cases.
with
h (rn) as (
select level
from dual
connect by level <= (select max(ceil(value/divisor)) from table_name)
)
select tn.id, tn.value, tn.divisor, h.rn,
case when h.rn <= tn.value/tn.divisor
then tn.divisor else mod(tn.value, tn.divisor) end as split_value
from table_name tn join h on h.rn <= ceil(tn.value/tn.divisor)
order by tn.id, h.rn
;
Which, for the data below, produces the output shown at the end. Note that I assumed there is an id
column too, as primary key; in the query, I only need that to get a proper order by
clause. If ordering is not needed, you don't need such an id
(although you probably have it already, as an invoice number, loan number, or similar). You can also use rowid
instead of id
, if the only use is in the order by
clause.
Sample data:
create table table_name (id, value, divisor) as
select 2301, 450, 100 from dual union all
select 2302, 2.3, 0.5 from dual union all
select 2303, 300, 100 from dual union all
select 2304, 8, 20 from dual union all
select 2305, 150, 150 from dual
;
Output from the query, using this sample data:
ID VALUE DIVISOR RN SPLIT_VALUE
---------- ---------- ---------- ---------- -----------
2301 450 100 1 100
2301 450 100 2 100
2301 450 100 3 100
2301 450 100 4 100
2301 450 100 5 50
2302 2.3 .5 1 .5
2302 2.3 .5 2 .5
2302 2.3 .5 3 .5
2302 2.3 .5 4 .5
2302 2.3 .5 5 .3
2303 300 100 1 100
2303 300 100 2 100
2303 300 100 3 100
2304 8 20 1 8
2305 150 150 1 150