Question about limiting the number of table spaces

[former_SQLA_member1694881]

Hi,

As far as I know, in SAW SQL Anywhere 17 and below it can be a maximum of 13 table spaces (1 main and 12 additional).

Question: Why is there such a restriction ? Why can there be 13 table spaces, and not say 255 ?

[johnsmirnios · Accepted solution]

Page IDs are 32 bits. 4 bits of a page id identify the dbspace: 13 for table dbspaces plus log, map, and temp files (16 files). The map file isn't used anymore -- it was used by the 'write file' feature that allowed updates to read-only dbs (ex, dbs on CDROM).

The remaining 28 bits are used as the physical page number within the file which gives (2^28)*page_size as the max dbspace file size.

If we allowed more files while keeping 32-bit page IDs, the size limit for each file would be smaller. Changing to larger page IDs (64-bits) would cost space on every page where we store page IDs and would be a very big, fundamental format change.

[former_SQLA_member1694881 · Accepted solution]

Your answer is clear (thanks for such a detailed answer), although 13 tablespaces are still not enough.

Can You tell me whether You have plans to increase the bit size of this identifier (48 or 64 bits) ?

[MarkCulp · Accepted solution]

Perhaps it would be useful if you could explain why you think you need more than 13 tablespaces?

[VolkerBarth · Accepted solution]

The map file isn't used anymore.

So, technically, you could increase the number of allowed additional dbspaces from 12 to 13? (Note: I don't have that need.)

[former_SQLA_member1694881 · Accepted solution]

I store document tests (files) in my database, so the database is growing quickly. Now my database page size is 16 kb, I don’t want to increase it to 32 kb or store document texts outside the database.

In my opinion, it’s better to have two 4 TB table spaces than one 8 TB table space - it’s easier to operate with smaller files

[VolkerBarth · Accepted solution]

So, given you can have 13 dbspaces with at maximum 4 TB for 16 KB pages, are you facing a 52 TB database size limit? Just out of curiosity: How long does a backup (and a restore) take?

[former_SQLA_member1694881 · Accepted solution]

In a week, users retain on an average of 15 GB in the database of texts of documents. I can fill each table space no more than 75% of its size (this is necessary so that there is still a place to change the file previously saved there). I am still far from filling all the tabular spaces, now the total volume of the base (all the database files except the log) is about 5 TB, but I look into the future and if there was no such restriction on tabular spaces, it would be easier for me.

As for the backup, an incremental backup is made.