Size occupied on disk by a table in SQLite

Question

Size occupied on disk by a table in SQLite

Navigation

#1 by (5 votes)

2

In Oracle, when we want to know how much a table occupies disk 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 do you know how much a table is occupying exactly on disk? Is there a dictionary? I've already seen awful matrix-computing solutions to return the data I would not want to use that gives a hard-hitting calculation 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

sql android sqlite sqlite3

asked by anonymous 10.06.2016 / 20:40

1 answer

How to disable a button, and to activate it, should you click on another button? Find item in a List with Contains method

score 5 · Accepted Answer

If you need to know the exact size while your code is running, it is chipped. The only way I see it is to read the whole table and go adding the actual occupied lengths. And this can not even do it directly. You have to see the internal implementation to calculate the size of some coulas. And if the implementation changes, it is. That is, do not waste time with it.

If you only need to have an idea at some point per your organization, you can use sqlite_anlyzer . Maybe you can read the output via code. But it is gambiarra. Anyway, it does not have Android ready. 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 is possible to do another gambiarra. I created a% w / o of% that keeps maintaining an accumulator in each write done in the table. This accumulator will always have the size of the table. It is not so simple to do, it has overhead and it creates a maintenance burden (even if you can abstract it), but it is a (questionable) solution. But this is basically what Oracle does:)

I have seen other "solutions", one worse than the other.