Oracle Single table with json vs set of tables using joins


I'm building a reporting solution. The data will be stored on Oracle database. I expect to get near to several billion data set since i have to keep data for 1 year period. When designing database schema i faced problem to go with single table or set of tables.

  1. Single table in format of (ID, DATETTIME, JSON ) which has all the data dumped as json in JSON column. this will avoid any joins also cater for future event format changes.

  2. Set of tables ( 4-6) data dived on them. query will be using several joins and several unions. Format will be hard to change.

What would be the better approach in terms of performance. For large data set how efficient json over joins ?

Best Answer

Part of that depends on whether or not you need to ever parse that JSON, say in a WHERE clause, or GROUP BY, etc...

If it stays a faceless blob, it's probably faster to leave it as option #1; but keep in mind this is rarely the case - you're likely to unexpectedly be asked to report/query on the JSON at some point!!!

I don't know about Oracle, but in Microsoft SQL Server, UNIONs tend to perform poorly when ALSO joining; they seem fine as a standalone query but once it becomes a subquery things seem to slow down, unless you do something like a materialized view.

So while I personally prefer option 2, the performance may bog down with a large data set that is over 1 million rows. Can you refrain from using a UNION, and instead place the records in the same table but distinguish them with a type column?

  1. How many rows (and how wide is a single row) of your "large data set"?
  2. Can you temporarily obtain a test environment and try both options under a load test?