0

Oracle SQL

Example 950/100 The table should able to populate as below

  1. 100
  2. 100
  3. 100
  4. 100
  5. 100
  6. 100
  7. 100
  8. 100
  9. 100
  10. 50

Had try CONNECT BY LEVEL , however it seems to be taking too long with huge data stored tables. Any other alternatives way instead of this ? Thanks !

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Simplot
  • 13
  • 4
  • Do you only have one such number to split, or a whole bunch of them? Are they all divided by 100, or is each divided by a different fixed number? Also, what Oracle version do you use? Since Oracle 12.1, you should use the "connect by" thing in a lateral clause, which makes the process much faster. But it's not worth discussing that if we don't know your version. –  Jun 17 '21 at 18:54

2 Answers2

1

You can use a recursive sub-query factoring clause:

WITH data ( rn, value, remainder, divisor ) AS (
  SELECT ROWNUM, value, value, divisor FROM table_name
UNION ALL
  SELECT rn, value, remainder - divisor, divisor FROM data WHERE remainder > divisor
)
SEARCH DEPTH FIRST BY rn SET rn_order
SELECT value, divisor, LEAST( remainder, divisor ) AS split_value
FROM   data;

Or, from Oracle 12, a hierarchical query:

SELECT *
FROM   table_name t
       CROSS APPLY (
         SELECT LEAST( t.divisor, t.value - (LEVEL - 1) * t.divisor ) AS split_value
         FROM   DUAL
         CONNECT BY LEVEL <= CEIL(t.value/t.divisor)
       )

Which, for the sample data:

CREATE TABLE table_name ( value, divisor ) AS
SELECT 950, 100 FROM DUAL UNION ALL
SELECT 2.4, 0.5 FROM DUAL

Both output:

VALUE DIVISOR SPLIT_VALUE
950 100 100
950 100 100
950 100 100
950 100 100
950 100 100
950 100 100
950 100 100
950 100 100
950 100 100
950 100 50
2.4 0.5 0.5
2.4 0.5 0.5
2.4 0.5 0.5
2.4 0.5 0.5
2.4 0.5 0.4

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
1

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