Javi P�rez wrote: > is there any way of calculating how many space will take a table > knowing an estimated number of the rows it's going to have?
Hi, ok, fine job for Friday afternoon. I hope, afterwards you do not wish never to have asked.... one row consists of 10 bytes overhead + 8 bytes for SYSKEY if you did not specifiy a primary key + space for your data the maximum space needed for your data is influenced by the datatype and defined length Data Type Memory Requirements of a Column Value in Bytes for This Data Type CHAR(n); n<=30 n+1 CHAR(n); VARCHAR(n); key column; 30<n<=254 n+1 CHAR(n); VARCHAR(n); no key column; 30<n<=254 n+2 CHAR(n); VARCHAR(n); 254<n n+3 LONG 9 FIXED(p,s) (p+1) DIV 2 + 2 FLOAT(p) (p+1) DIV 2 + 2 BOOLEAN 2 DATE 9 TIME 9 TIMESTAMP 21 This list is part of the reference manual (data definition --> column definition --> memory requirements...) For datatypes, where n+2 / n+3 is given, for the last column in the primary key and for columns added with alter table add only the space needed for storing the data is used, not the maximum space. This is true for (VAR)CHAR and number-data, not for DATE/TIME/... BTW: do not decide to add every column with alter table add. Space needed will decrease, but performance, too, if you do access those columns often. If you have an idea, how much space those longer, but variable columns will need, go on further. Now we have the average length of one row. One page in the database has 8 KB - some overhead, let us calculate with 8100 bytes available. Depending on your row size (just around 4KB compared to 100bytes) you will have 1 row/page-->page filled 50% or more, usually more. In a normal system (inserted,updated,deleted at different places) we often see pages filled around 80%--> around 6500 bytes. Now you can calculate, how many pages are needed to store the data. But you did not finish. Do you use the datatype LONG ? LONGs of one table which have short data (<8000bytes) are put together in one tree, longer ones do each have its own tree. --> expected length of your LONG data put into (perhaps several) page trees. Now we have the estimated number of leave pages for all our trees. Leave pages are those pages where the real data is stored. NOW we have to estimate how much info on top is needed in our B*page-trees to find fast and easyly that page that will contain the wanted row. And here the problem is for me to describe. It depends on the length of your primary key, on the number of bytes in this primary key which look the same in many consecutive rows and so on. B*trees, as you perhaps know, need some separator to decide which way to follow to the wanted leave page. The first bytes of the primary key together with a pointer to the page where rows with primary key-values >= the given first bytes are stored in those non-leave-pages. And if only few rows with long keys looking the same (for example) up to byte 345 have to be handled, your separator will be long, only few separators will fit in one non-leave-page and you will need many levels of non-leave-pages--> the number of non-leave-pages will be much higher than for another table with the same number of (short) rows where the first (for example) 7 bytes are enough to separate the pages. Therefore it is not easy (if ever possible without knowledge of data-distribution) to calculate the number of pages for the whole stuff. And if you want to create indices..... Have a nice weekend and good luck with the calculation Elke SAP Labs Berlin
