0

I have a table like this

parent_item  child_item  quantity
          A           B         2
          A           C         3
          B           E         1
          B           F         2

And would like to split this in multiple lines based on the quantity

parent_item  child_item  quantity
         A            B         1
         A            B         1
         A            C         1
         A            C         1
         A            C         1
         B            E         1
         B            F         1
         B            F         1

The column quantity (1) is not really necessary.

I was able to generate something with the help of connect by / level, but for large tables it's very very slow.I'm not really familiar with connect by / level, but this seemed to work, although I can't really explain:

select distinct parent_item, level LEVEL_TAG, child_item, level||quantity
FROM table
CONNECT BY quantity>=level
order by 1 asc;

I found similar questions, but in most cases topicstarter want's to split a delimited column value in multiple lines (Oracle - split single row into multiple rows)

What's the most performant method to solve this?

Thanks

Community
  • 1
  • 1
SDR
  • 23
  • 1
  • 1
  • 3
  • The reason why your connect by solution is slow is because you've not restricted the connect by to loop over each row by itself, so it's doing extra work. On top of that, you're then asking to get all the unique rows, which is yet more work. A [very similar question](http://stackoverflow.com/questions/41564309/enumerating-rows-in-a-inner-join/41565096#41565096) was asked yesterday, for which I gave a solution that used connect by to create the duplicate rows much more efficiently. It's the Oracle-syntax version of the solution that MTO has given below. – Boneist Jan 11 '17 at 11:14
  • @Boneist See [How to convert comma separated values to rows in oracle?](http://stackoverflow.com/q/38371989/1509264) for a discussion on the performance of various queries - the conclusion there was that using `DBMS_RANDOM` or `SYS_GUID()` in a hierarchical query was several times slower than either a correlated hierarchical query or a recursive sub-query factoring clause. – MT0 Jan 11 '17 at 11:35
  • @MT0 Interesting - thanks! I guess I should really get around to learning the recursive approach and making that my go-to for hierarchical queries *{:-) – Boneist Jan 11 '17 at 11:38

1 Answers1

9

Use a recursive sub-query factoring clause:

WITH split ( parent_item, child_item, lvl, quantity ) AS (
  SELECT parent_item, child_item, 1, quantity
  FROM   your_table
UNION ALL
  SELECT parent_item, child_item, lvl + 1, quantity
  FROM   split
  WHERE  lvl < quantity
)
SELECT parent_item, child_item, 1 As quantity
FROM   split;

Or you can use a correlated hierarchical query:

SELECT t.parent_item, t.child_item, 1 AS quantity
FROM   your_table t,
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM DUAL
             CONNECT BY LEVEL <= t.quantity
           )
           AS SYS.ODCINUMBERLIST
         )
       ) l;

As for which is more performant - try benchmarking the different solutions as we cannot tell you what will be more performant on your system.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Ooooh, that's clever! – JohnHC Jan 11 '17 at 11:10
  • Thanks a lot! Sorry about the duplicate question, i was using different keywords and never would have found the duplicate question ... The correlated hierarchical query gave us the best results. – SDR Jan 11 '17 at 13:56
  • It wasn't until I went to find the link in my other comment on your question (regarding performance) that I realised how many times I'd answered the same question over the past few years and have marked most of those as duplicates too. – MT0 Jan 11 '17 at 14:01