Oracle representing hierarchy of data combinations <p>I'm having an Oracle volume of data issue, which I think is being caused by a data representation problem. I have a hierarchy made from seven different lists, and need to store all the unique potential combinations of these elements. I need to store all the potential combinations, because I need to store additional information along with them. </p> <p>Historically, we've been managing four lists, which came to a few thousand combinations. This was fine, but requirements have changed as they are wont to do, and now we are running seven lists. In my real environment, the lists store between 20 and 200 items. The total number of combinations for this now, incredibly, is about 350 billion. </p> <p>As an example of what we're trying to represent with this, you could have a selection of lists like:</p> <p>Colours:Red, Green, Blue</p> <p>Shapes: Circle, Square, Triangle</p> <p>Flavours: Peach, Banana, Raspberry</p> <p>These would be stored as separate tables in Oracle like COLOURS, SHAPES, FLAVOURS, etc, and what we'd be doing is multiplying them all together (via a Cartesian join) to get an exploded table: This is what we're storing in Oracle at the moment.</p> <pre><code>COLOUR SHAPE FLAVOUR DATA_1 DATA_2 Red Circle Peach -- these hold additional information Red Circle Banana Red Circle Raspberry Red Square Peach Red Square Banana ... </code></pre> <p>and so on.</p> <p>We've found we can't store billions of records within an Oracle table and have any operation performed upon them in a timely fashion. I've tried to think of different ways to store this information but my brain keeps jamming up on the number of combinations. Is there anything I can do to store the records differently? Bearing in mind they're all unique combinations, and we need to store data along with them.</p> <p>Thanks for any help!</p>