SQL Anywhere database - Storing place, how to calculate ?

Author

Storing place, how to calculate ?

Peter Stojkovic

2005-08-24, 7:23 am

I have a question to storing place consumption.

When a have a table that exist on one column only type is INTEGER, how many
place is needed on harddisk to store 1 row.approx?
To store 2 rows approx?

I think on the 4K-Blocks of database.
Is each row stored in a seperate 4K-Block ?

How can I calculate the space that is needed for 100.000 datasets.?

Thanks
Peter

Rob Waywell

2005-08-24, 11:24 am

An integer requires 4 bytes of storage.
There is some overhead on each page but that is not significant in this case
since a page can only store up to 256 records.

So for a 1 column table where the only column is of type integer you will be
able to store 256 records on a single 4k page. (256*4bytes = 1kb)

To store 100,000 records you will need 100,000/256 = 391 pages. 391 pages *
4k/page gives you approximately 1.5 MB of disk space. I say approximate for
a few reasons including the fact that we allocate multiple pages when
growing a table and the fact that if this was the only table in the database
there would still be other space taken up by system tables, etc.

If you have an index/primary key/foreign key on this column then that will
also require additional storage space.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"Peter Stojkovic" <Peter.Stojkovic@gmx.net> wrote in message
news:430c5ef4$1@foru
ms-1-dub...
>I have a question to storing place consumption.
>
> When a have a table that exist on one column only type is INTEGER, how
> many place is needed on harddisk to store 1 row.approx?
> To store 2 rows approx?
>
>
> I think on the 4K-Blocks of database.
> Is each row stored in a seperate 4K-Block ?
>
> How can I calculate the space that is needed for 100.000 datasets.?
>
>
>
> Thanks
> Peter
>
>
>
>
>
>

Nick Elson

2005-08-24, 1:23 pm

Integers only take up 4 bytes. Many such rows
can fit on a page of any size allowed.

With fix sized rows like this, we can pack many of
them onto a single page, and that can be done so
without reserving any free space for rows to grow
[see PCTFREE in the Alter Table statement]. Normally
200 bytes are reserved for PCTFREE and there is some
overhead to manage the page but you should find you
can get something around 1000 such rows onto a single
table page [with PCTFREE set to zero that is].

Do not forget there will be overhead for any indexes.
Do not forget there are indexes added by the system
for Primary and Foreign Keys that take up space.

Additional, details can be queried using the database
and database tools directly. See the following for
some of them [see the documentation for details]

View the table in Sybase Central and 'right click' on it to
select it's "Properties". The [Miscellaneous] tab provides
you a way to see the Maximum table width (i.e. the max.
row size) as well as a way to see a number of rows used.

The supplied system procedure

sa_table_page_usage(
)

gives you a way to see the full impact of storage requirements
for the table broken down by table and index pages.

Also, the procedure

sa_table_fragementat
ion()

as well as

sa_index_levels()
and
sa_index_density()

provides you will lots more details.

"Peter Stojkovic" <Peter.Stojkovic@gmx.net> wrote in message
news:430c5ef4$1@foru
ms-1-dub...
>I have a question to storing place consumption.
>
> When a have a table that exist on one column only type is INTEGER, how
> many place is needed on harddisk to store 1 row.approx?
> To store 2 rows approx?
>
>
> I think on the 4K-Blocks of database.
> Is each row stored in a seperate 4K-Block ?
>
> How can I calculate the space that is needed for 100.000 datasets.?
>
>
>
> Thanks
> Peter
>
>
>
>
>
>

Sponsored Links