I'm a developer on a reporting application that runs complex user-defined queries, stores the results, and then sends the result to Excel generation, web-based tables, and graphs, and so on. Currently, we're storing the entire result set serialized as a clob in an existing table.
These result sets may be large (some >1GB) and expensive to generate (many minutes). Regenerating them on-the-fly is not an option. They also are heterogeneous -- each result set will likely have very different columns. The result sets are immutable -- they will never be modified, although they will eventually be deleted. Each result set will be read occasionally, but often in bursts (e.g. if someone views the data via our web interface, then we expect many more requests soon).
Currently, these thousands of result sets are taking up a lot of DB space. In addition, it is difficult to manipulate the values other than to select the entire result set or the first n records. We would like to be able to sort and filter the result set. It seems logical that if we have tabular data that we'd like to sort and filter, then storing it as a table is the obvious answer.
However, I'm not sure what the right way would be to do this in Oracle. The naive solution would be to dynamically run CREATE TABLE
statements for every one, naming them from a GUID or something. This kind of vaguely feels like an unwise thing to do, maybe unless we also move this data to a separate database. I looked up global temporary tables, but we need them to persist across sessions. A co-worker suggested using external tables, but I'm not sure I understand the performance implications of that. Basically, I'm in over my head from an Oracle architecture standpoint, and I'd appreciate advice from more experienced developers and DBAs.
So: given these constraints, what approaches should I consider?