Question:
In Oracle, when we want to know how much disk a table occupies, we can query the dba_segments dictionary.
SELECT segment_name, segment_type, bytes/1024/1024 MB
FROM dba_segments
WHERE segment_type='TABLE' and segment_name='<nome-tabela>';
In SQLite database how to know how much a table is occupying exactly on disk? Is there a dictionary? I've seen bad matrix calculus solutions to return data that I wouldn't want to use, which gives a flawed calculus for obvious reasons (overestimated):
SELECT COUNT(*) * -- The number of rows in the table
( 24 + -- The length of all 4 byte int columns
12 + -- The length of all 8 byte int columns
128 ) -- The estimate of the average length of all string columns
FROM MyTable
Answer:
If you need to know the exact size when running your code, it's chipped. The only way I see it is to read the entire table and add up the actual occupied sizes. And that can't even be done directly. Have to see the internal implementation to calculate the size of some columns. And if the implementation changes, that's it. In other words, don't even waste time with this.
If you just need to have an idea at some point by your organization, you can use sqlite_anlyzer . Maybe it can read the output via code, but it's a trick. Anyway, it's not ready for Android. It would have to compile, maybe even adapt to run on it.
If it is very important to always know the size of the table (I doubt it) it is possible to do another workaround. I created a TRIGGER that keeps an accumulator in each writing made in the table. This accumulator will always be the size of the table. It's not that simple to do, it has overhead and creates a burden for maintenance (although I can abstract this), but it's a solution (…questionable). Basically what Oracle does 🙂
I've seen other "solutions", one worse than the other.