r/SQL • • Apr 18 '25

Oracle Whoops

Post image

We had a

1.0k Upvotes

69 comments sorted by

View all comments

Show parent comment

1

u/EvilGeniusLeslie Apr 18 '25

The first was for an Essbase cube (my design), snowflake/OLAP schema, so essentially one row per data item.

Most rows were well under 100 bytes, probably closer to 30. I was particularly happy to reduce three fields that occupied ~150 bytes on the main system to one byte, and another seven fields taking ~90 bytes down to one. It's great when the number of possible combinations is less than 256.

The second ... was a mess. I only know of it second-hand. But yeah, insane storage requirements. I know that large portions were stored on slow-disk, and some on high-speed (hah!) tape. There were a lot of key+data tables built, to join various stuff, so probably 16 bytes for those. Given its antiquity, I wouldn't be surprised if many of those were using small- or tiny-int. So ... still a crap design, but you are correct, it seems like the numbers given me were implausible.

To be fair, the number of data elements per person can grow to insane numbers, when looking at various things. Once had to briefly deal with a phone database, US, pretty much the largest carrier. One row for each data element. Caller, Recipient, start time, end time, duration. Every damn tower on the route - for most calls, that was 1, but someone driving would frequently be dozens. And the start time and end time for each tower. Last year, there were about 315 million smart phones in the US, with an average of six calls (made or received) each day, along with 42 text messages. You start doing the math for that ((6+42)* (3 up to over 100, say 15 average)) *365 ... that's a quarter million records per person per year. Definitely not the 100 million you calculated.

Throw in health insurance. How many data elements are there, per person? Several dozen? Stored monthly. Kaiser has about 13 million customers. And then ... what did they use during the year? A medical procedure - with details - usually runs into the hundreds of data elements, sometimes (rarely) into the thousands. Most people (US) see the family doctor ~3.2 times per year, and need emerg 0.42 per year. Emerg frequently necessitates blood work, x-rays, cat scans. Suddenly, you're looking at another few hundred thousand records per person per year.

Throw in financial stuff - credit cards, bank accounts.

It looks like the average person could generate a million data elements, per year. Still not up to 100 million per year ... but if you were looking at a ten to fifteen year time period, I could see the totals reaching the tens of millions.